November 14, 2024: PostgreSQL 17.1, 16.5, 15.9, 14.14, 13.17, and 12.21 Released!

Server side rollback at statement level for PostgreSQL

Posted on 2020-11-02 by Gilles Darold
Related Open Source

Zurich, Switzerland - Nov 2nd, 2020

Server side rollback at statement level for PostgreSQL

The initial release v1.0 of pg_statement_rollback was released today.

pg_statement_rollback is a PostgreSQL extension to add server side transaction with rollback at statement level like in Oracle or DB2.

Statement-level rollback

If at any time during execution a SQL statement causes an error, all effects of the statement are rolled back. The effect of the rollback is as if that statement had never been run. This operation is called statement-level rollback and has the following characteristics:

  • A SQL statement that does not succeed causes the loss only of work it would have performed itself. The unsuccessful statement does not cause the loss of any work that preceded it in the current transaction.
  • The effect of the rollback is as if the statement had never been run.

In PostgreSQL the transaction cannot continue when you encounter an error and the entire work done in the transaction is rolled back. Oracle or DB2 have implicit savepoint before each statement execution which allow a rollback to the state just before the statement failure.

Current implementation of rollback at statement level for PostgreSQL is done at client side. psql has \set ON_ERROR_ROLLBACK on, JDBC has autorollback on SQL exception from executing a query, psqlODBC too with the "statement level rollback" mode. The problem of these implementations is that they add extra communication with the server by sending a SAVEPOINT autosave and RELEASE SAVEPOINT autosave so it can seriously limit the throughput of the application.

See documentation for a complete description of the feature and how to use of the extension.

Links & Credits

pg_statement_rollback is an open project from LzLabs (https://www.lzlabs.com/). Any contribution to build a better tool is welcome. You just have to send your ideas, features requests or patches using the GitHub tools.

Links :