[PATCH] Hooks at XactCommand level - Mailing list pgsql-hackers

Hi,

Based on a PoC reported in a previous thread [1] I'd like to propose new
hooks around transaction commands. The objective of this patch is to
allow PostgreSQL extension to act at start and end (including abort) of
a SQL statement in a transaction.

The idea for these hooks is born from the no go given to Takayuki
Tsunakawa's patch[2] proposing an in core implementation of
statement-level rollback transaction and the pg_statement_rollback
extension[3] that we have developed at LzLabs. The extension
pg_statement_rollback has two limitation, the first one is that the
client still have to call the ROLLBACK TO SAVEPOINT when an error is
encountered and the second is that it generates a crash when PostgreSQL
is compiled with assert that can not be fixed at the extension level.

Although that I have not though about other uses for these hooks, they
will allow a full server side statement-level rollback feature like in
commercial DBMSs like DB2 and Oracle. This feature is very often
requested by users that want to migrate to PostgreSQL.


SPECIFICATION
==================================================


There is no additional syntax or GUC, the patch just adds three new hooks:


* start_xact_command_hook called at end of the start_xact_command()
function.
* finish_xact_command called in finish_xact_command() just before
CommitTransactionCommand().
* abort_current_transaction_hook called after an error is encountered at
end of AbortCurrentTransaction().

These hooks allow an external plugins to execute code related to the SQL
statements executed in a transaction.


DESIGN
==================================================


Nothing more to add here.


CONSIDERATIONS AND REQUESTS
==================================================


An extension using these hooks that implements the server side rollback
at statement level feature is attached to demonstrate the interest of
these hooks. If we want to support this feature the extension could be
added under the contrib/ directory.

Here is an example of use of these hooks through the
pg_statement_rollbackv2 extension:

    LOAD 'pg_statement_rollbackv2.so';
    LOAD
    SET pg_statement_rollback.enabled TO on;
    SET
    CREATE SCHEMA testrsl;
    CREATE SCHEMA
    SET search_path TO testrsl,public;
    SET
    BEGIN;
    BEGIN
    CREATE TABLE tbl_rsl(id integer, val varchar(256));
    CREATE TABLE
    INSERT INTO tbl_rsl VALUES (1, 'one');
    INSERT 0 1
    WITH write AS (INSERT INTO tbl_rsl VALUES (2, 'two') RETURNING id,
val) SELECT * FROM write;
     id | val
    ----+-----
      2 | two
    (1 row)

    UPDATE tbl_rsl SET id = 'two', val = 2 WHERE id = 1; -- >>>>> will fail
    psql:simple.sql:14: ERROR:  invalid input syntax for type integer: "two"
    LINE 1: UPDATE tbl_rsl SET id = 'two', val = 2 WHERE id = 1;
                    ^
    SELECT * FROM tbl_rsl; -- Should show records id 1 + 2
     id | val
    ----+-----
      1 | one
      2 | two
    (2 rows)

    COMMIT;
    COMMIT

As you can see the failing UPDATE statement has been rolled back and we
recover the state of the transaction just before the statement without
any client savepoint and rollback to savepoint action.


I'll add this patch to Commitfest 2021-01.


Best regards


[1]
https://www.postgresql-archive.org/Issue-with-server-side-statement-level-rollback-td6162387.html
[2]
https://www.postgresql.org/message-id/flat/0A3221C70F24FB45833433255569204D1F6A9286%40G01JPEXMBYT05
[3] https://github.com/darold/pg_statement_rollbackv2

--
Gilles Darold
http://www.darold.net/