v2-0002-Add-test-for-row-locking-and-multixids-with-prepa.patch

From bbc15a0dc7126515b7d165011659bf4e07803ed6 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 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 <samimseih@gmail.com>
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