Re: Statement-level rollback

From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Peter Eisentraut
> On 2/28/17 02:39, Tsunakawa, Takayuki wrote:
> > I'd like to propose statement-level rollback feature. To repeat myself,
> this is requested for users to migrate from other DBMSs to PostgreSQL. They
> expect that a failure of one SQL statement should not abort the entire
> transaction and their apps (client programs and stored procedures) can
> continue the transaction with a different SQL statement.
>
> Can you provide some references on how other systems provide this feature?

Oracle doesn't.

SQL Server provides like this:

SET XACT_ABORT
https://msdn.microsoft.com/en-us/library/ms188792.aspx

MySQL doesn't. BTW, MySQL enables changing autocommit mode with SET statement:

16.5.2.2 autocommit, Commit, and Rollback
https://dev.mysql.com/doc/refman/8.0/en/innodb-autocommit-commit-rollback.html

And above all, I've found EnterpriseDB supports statement-level rollback with GUC! So PostgreSQL should be able to do.

https://www.edbpostgres.com/docs/en/9.6/asguide/EDB_Postgres_Advanced_Server_Guide.1.17.html#pID0E0QUD0HA

----------------------------------------
edb_stmt_level_tx is set to TRUE, then an exception will not automatically roll back prior uncommitted database updates. If edb_stmt_level_tx is set to FALSE, then an exception will roll back uncommitted database updates.

Note: Use edb_stmt_level_tx set to TRUE only when absolutely necessary, as this may cause a negative performance impact.
----------------------------------------

Regards
Takayuki Tsunakawa