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/
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index 9cd0b7c11b..5449446884 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -314,6 +314,9 @@ typedef struct SubXactCallbackItem
static SubXactCallbackItem *SubXact_callbacks = NULL;
+/* Hook for plugins to get control of at end of AbortCurrentTransaction */
+AbortCurrentTransaction_hook_type abort_current_transaction_hook = NULL;
+
/* local function prototypes */
static void AssignTransactionId(TransactionState s);
static void AbortTransaction(void);
@@ -3358,6 +3361,9 @@ AbortCurrentTransaction(void)
AbortCurrentTransaction();
break;
}
+
+ if (abort_current_transaction_hook)
+ (*abort_current_transaction_hook) ();
}
/*
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 3679799e50..3ed8cb3f94 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -192,6 +192,12 @@ static void log_disconnections(int code, Datum arg);
static void enable_statement_timeout(void);
static void disable_statement_timeout(void);
+/*
+ * Hooks for plugins to get control at end/start of
+ * start_xact_command()/finish_xact_command().
+ */
+XactCommandStart_hook_type start_xact_command_hook = NULL;
+XactCommandFinish_hook_type finish_xact_command_hook = NULL;
/* ----------------------------------------------------------------
* routines to obtain user input
@@ -2649,8 +2655,16 @@ start_xact_command(void)
* not desired, the timeout has to be disabled explicitly.
*/
enable_statement_timeout();
+
+ /*
+ * Now give loadable modules a chance to execute code before a transaction
+ * command is processed.
+ */
+ if (start_xact_command_hook)
+ (*start_xact_command_hook) ();
}
+
static void
finish_xact_command(void)
{
@@ -2659,6 +2673,13 @@ finish_xact_command(void)
if (xact_started)
{
+ /*
+ * Now give loadable modules a chance to execute code just before a
+ * transaction command is committed.
+ */
+ if (finish_xact_command_hook)
+ (*finish_xact_command_hook) ();
+
CommitTransactionCommand();
#ifdef MEMORY_CONTEXT_CHECKING
diff --git a/src/include/access/xact.h b/src/include/access/xact.h
index 7320de345c..2e866b2a91 100644
--- a/src/include/access/xact.h
+++ b/src/include/access/xact.h
@@ -467,4 +467,8 @@ extern void EnterParallelMode(void);
extern void ExitParallelMode(void);
extern bool IsInParallelMode(void);
+/* Hook for plugins to get control in start_xact_command() and finish_xact_command() */
+typedef void (*AbortCurrentTransaction_hook_type) (void);
+extern PGDLLIMPORT AbortCurrentTransaction_hook_type abort_current_transaction_hook;
+
#endif /* XACT_H */
diff --git a/src/include/tcop/pquery.h b/src/include/tcop/pquery.h
index 437642cc72..a8bef2f639 100644
--- a/src/include/tcop/pquery.h
+++ b/src/include/tcop/pquery.h
@@ -42,4 +42,10 @@ extern uint64 PortalRunFetch(Portal portal,
long count,
DestReceiver *dest);
+/* Hook for plugins to get control in start_xact_command() and finish_xact_command() */
+typedef void (*XactCommandStart_hook_type) (void);
+extern PGDLLIMPORT XactCommandStart_hook_type start_xact_command_hook;
+typedef void (*XactCommandFinish_hook_type) (void);
+extern PGDLLIMPORT XactCommandFinish_hook_type finish_xact_command_hook;
+
#endif /* PQUERY_H */
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 04464c2e76..ec28a4c914 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -13,6 +13,7 @@ A_Expr_Kind
A_Indices
A_Indirection
A_Star
+AbortCurrentTransaction_hook_type
AbsoluteTime
AccessMethodInfo
AccessPriv
@@ -2793,6 +2794,8 @@ XPVIV
XPVMG
XactCallback
XactCallbackItem
+XactCommandStart_hook_type
+XactCommandFinish_hook_type
XactEvent
XactLockTableWaitInfo
XidBoundsViolation
pg_statement_rollbackv2.tar.gz
Description: application/gzip
