使用libpqxx进行批量数据存储或如何在libpqxx中使用COPY语句

Question

使用libpqxx进行批量数据存储或如何在libpqxx中使用COPY语句

3

要在PostgreSQL中插入大量数据/填充数据库，最快的方法是使用COPY。来源
我需要填充一个数据库。目前我每秒只能得到100-200个写入速度。这涉及通过C++库libpqxx发送许多单独的INSERT。我认为有两个原因：

数据有很多重复记录。（我有原始日志，我解析并发送。）这会导致主键异常。
逐个发送Insert语句。

第一个原因不在我的控制范围内。然而，我正在阅读关于第二个原因的内容。
据我所知，tablewriter类适用于此目的。然而，它已经被弃用了。我已经了解到可以使用stdin作为copy的参数。但是在这些线索之后，我迷失了。有人能帮我找到解决方案吗？

编辑：这里是代码，其中我有一个执行语句的函数：

void pushLog(Log log,pqxx::connection *conn){
    pqxx::work w(*conn);
    std::stringstream stmt;
    stmt<<"INSERT INTO logs VALUES('"<<log.getDevice()<<"','"<<log.getUser()<<"','"<<log.getDate()<<"','"<<log.getLabel()<<"');";
    try{
        pqxx::result res = w.exec(stmt.str());
        w.commit();
    }
    catch(const std::exception &e){
        std::cerr << e.what() << std::endl;
        std::cout<<"Exception on statement:["<<stmt.str()<<"]\n";
        return;
    }

}

我之前建立了连接，并传递了一个引用。

注：该问题可能缺少一些细节。如果有的话，请评论，我会进行编辑并添加它们。

- digvijay91

检查是否为每个插入操作都启动了新事务。您应该在一个事务中完成所有操作。如果可能，请展示相关代码。 - Daniel Vérité

@DanielVérité 我已经添加了我拥有的代码。我怀疑它不是在一个事务中发生的，但我不知道如何使用libpqxx控制事务。 - digvijay91

是的，这段代码会提交每个INSERT，这不好。你需要重新设计它，只在循环中为每一行执行w.exec(..)，并将pqxx::work实例化和w.commit()放在循环外面。 - Daniel Vérité

1

那你的意思是像有10个w.execs()然后一个commit吗？ - digvijay91

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Daniel Vérité · Accepted Answer

pushLog函数会将每个插入操作单独提交，而且提交速度较慢。

正如文档中填充数据库所解释的：

如果您允许每次插入都单独提交，PostgreSQL会为添加的每一行执行大量工作

此外：

将所有插入操作放在一个事务中的另一个好处是，如果一行的插入失败，则从插入到此时为止插入的所有行都将回滚，因此您不会被卡在部分加载的数据上

然而，在您的情况下，这将是一个问题而不是好处，因为每个INSERT可能会因主键冲突而失败，从而取消上次提交以来的先前INSERT。请注意，如果使用COPY，这也将是一个问题。

由于性能需要真正必须将查询分组在事务中，因此您需要以不中止事务的方式处理主键冲突。

通常使用两种方法：

避免错误：INSERT INTO... WHERE NOT EXISTS (SELECT 1 FROM table WHERE primary_key=...)
通过在一个带有异常块忽略itr的plpgsql函数中插入来捕获错误。导致重复的具体INSERT将被取消，但事务不会中止。

如果您有并发插入，这些方法需要使用锁定策略进行改进。