如何与LIBPQXX API同时插入数据?(PostgreSQL，线程)

Question

使用的东西：

库：libpqxx:x64-windows version 6.4.4

OS：Windows 10

编译器：Visual C (MSVC(

这是我的SQL表：

CREATE TABLE test(
    test_name VARCHAR(16)
);

这是我的简约测试版本：

main.cpp

#include <pqxx/pqxx> 
#include "test.h"
int main()
{
    pqxx::connection database_connection("dbname = test user = postgres password = *****
      hostaddr = 127.0.0.1 port = 5432");

    database_connection.prepare("insert_into_table", "INSERT INTO test 
    VALUES ($1)");
    test test(&database_connection);
    system("pause");
}

---

test.h

#pragma once
#include <pqxx/connection.hxx>
#include <thread>
#include <array>
class test
{
public:
    explicit test(pqxx::connection* database_connection);
    void InsertData();
    std::array<std::thread, 1> threads{};
    pqxx::connection* database_connection;
};

---

test.cpp

#include "test.h"
#include <iostream>
#include <pqxx/transaction.hxx>
test::test(pqxx::connection* database_connection) : database_connection(database_connection)
{
    for (auto& i : threads)
    {
        i = std::thread(&test::InsertData, this);
    }
}

void test::InsertData()
{
    pqxx::work work(*database_connection);
    try
    {
        pqxx::result result = work.exec_prepared("insert_into_table", "test_data"); //prepared data in real project pqxx::binarystring blobs
        work.commit();
    }
    catch (const std::exception& e) {
        work.abort();
    }
}

---

将test.h中的螺纹大小更改为大于1的任何结果。

注意：我必须使用准备好的SQL语句将RAW BYTEA数据插入我的表。

输出：

Exception thrown at 0x00007FFC2FF44008 in Project3.exe: Microsoft C++ exception : pqxx::usage_error at memory location 0x000000CEB00FF050.
Exception thrown at 0x00007FFC2FF44008 in Project3.exe: Microsoft C++ exception : pqxx::usage_error at memory location 0x000000CEB03FEF50.
Exception thrown at 0x00007FFC2FF44008 in Project3.exe: Microsoft C++ exception : pqxx::usage_error at memory location 0x000000CEB04FED30.
Exception thrown at 0x00007FFC2FF44008 in Project3.exe: Microsoft C++ exception : pqxx::usage_error at memory location 0x000000CEB02FEAF0.
Debug Error!

如何同时插入此API？

Answer 1

解决方案的链接：https：//pqxx.org/development/libpqxx/wiki/threading

Approaches for thread - safe programs
Approach 1: single database thread
Make one of your threads the dedicated "database thread." Ensure that that thread is the only one that ever accesses objects or functions from libpqxx.Use message - passing to channel all your database interactions through this thread.
This approach should be safe even if your libpq build is not thread - safe.
Approach 2 : global lock
Let multiple threads access objects and functions from libpqxx, but never simultaneously.Use a single, global lock to protect any access to libpqxx.
You do need a lock of some sort.You can't generally rely on other ways to know that thread 1 is done before thread 2 uses the library! Data from thread 1 may still be cached in a register or, depending on your system architecture, a processor cache that thread 2 isn't synchronized with.Or vice versa.It gets ugly and subtle.
Approach 3 : thread - local connections
Every connection in libpqxx acts as its own little world.It produces result objects, you create transactions on it, it uses error message callbacksand so forth.Keep all of the objects related to a connection exclusively inside the same thread that created the connection.This way you'll need no locking at all.
Of course you can still have more than one connection inside the same thread.This model should be fine for most applications, so this is probably as far as you'll want to read.
Approach 4 : per - connection locking
If you really have to share any libpqxx objects at all between threads, set up one lock per connection.This lock should protect all objects related to that connection.That way, all access to a connectionand its related objects will be serialized as if they all stayed in the same thread.
The risk of deadlocks may be slightly greater if you go this way.

在我的情况下，我将连接更改为

static thread_local database_connection;

因此，每个线程都有自己的连接，然后代码工作而无需更改。