From: Heikki Linnakangas <hlinnaka@iki.fi>
To: Yura Sokolov <y.sokolov@postgrespro.ru>
To: Sami Imseih <samimseih@gmail.com>
Cc: Chao Li <li.evan.chao@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Subject: Re: Fix bug in multixact Oldest*MXactId initialization and access
Date: Tue, 3 Mar 2026 17:11:05 +0200
Message-ID: <e7c8a4ef-458a-4e72-be09-51c65ab446d5@iki.fi> (raw)
In-Reply-To: <120550bf-ca50-4a07-91b1-a88f1434ee8b@postgrespro.ru>
References: <7acc94b0-ea82-4657-b1b0-77842cb7a60c@postgrespro.ru>
<c3eaaac1-d244-4e4d-b5d7-06c284108eac@postgrespro.ru>
<C991344D-F38A-4EEC-903A-72B52FF887FA@gmail.com>
<bf2951b4-687b-4bd3-a072-46deb463f177@postgrespro.ru>
<CAA5RZ0t8iGnqO3JcAZP1VEjCspcnZtQ0GFFVGLNLAsFqfHqRsw@mail.gmail.com>
<ebeb0db1-0eb2-4439-a6c8-1d96b60b25ad@postgrespro.ru>
<CAA5RZ0s+rr=DVkFnMwupgnCAvPjJkjQ3jr5=7X_=Sb5Mr02-Mg@mail.gmail.com>
<3d7a2207-0e61-4d95-bc00-e5248956a32b@postgrespro.ru>
<366b6f95-613d-4ef8-82e4-bb2fd06d9c4d@iki.fi>
<CAA5RZ0vXCMHEsPg7wFXmBOEPgWB8NvGKWx9cZsz7F99mFU+xLw@mail.gmail.com>
<231cd950-73e9-4db5-a516-c5216af7b93a@iki.fi>
<944d7897-3ec3-47bb-9e93-b99f2ef3124a@postgrespro.ru>
<CAA5RZ0umyHiQNwr1m6FVF0WPCP3QAFAzdF=mQ+_u-vknhOBYWw@mail.gmail.com>
<A0249625-0EF4-4068-854F-0592139DFBC1@gmail.com>
<c585168a-210a-4728-8e5e-8e9384126ccd@iki.fi>
<CAA5RZ0s3wL+4RJRRtJVK1VxEgAmYrj7ftfSuAJHJ+oDfTabQbg@mail.gmail.com>
<120550bf-ca50-4a07-91b1-a88f1434ee8b@postgrespro.ru>
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...]
> 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
Attachments:0001-Add-test-for-row-locking-and-multixids-with-prepared.patch (text/x-patch, 5.5K) download
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.outindex 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.outindex 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.sqlindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: hlinnaka@iki.fi, y.sokolov@postgrespro.ru, samimseih@gmail.com, li.evan.chao@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Fix bug in multixact Oldest*MXactId initialization and access
In-Reply-To: <e7c8a4ef-458a-4e72-be09-51c65ab446d5@iki.fi>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox