Pqxx reuse / activate work transaction

Question

I want to use pqxx :: work for brief queries and obligations, while the commit function does not allow me to use it again. Here is a simple example:

pqxx::connection G_connexion("dbname=basetest user=usertest password=1234"); pqxx::work G_work(G_connexion); int main(int argc, char* argv[]) { G_work.exec("insert into test.table1(nom) VALUES('foo');"); G_work.commit();//until here, no problem G_work.exec("insert into test.table1(nom) VALUES('bar');"); //error, transaction already closed G_work.commit(); } 

When I try to insert the value "bar", after the commit, I get pqxx :: use_error: Error executing query . Attempt to activate transaction<READ COMMITTED> which is already closed Error executing query . Attempt to activate transaction<READ COMMITTED> which is already closed

How can I avoid closing the connection after making changes? can I reset G_work with the successful equivalent of G_work = pqxx :: work (G_connexion) or another? In addition, one bad request should not lead to the failure of the whole process, only that process (G_work can still be used after the failure).

I need to keep the same G_Work variable, because it will be a global variable called from many places in the program.

Answer 1

pqxx::work is just pqxx::transaction<> , which ultimately gets most of its logic from pqxx::transaction_base .

This class is not intended for multiple transactions. Instead, it is for a single transaction in a try / catch block. It has a state member variable ( m_Status ) that is never reinitialized even after commit.

Normal pattern:

 { pqxx::work l_work(G_connexion); try { l_work.exec("insert into test.table1(nom) VALUES('foo');"); l_work.commit(); } catch (const exception& e) { l_work.abort(); throw; } } 

Maybe libpqxx might cancel the transaction on deletion (to avoid trying / catch entirely), but it is not.

This doesn't seem to match your usage pattern, as you want G_work be a global variable, accessible from several places in your program. Note that pqxx :: work is not a class for connection objects, but simply a way to encapsulate the start / commit / rollback with C ++ exception handling.

However, libpqxx also allows the execution of an external transaction statement (or at least outside of transactions managed by libpqxx). You must use instances of the pqxx::nontransaction .

 #include "pqxx/nontransaction" pqxx::connection G_connexion("dbname=basetest user=usertest password=1234"); pqxx::nontransaction G_work(G_connexion); int f() { G_work.exec("insert into test.table1(nom) VALUES('foo');"); G_work.exec("insert into test.table1(nom) VALUES('bar');"); } 

Note that this is equivalent to:

 #include "pqxx/nontransaction" pqxx::connection G_connexion("dbname=basetest user=usertest password=1234"); int f() { pqxx::nontransaction l_work(G_connexion); l_work.exec("insert into test.table1(nom) VALUES('foo');"); l_work.exec("insert into test.table1(nom) VALUES('bar');"); } 

In the end, nothing prevents you from managing transactions with pqxx::nontransaction . This is especially true if you want save points . I would also advise using pqxx::nontransaction if your transaction should go beyond the scope (for example, in the global scope).

 #include "pqxx/nontransaction" pqxx::connection G_connexion("dbname=basetest user=usertest password=1234"); pqxx::nontransaction G_work(G_connexion); int f() { G_work.exec("begin;"); G_work.exec("insert into test.table1(nom) VALUES('foo');"); G_work.exec("savepoint f_savepoint;"); // If the statement fails, rollback to checkpoint. try { G_work.exec("insert into test.table1(nom) VALUES('bar');"); } catch (const pqxx::sql_error& e) { G_work.exec("rollback to savepoint f_savepoint;"); } G_work.exec("commit;"); }