On 03/03/2026 17:22, Tom Lane wrote:
Heikki Linnakangas <[email protected]> writes:
Ok, here's another version of Sami's repro. I realized that it doesn't
even need concurrent sessions, so I moved it to the main regression test
suite, into the 'prepared_xacts' test. Looks good?
Not directly relevant to the thing being tested here, but ...
I wonder if we couldn't reduce future maintenance effort for
prepared_xacts_1.out by modifying that test script to make an
early exit if prepared transactions are disabled.
+1, I was just thinking the same.
Here's a patch to do that. I'll backpatch this to all supported
versions, to make backpatching test changes less painful in the future.
- Heikki
From 7f08ba419a3183f3f207caffd36602f0a9bd885a Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas <[email protected]>
Date: Tue, 3 Mar 2026 18:51:38 +0200
Subject: [PATCH v2 1/2] Skip prepared_xacts test if max_prepared_transactions
< 2
This reduces maintenance overhead, as we no longer need to update the
dummy expected output file every time the .sql file changes.
Discussion: https://www.postgresql.org/message-id/[email protected]
Backpatch-through: 14
---
src/test/regress/expected/prepared_xacts.out | 7 +-
.../regress/expected/prepared_xacts_1.out | 269 +-----------------
src/test/regress/sql/prepared_xacts.sql | 7 +-
3 files changed, 14 insertions(+), 269 deletions(-)
diff --git a/src/test/regress/expected/prepared_xacts.out b/src/test/regress/expected/prepared_xacts.out
index 515a2ada9d1..6ed81e43443 100644
--- a/src/test/regress/expected/prepared_xacts.out
+++ b/src/test/regress/expected/prepared_xacts.out
@@ -1,3 +1,7 @@
+SELECT current_setting('max_prepared_transactions')::integer < 2 AS skip_test \gset
+\if :skip_test
+\quit
+\endif
--
-- PREPARED TRANSACTIONS (two-phase commit)
--
@@ -265,6 +269,5 @@ SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
-- Clean up
DROP TABLE pxtest2;
-DROP TABLE pxtest3; -- will still be there if prepared xacts are disabled
-ERROR: table "pxtest3" does not exist
+-- pxtest3 was already dropped
DROP TABLE pxtest4;
diff --git a/src/test/regress/expected/prepared_xacts_1.out b/src/test/regress/expected/prepared_xacts_1.out
index 6ad3d11898a..a21314768c3 100644
--- a/src/test/regress/expected/prepared_xacts_1.out
+++ b/src/test/regress/expected/prepared_xacts_1.out
@@ -1,266 +1,3 @@
---
--- PREPARED TRANSACTIONS (two-phase commit)
---
--- We can't readily test persistence of prepared xacts within the
--- regression script framework, unfortunately. Note that a crash
--- isn't really needed ... stopping and starting the postmaster would
--- be enough, but we can't even do that here.
--- create a simple table that we'll use in the tests
-CREATE TABLE pxtest1 (foobar VARCHAR(10));
-INSERT INTO pxtest1 VALUES ('aaa');
--- Test PREPARE TRANSACTION
-BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-UPDATE pxtest1 SET foobar = 'bbb' WHERE foobar = 'aaa';
-SELECT * FROM pxtest1;
- foobar
---------
- bbb
-(1 row)
-
-PREPARE TRANSACTION 'regress_foo1';
-ERROR: prepared transactions are disabled
-HINT: Set "max_prepared_transactions" to a nonzero value.
-SELECT * FROM pxtest1;
- foobar
---------
- aaa
-(1 row)
-
--- Test pg_prepared_xacts system view
-SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
- gid
------
-(0 rows)
-
--- Test ROLLBACK PREPARED
-ROLLBACK PREPARED 'regress_foo1';
-ERROR: prepared transaction with identifier "regress_foo1" does not exist
-SELECT * FROM pxtest1;
- foobar
---------
- aaa
-(1 row)
-
-SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
- gid
------
-(0 rows)
-
--- Test COMMIT PREPARED
-BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-INSERT INTO pxtest1 VALUES ('ddd');
-SELECT * FROM pxtest1;
- foobar
---------
- aaa
- ddd
-(2 rows)
-
-PREPARE TRANSACTION 'regress_foo2';
-ERROR: prepared transactions are disabled
-HINT: Set "max_prepared_transactions" to a nonzero value.
-SELECT * FROM pxtest1;
- foobar
---------
- aaa
-(1 row)
-
-COMMIT PREPARED 'regress_foo2';
-ERROR: prepared transaction with identifier "regress_foo2" does not exist
-SELECT * FROM pxtest1;
- foobar
---------
- aaa
-(1 row)
-
--- Test duplicate gids
-BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-UPDATE pxtest1 SET foobar = 'eee' WHERE foobar = 'ddd';
-SELECT * FROM pxtest1;
- foobar
---------
- aaa
-(1 row)
-
-PREPARE TRANSACTION 'regress_foo3';
-ERROR: prepared transactions are disabled
-HINT: Set "max_prepared_transactions" to a nonzero value.
-SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
- gid
------
-(0 rows)
-
-BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-INSERT INTO pxtest1 VALUES ('fff');
--- This should fail, because the gid foo3 is already in use
-PREPARE TRANSACTION 'regress_foo3';
-ERROR: prepared transactions are disabled
-HINT: Set "max_prepared_transactions" to a nonzero value.
-SELECT * FROM pxtest1;
- foobar
---------
- aaa
-(1 row)
-
-ROLLBACK PREPARED 'regress_foo3';
-ERROR: prepared transaction with identifier "regress_foo3" does not exist
-SELECT * FROM pxtest1;
- foobar
---------
- aaa
-(1 row)
-
--- Test serialization failure (SSI)
-BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-UPDATE pxtest1 SET foobar = 'eee' WHERE foobar = 'ddd';
-SELECT * FROM pxtest1;
- foobar
---------
- aaa
-(1 row)
-
-PREPARE TRANSACTION 'regress_foo4';
-ERROR: prepared transactions are disabled
-HINT: Set "max_prepared_transactions" to a nonzero value.
-SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
- gid
------
-(0 rows)
-
-BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-SELECT * FROM pxtest1;
- foobar
---------
- aaa
-(1 row)
-
--- This should fail, because the two transactions have a write-skew anomaly
-INSERT INTO pxtest1 VALUES ('fff');
-PREPARE TRANSACTION 'regress_foo5';
-ERROR: prepared transactions are disabled
-HINT: Set "max_prepared_transactions" to a nonzero value.
-SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
- gid
------
-(0 rows)
-
-ROLLBACK PREPARED 'regress_foo4';
-ERROR: prepared transaction with identifier "regress_foo4" does not exist
-SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
- gid
------
-(0 rows)
-
--- Clean up
-DROP TABLE pxtest1;
--- Test detection of session-level and xact-level locks on same object
-BEGIN;
-SELECT pg_advisory_lock(1);
- pg_advisory_lock
-------------------
-
-(1 row)
-
-SELECT pg_advisory_xact_lock_shared(1);
- pg_advisory_xact_lock_shared
-------------------------------
-
-(1 row)
-
-PREPARE TRANSACTION 'regress_foo6'; -- fails
-ERROR: prepared transactions are disabled
-HINT: Set "max_prepared_transactions" to a nonzero value.
--- Test subtransactions
-BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
- CREATE TABLE pxtest2 (a int);
- INSERT INTO pxtest2 VALUES (1);
- SAVEPOINT a;
- INSERT INTO pxtest2 VALUES (2);
- ROLLBACK TO a;
- SAVEPOINT b;
- INSERT INTO pxtest2 VALUES (3);
-PREPARE TRANSACTION 'regress_sub1';
-ERROR: prepared transactions are disabled
-HINT: Set "max_prepared_transactions" to a nonzero value.
-CREATE TABLE pxtest3(fff int);
--- Test shared invalidation
-BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
- DROP TABLE pxtest3;
- CREATE TABLE pxtest4 (a int);
- INSERT INTO pxtest4 VALUES (1);
- INSERT INTO pxtest4 VALUES (2);
- DECLARE foo CURSOR FOR SELECT * FROM pxtest4;
- -- Fetch 1 tuple, keeping the cursor open
- FETCH 1 FROM foo;
- a
----
- 1
-(1 row)
-
-PREPARE TRANSACTION 'regress_sub2';
-ERROR: prepared transactions are disabled
-HINT: Set "max_prepared_transactions" to a nonzero value.
--- No such cursor
-FETCH 1 FROM foo;
-ERROR: cursor "foo" does not exist
--- Table doesn't exist, the creation hasn't been committed yet
-SELECT * FROM pxtest2;
-ERROR: relation "pxtest2" does not exist
-LINE 1: SELECT * FROM pxtest2;
- ^
--- There should be two prepared transactions
-SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
- gid
------
-(0 rows)
-
--- pxtest3 should be locked because of the pending DROP
-begin;
-lock table pxtest3 in access share mode nowait;
-rollback;
--- Disconnect, we will continue testing in a different backend
-\c -
--- There should still be two prepared transactions
-SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
- gid
------
-(0 rows)
-
--- pxtest3 should still be locked because of the pending DROP
-begin;
-lock table pxtest3 in access share mode nowait;
-rollback;
--- Commit table creation
-COMMIT PREPARED 'regress_sub1';
-ERROR: prepared transaction with identifier "regress_sub1" does not exist
-\d pxtest2
-SELECT * FROM pxtest2;
-ERROR: relation "pxtest2" does not exist
-LINE 1: SELECT * FROM pxtest2;
- ^
--- There should be one prepared transaction
-SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
- gid
------
-(0 rows)
-
--- Commit table drop
-COMMIT PREPARED 'regress_sub2';
-ERROR: prepared transaction with identifier "regress_sub2" does not exist
-SELECT * FROM pxtest3;
- fff
------
-(0 rows)
-
--- There should be no prepared transactions
-SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
- gid
------
-(0 rows)
-
--- Clean up
-DROP TABLE pxtest2;
-ERROR: table "pxtest2" does not exist
-DROP TABLE pxtest3; -- will still be there if prepared xacts are disabled
-DROP TABLE pxtest4;
-ERROR: table "pxtest4" does not exist
+SELECT current_setting('max_prepared_transactions')::integer < 2 AS skip_test \gset
+\if :skip_test
+\quit
diff --git a/src/test/regress/sql/prepared_xacts.sql b/src/test/regress/sql/prepared_xacts.sql
index ade3a2672a8..bac8455e861 100644
--- a/src/test/regress/sql/prepared_xacts.sql
+++ b/src/test/regress/sql/prepared_xacts.sql
@@ -1,3 +1,8 @@
+SELECT current_setting('max_prepared_transactions')::integer < 2 AS skip_test \gset
+\if :skip_test
+\quit
+\endif
+
--
-- PREPARED TRANSACTIONS (two-phase commit)
--
@@ -160,5 +165,5 @@ SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
-- Clean up
DROP TABLE pxtest2;
-DROP TABLE pxtest3; -- will still be there if prepared xacts are disabled
+-- pxtest3 was already dropped
DROP TABLE pxtest4;
--
2.47.3
From bbc15a0dc7126515b7d165011659bf4e07803ed6 Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas <[email protected]>
Date: Tue, 3 Mar 2026 17:03:23 +0200
Subject: [PATCH v2 2/2] Add test for row-locking and multixids with prepared
transactions
This is a repro for the issue fixed in commit ccae90abdb. Backpatch to
v17 like that commit, although that's a little arbitrary as this test
would work on older versions too.
Author: Sami Imseih <[email protected]>
Discussion: https://www.postgresql.org/message-id/CAA5RZ0twq5bNMq0r0QNoopQnAEv+J3qJNCrLs7HVqTEntBhJ=g...@mail.gmail.com
Backpatch-through: 17
---
src/test/regress/expected/prepared_xacts.out | 30 ++++++++++++++++++++
src/test/regress/sql/prepared_xacts.sql | 25 ++++++++++++++++
2 files changed, 55 insertions(+)
diff --git a/src/test/regress/expected/prepared_xacts.out b/src/test/regress/expected/prepared_xacts.out
index 6ed81e43443..ac4ebf6ca36 100644
--- a/src/test/regress/expected/prepared_xacts.out
+++ b/src/test/regress/expected/prepared_xacts.out
@@ -267,7 +267,37 @@ SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
-----
(0 rows)
+-- Test row-level locks held by prepared transactions
+CREATE TABLE pxtest_rowlock (id int PRIMARY KEY, data text);
+INSERT INTO pxtest_rowlock VALUES (1, 'test data');
+BEGIN;
+SELECT * FROM pxtest_rowlock WHERE id = 1 FOR SHARE;
+ id | data
+----+-----------
+ 1 | test data
+(1 row)
+
+PREPARE TRANSACTION 'regress_p1';
+-- Should fail because the row is locked
+SELECT * FROM pxtest_rowlock WHERE id = 1 FOR UPDATE NOWAIT;
+ERROR: could not obtain lock on row in relation "pxtest_rowlock"
+-- Test prepared transactions that participate in multixacts. For
+-- that, lock the same row again, creating a multixid.
+BEGIN;
+SELECT * FROM pxtest_rowlock WHERE id = 1 FOR SHARE;
+ id | data
+----+-----------
+ 1 | test data
+(1 row)
+
+PREPARE TRANSACTION 'regress_p2';
+-- Should fail because the row is locked
+SELECT * FROM pxtest_rowlock WHERE id = 1 FOR UPDATE NOWAIT;
+ERROR: could not obtain lock on row in relation "pxtest_rowlock"
+ROLLBACK PREPARED 'regress_p1';
+ROLLBACK PREPARED 'regress_p2';
-- Clean up
DROP TABLE pxtest2;
-- pxtest3 was already dropped
DROP TABLE pxtest4;
+DROP TABLE pxtest_rowlock;
diff --git a/src/test/regress/sql/prepared_xacts.sql b/src/test/regress/sql/prepared_xacts.sql
index bac8455e861..b0712b153e0 100644
--- a/src/test/regress/sql/prepared_xacts.sql
+++ b/src/test/regress/sql/prepared_xacts.sql
@@ -163,7 +163,32 @@ SELECT * FROM pxtest3;
-- There should be no prepared transactions
SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
+
+-- Test row-level locks held by prepared transactions
+CREATE TABLE pxtest_rowlock (id int PRIMARY KEY, data text);
+INSERT INTO pxtest_rowlock VALUES (1, 'test data');
+
+BEGIN;
+SELECT * FROM pxtest_rowlock WHERE id = 1 FOR SHARE;
+PREPARE TRANSACTION 'regress_p1';
+
+-- Should fail because the row is locked
+SELECT * FROM pxtest_rowlock WHERE id = 1 FOR UPDATE NOWAIT;
+
+-- Test prepared transactions that participate in multixacts. For
+-- that, lock the same row again, creating a multixid.
+BEGIN;
+SELECT * FROM pxtest_rowlock WHERE id = 1 FOR SHARE;
+PREPARE TRANSACTION 'regress_p2';
+
+-- Should fail because the row is locked
+SELECT * FROM pxtest_rowlock WHERE id = 1 FOR UPDATE NOWAIT;
+
+ROLLBACK PREPARED 'regress_p1';
+ROLLBACK PREPARED 'regress_p2';
+
-- Clean up
DROP TABLE pxtest2;
-- pxtest3 was already dropped
DROP TABLE pxtest4;
+DROP TABLE pxtest_rowlock;
--
2.47.3
