pqxx reuse / reactivate a work transaction

Question

I want to use a pqxx::work for multiple queries AND commitments, while the commit function prevents me from using 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 'bar' value, after the commit, I get a pqxx::usage_error : Error executing query. Attempt to activate transaction<READ COMMITTED> which is already closed

How can I avoid to close the connection after I commit the changes? Can I reset G_work with a succeeding equivalent of G_work=pqxx::work(G_connexion), or other? Also, one bad request should not crash the entire process, just the one in process (G_work still usable after a failure).

I have to keep the same variable G_Work because it will be a global variable called from lots of places in the program.

Cacology

Did you find an answer? – Harter

No. As nobody seems to have an answer I am adding a database class to each class in my software, passing it through methods when needed. An unique global transaction was too dangerous as I could not reactivate it if it failed. – Cacology

Answer 1

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

This class is not intended to serve for several transactions. Instead, it is intended for a single transaction within a try/catch block. It has a state member variable (m_Status) which is never reinitialized, even after a commit.

The normal pattern is:

{
    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;
    }
}

Arguably, libpqxx could rollback the transaction on deletion (to avoid the try/catch entirely) but it doesn't.

It seems that this doesn't fit your usage pattern as you want G_work to be a global variable accessible from several places in your program. Please note that pqxx::work is not the class for connection objects, but just a way to encapsulate begin/commit/rollback with C++ exceptions handling.

Nevertheless, libpqxx also allows you to execute statement outside transactions (or at least outside libpqxx-managed transactions). You should use instances of pqxx::nontransaction class.

#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');");
}

Please 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');");
}

Eventually, nothing prevents you to manage transactions with pqxx::nontransaction. This is especially true if you want savepoints. I would also advise using pqxx::nontransaction if your transaction is meant to last beyond a function scope (e.g. at 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;");
}

Crampon

Ok but using pqxx::nontransaction class lose the transactionnal part I want to keep. Indeed I want something like checkpoints : when the sohftware as reached some important points, it can submit/rollback, and then keep on going with the same global connection. – Cacology

You can manage transactions yourself with pqxx::nontransaction. i edited the answer accordingly. – Crampon

Ok I already thought of that, even with pqxx::transaction I was able to use exec("commit"); exec("begin") instead of G_work.begin(), what solves part if the problem. But still I need to be able to reset the connection in case of one failing request without reseting the software. For instance if i cannot insert a line due to some constraint, I must be able to keep going on the other actions. – Cacology

This is exactly the purpose of SAVEPOINT. See postgresql.org/docs/current/static/sql-savepoint.html – Crampon

OK ! the nontransaction class of G_work prevent the connexion to break when there is a illegal action on the base, and the rollback fits my checkpoints, that works for me. Thanks for your help Paul – Cacology