postgres.git / summary / log / commit / refs

commit    fe08113aefc9dec62f218d5d712dde4ffbb0f0a3
Author:   Heikki Linnakangas <heikki.linnakangas@iki.fi>
Date:     Wed Mar 04 09:29:02 2026 +0000

    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;


[parent: 19615a44b3e6]