v2-0001-Skip-prepared_xacts-test-if-max_prepared_transact.patch

From 7f08ba419a3183f3f207caffd36602f0a9bd885a Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas <heikki.linnakangas@iki.fi>
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/1009073.1772551323@sss.pgh.pa.us
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