0001-Add-test-for-row-locking-and-multixids-with-prepared.patch

From 2e0f81e75d89eb3eb4dcc6411723cac3d07af25e Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas <heikki.linnakangas@iki.fi>
Date: Tue, 3 Mar 2026 17:03:23 +0200
Subject: [PATCH 1/1] 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 <samimseih@gmail.com>
Discussion: XXX
Backpatch-through: 17
---
 src/test/regress/expected/prepared_xacts.out  | 30 +++++++++++++
 .../regress/expected/prepared_xacts_1.out     | 44 +++++++++++++++++++
 src/test/regress/sql/prepared_xacts.sql       | 25 +++++++++++
 3 files changed, 99 insertions(+)

diff --git a/src/test/regress/expected/prepared_xacts.out b/src/test/regress/expected/prepared_xacts.out
index 515a2ada9d1..71d2e591622 100644
--- a/src/test/regress/expected/prepared_xacts.out
+++ b/src/test/regress/expected/prepared_xacts.out
@@ -263,8 +263,38 @@ 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;
 DROP TABLE pxtest3;  -- will still be there if prepared xacts are disabled
 ERROR:  table "pxtest3" does not exist
 DROP TABLE pxtest4;
+DROP TABLE pxtest_rowlock;
diff --git a/src/test/regress/expected/prepared_xacts_1.out b/src/test/regress/expected/prepared_xacts_1.out
index 6ad3d11898a..f70b4724fc5 100644
--- a/src/test/regress/expected/prepared_xacts_1.out
+++ b/src/test/regress/expected/prepared_xacts_1.out
@@ -258,9 +258,53 @@ 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';
+ERROR:  prepared transactions are disabled
+HINT:  Set "max_prepared_transactions" to a nonzero value.
+-- Should fail because the row is locked
+SELECT * FROM pxtest_rowlock WHERE id = 1 FOR UPDATE NOWAIT;
+ id |   data    
+----+-----------
+  1 | test data
+(1 row)
+
+-- 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';
+ERROR:  prepared transactions are disabled
+HINT:  Set "max_prepared_transactions" to a nonzero value.
+-- Should fail because the row is locked
+SELECT * FROM pxtest_rowlock WHERE id = 1 FOR UPDATE NOWAIT;
+ id |   data    
+----+-----------
+  1 | test data
+(1 row)
+
+ROLLBACK PREPARED 'regress_p1';
+ERROR:  prepared transaction with identifier "regress_p1" does not exist
+ROLLBACK PREPARED 'regress_p2';
+ERROR:  prepared transaction with identifier "regress_p2" does not exist
 -- 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
+DROP TABLE pxtest_rowlock;
diff --git a/src/test/regress/sql/prepared_xacts.sql b/src/test/regress/sql/prepared_xacts.sql
index ade3a2672a8..5d2f06798c6 100644
--- a/src/test/regress/sql/prepared_xacts.sql
+++ b/src/test/regress/sql/prepared_xacts.sql
@@ -158,7 +158,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;
 DROP TABLE pxtest3;  -- will still be there if prepared xacts are disabled
 DROP TABLE pxtest4;
+DROP TABLE pxtest_rowlock;
-- 
2.47.3