Zurich, Switzerland - Nov 2nd, 2020
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.
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:
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
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.
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.