Re: Issue with server side statement-level rollback

Le 20/11/2020 à 16:18, Gilles Darold a écrit :
> I will work later on a POC to demonstrate the use case I want to
> implement.

Hi Andres,

I have created a new version of the pg_statement_rollback extension [1]
to demonstrate the use of the hooks on start_xact_command(),
finish_xact_command() and AbortCurrentTransaction() to implement the
statement-level rollback feature entirely driven at serverside. It
require that the patch [2]  I've provided be applied on PostgreSQL
source first.

Here is what can be achieved with this patch:

LOAD 'pg_statement_rollback.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

Actually unlike I've though this is the hook on finish_xact_command()
that is useless. In the extension I'm executing the RELEASE/SAVEPOINT in
the start_xact_command() hook before executing the next statement. The
hook on AbortCurrentTransaction() is used to signal that a ROLLOBACK
TO/SAVEPOINT need to be executed into the start_xact_command() hook
instead of a RELEASE/SAVEPOINT.

This works perfectly and do not crash PG anymore when compiled with
assert. Advanced tests (with triggers, client savepoint, CTE, etc.) are
available in the test/sql/ directory. Use of "make installcheck" allow
to run the regression tests.

Based on this result I really think that these hooks should be included
to be able to extend PostgreSQL for such feature although I have not
though about an other use that this one.

Regards, 

I've attached all code for archiving but the current version can be
found here too:

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

[2]
https://raw.githubusercontent.com/darold/pg_statement_rollbackv2/main/command-start-finish-hook-v1.patch

--

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