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/
