On 03/03/2026 14:02, Yura Sokolov wrote:
02.03.2026 22:09, Sami Imseih пишет:
It says “Each is indexed by ProcNumber”, but it’s no longer accurate for
OldestMemberMXactId prepared-xact entries, which now use index (procno -
FIRST_PREPARED_XACT_PROC_NUMBER).
Fixed those and some other comment work, and pushed. Thanks!
Thanks! what are your thoughts about adding a test like the one
here [1] ? This allows us to test correct handling of prepared
transaction dummy procs. The asserts added will not cover
this case.
What do you think?
[1]
[https://www.postgresql.org/message-id/CAA5RZ0twq5bNMq0r0QNoopQnAEv%2BJ3qJNCrLs7HVqTEntBhJ%3Dg%40mail.gmail.com]
I support: test for bug fixed is a good thing.
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?
- Heikki
From 2e0f81e75d89eb3eb4dcc6411723cac3d07af25e Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas <[email protected]>
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 <[email protected]>
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
