[PATCH] Hooks at XactCommand level

From: Gilles Darold <gilles(at)darold(dot)net>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: [PATCH] Hooks at XactCommand level
Date: 2020-12-08 10:15:12
Message-ID: ed0a9bde-8658-d1ea-1ca7-29b89e9af929@darold.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


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.


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()
* finish_xact_command called in finish_xact_command() just before
* 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.


Nothing more to add here.


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';
    SET pg_statement_rollback.enabled TO on;
    CREATE SCHEMA testrsl;
    SET search_path TO testrsl,public;
    CREATE TABLE tbl_rsl(id integer, val varchar(256));
    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)


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

[3] https://github.com/darold/pg_statement_rollbackv2

Gilles Darold

Attachment Content-Type Size
command-start-finish-hook-v2.patch text/x-patch 3.8 KB
pg_statement_rollbackv2.tar.gz application/gzip 37.7 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2020-12-08 10:16:05 Re: Single transaction in the tablesync worker?
Previous Message Konstantin Knizhnik 2020-12-08 09:59:25 Re: Wrong check in pg_visibility?