The pg_trans class
This is the class to use for starting and ending transactions or subtransactions.
Subtransactions are a new feature of PostgreSQL v8.0 and are implemented by
issuing SAVEPOINT and ROLLBACK TO...
commands. The feature needs to be activated by setting the
nested_transactions option to true in the pg_cnx object.
Public functions
-
pg_trans (pg_cnx& cnx)
Default Constructor.
If the connexion is not already in a transaction, a SQL BEGIN command is issued
to the server, starting a new transaction.
Otherwise, if nested transactions are not available, nothing is done,
else a SQL SAVEPOINT command is issued, thus starting a subtransaction.
-
[virtual] ~pg_trans ()
Destructor. If the commit() function has not been called, a rollback() is
implicitly issued, aborting the transaction or subtransaction.
- void commit()
The transaction or subtransaction is commited.
- void rollback()
The transaction or subtransaction is rolled back. Note that it is useless
to issue a rollback after a SQL error, because the PostgreSQL server will
have rolled back the current [sub]transaction even before the application
code knows about it.
Usage patterns
Top level transactions
A programmer should use top level transactions to have a set of SQL statements
affect the database in a "all or nothing" way. The overall performance
of a set of SQL statements is also better while inside a transaction since
the writes are not commited until the end of the transaction.
Nested transactions
A programmer can use subtransactions to implement a local commit-or-not strategy
inside a function, while letting the caller function decide if the overall
updating must be commited or not, depending on the error that happened.
Here is an example that makes use of pg_trans and pg_excpt to isolate the
main database code from an error happening while logging an event:
int newlog(pg_cnx& cnx,const char* txt)
{
pg_trans tr;
try {
pg_stream s("INSERT INTO logtable(txt,event_date) VALUES(:p,now())", cnx);
s << txt;
}
catch(pg_excpt e) {
return 0; // no need to rollback
}
tr.commit(); // commit if no error happpened
return 1; // OK
}
int process(pg_cnx& cnx)
{
pg_trans trans;
cnx.set_option("nested_transactions", true);
// ... do database processing ...
if (!newlog(cnx, "logmessage")) {
std:cerr << "warning: log failed (processing continues)\n";
}
// ... do more database processing ...
trans.commit();
}
It is to be noted that the code doesn't have to be different to deal
with transactions compared with subtransactions, since it is the
current sql context that tells which one should be used.
In the previous example, if newlog() was called while no transaction
was in progress, the pg_trans object would issue a top-level
transaction and its behavior would be similar.
Not using pg_trans
A programmer wanting to use transactions without resorting to pg_trans
in order to have more control can simply issue these sql statements:
pg_stmt("BEGIN") will start a top-level transaction
pg_stmt("END") will commit a top-level transaction
pg_stmt("ROLLBACK") will rollback a top-level transaction
pg_stmt("SAVEPOINT s") will start a subtransaction
pg_stmt("RELEASE SAVEPOINT s") will commit a subtransaction
pg_stmt("ROLLBACK TO s") will rollback a subtransaction
Note that the savepoint name pgs_s is reserved for pgstream use.