pqxx::nontransaction seems to start a transaction

Question

Base problem: I have a SELECT statement with a high number of comparisons of bytea which takes a significant amount of time to process and I need to reduce this. To reduce the time I am utilising multi-threading with std::thread.

To ensure that I would not run into problems with spawning multiple transactions and the SELECT is thread-safe I used pqxx::nontransaction.

And I get this error: libc++abi: libc++abi: terminating with uncaught exception of type pqxx::usage_error: Started new transaction while transaction was still active.terminating with uncaught exception of type pqxx::usage_error: Started new transaction while transaction was still active.

The basic idea is illustrated here using std::string as a representation for the fingerprint.

#include <pqxx/pqxx>
#include <string>

#include <vector>
#include <string>

#include <thread>

int main(void)
{
    pqxx::connection con; // Assum setup
    std::vector<std::string> items; /// Assume containing content

    std::vector<std::thread> threads;

    size_t num_items_thread = items.size() / std::thread::hardware_concurrency() - 1;
    size_t items_remainder = items.size() % (std::thread::hardware_concurrency() - 1);

    size_t off = 0; 
    
    for (size_t i = 0; i < std::thread::hardware_concurrency() - 1; ++i)
    {
        size_t end = off + num_items_thread;
        if (i == std::thread::hardware_concurrency() - 2)
        {
            end = items.size();
        }
        
        threads.emplace_back([&items, &con, off, end](){
                                 pqxx::nontransaction work(con);

                                 std::vector<std::string> data(std::begin(items) + off, std::begin(items) + end);

                                 std::string param_list = "";

                                 for (size_t i = 1; i <= data.size(); ++i)
                                 {

                                     param_list = param_list + std::to_string(i) + ","; 
                                 }
                                 param_list = param_list.substr(0, param_list.size() - 1);
                                 std::string query = "SELECT * FROM tbl WHERE arr IN (" + param_list + ")";
                                 work.exec_params(query, pqxx::prepare::make_dynamic_params(data));
                                 
                             });

        off = off + num_items_thread;
    }

    for (auto& thread : threads)
    {
        thread.join();
    }
    return 0; 
}

From what I understand based on the libpqxx documentation this should give me execution without invoking a transaction, but based on the error I still get a transaction. How can I avoid this?

but that was "just" a mistake in general idea. I don't have it in my code — Lars Nielsen, Jun 15 '21 at 13:25
pqxx is generally [not threadsafe](https://libpqxx.readthedocs.io/en/7.3.0/a01348.html). you need to protect the connection with a mutex while querying. A good alternative (depending on your usecase) might be to have 1 connection per thread instead, then you don't have any locking. — Turtlefight, Jun 15 '21 at 14:09
@Turtlefight that should not be need when not actually needing a transaction no? — Lars Nielsen, Jun 15 '21 at 18:59
@LarsNielsen pq and pqxx don't do any locking by themselves. See [here](https://stackoverflow.com/questions/28936544/thread-safe-pqconn-object) - Excerpt from the official documentation: One thread restriction is that no two threads attempt to manipulate the same PGconn object at the same time. In particular, you cannot issue concurrent commands from different threads through the same connection object. (If you need to run concurrent commands, use multiple connections.) [from here](https://www.postgresql.org/docs/8.4/libpq-threading.html).so no,sharing a connection between threads is not safe. — Turtlefight, Jun 16 '21 at 13:08

pqxx::nontransaction seems to start a transaction

0 Answers0