From 2e0f81e75d89eb3eb4dcc6411723cac3d07af25e Mon Sep 17 00:00:00 2001 From: Heikki Linnakangas 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 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