Set autocommit off in PostgreSQL with SOCI C++

Question

This is an answer rather than a question which I need to state in SO anyway. I was struggle with this question ("how to turn off autocommit when using soci library with PostgreSQL databases") for a long time and came up with several solutions.

In Oracle, by default the auto commit option is turned off and we have to call soci::session::commit explicitly to commit the transactions we have made but in PostgreSQL this is other way around and it will commit as soon as we execute a sql statement (correct me, if I'm wrong). This will introduce problems when we write applications database independently. The soci library provide soci::transaction in order to address this.

So, when we initialize a soci::transaction by providing the soci::session to that, it will hold the transaction we have made without commiting to the database. At the end when we call soci::transaction::commit it will commit the changes to the database.

soci::session sql(CONNECTION_STRING);
soci::transaction tr(sql);
try {
    sql << "insert into soci_test(id, name) values(7, \'John\')";
    tr.commit();
}
catch (std::exception& e) {
    tr.rollback();
}

But, performing commit or rollback will end the transaction tr and we need to initialize another soci::transaction in order to hold future transactions (to create an active in progress transaction) we are about to make. Here are more fun facts about soci::transaction.

1. You can have only one soci::transaction instance per soci::session. The second one will replace the first one, if you initialize another.
2. You can not perform more than a single commit or rollback using a soci::transaction. You will receive an exception, at the second time you do commit or rollback.
3. You can initialize a transaction, then use session::commit or session::rollback. It will give the same result as transaction::commit or transaction::rollback. But the transaction will end as soon as you perform single commit or rollback as usual.
4. It doesn't matter the visibility of the soci::transaction object to your scope (where you execute the sql and call commit or rollback) in order to hold the db transactions you made until explicitly commit or rollback. In other words, if there is an active transaction in progress for a session, db transactions will hold until we explicitly commit or rollback.
5. But, if the lifetime of the transaction instance which created for the session was end, we cannot expect the db transactions will be halt.
6. If you every suffer with "WARNING: there is no transaction in progress", you have to perform commit or rollback only using soci::transaction::commit or soci::transaction::rollback.

Now I will post the solution which I came up with, in order to enable the explicit commit or rollback with any database backend.

Answer 1

This is the solution I came up with.

namespace mysociutils
{
    class session : public soci::session
    {
    public:
        void open(std::string const & connectString)
        {
            soci::session::open(connectString);
            tr = std::unique_ptr<soci::transaction>(new soci::transaction(*this));
        }

        void commit()
        {
            tr->commit();
            tr  = std::unique_ptr<soci::transaction>(new soci::transaction(*this));
        }

        void rollback()
        {
            tr->rollback();
            tr  = std::unique_ptr<soci::transaction>(new soci::transaction(*this));
        }        

        void ~session()
        {
            tr->rollback();
        }

    private:
        std::unique_ptr<soci::transaction> tr;
    };
}

When ever commit or rollback is performed, initialize a new soci::transaction. Now you can replace your soci::session sql with mysociutils::session sql and enjoy SET AUTOCOMMIT OFF.