Zurich, Switzerland - Nov 2nd, 2020
Server side rollback at statement level for PostgreSQL
pg_statement_rollback is a PostgreSQL extension to add server side
transaction with rollback at statement level like in Oracle or DB2.
The initial release v1.0 of pg_statement_rollback was released today.
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.
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 :
- Documentation: https://github.com/lzlabs/pg_statement_rollback/blob/master/README.md
- Download: https://github.com/lzlabs/pg_statement_rollback/releases/
- Support: use GitHub report tool at https://github.com/lzlabs/pg_statement_rollback/issues
- Authors: https://github.com/lzlabs/pg_statement_rollback/#authors