CODEWITHSUNDEEP
c++postgresqlconcurrencylibpqxx
Ojav
Ojav

Reputation: 980

How To Insert Data concurrently with the libpqxx API? (PostgreSQL,threads)

Stuff used:

Library: libpqxx:x64-windows version 6.4.4

OS: Windows 10

Compiler: Visual C++ (MSVC)

This is my SQL Table:

CREATE TABLE test(
    test_name VARCHAR(16)
);

This is my minimalistic test version:

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();
    }

}

Changing the thread size in test.h to anything greater than 1 results in.

Note: I have to use a prepared SQL statement to later insert raw bytea data into my table.

Output:

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!

How do I concurrently insert with this API?

Upvotes: 0

Views: 2395

Answers (1)

Ojav
Ojav

Reputation: 980

Link for solution: 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.

In my case I changed the connection to 

static thread_local database_connection;

so every thread has its own connection and then the code works without changing much.

Upvotes: 3

Related Questions