On Mon, Apr 29, 2024 at 09:12:48AM +0800, Richard Guo wrote: > Does anyone have any clue to this failure? > > FWIW, after another run of this test, the failure just disappears. Does > it suggest that the test case is flaky?
If you grep the source tree, you'd notice that a prepared transaction named gxid only exists in the 2PC tests of ECPG, in src/interfaces/ecpg/test/sql/twophase.pgc. So the origin of the failure comes from a race condition due to test parallelization, because the scan of pg_prepared_xacts affects all databases with installcheck, and in your case it means that the scan of pg_prepared_xacts was running in parallel of the ECPG tests with an installcheck. The only location in the whole tree where we want to do predictible scans of pg_prepared_xacts is prepared_xacts.sql, so rather than playing with 2PC transactions across a bunch of tests, I think that we should do two things, both touching prepared_xacts.sql: - The 2PC transactions run in the main regression test suite should use names that would be unlikely used elsewhere. - Limit the scans of pg_prepared_xacts on these name patterns to avoid interferences. See for example the attached with both expected outputs updated depending on the value set for max_prepared_transactions in the backend. There may be an argument in back-patching that, but I don't recall seeing this failure in the CI, so perhaps that's not worth bothering with. What do you think? -- Michael
diff --git a/src/test/regress/expected/prepared_xacts.out b/src/test/regress/expected/prepared_xacts.out
index ba8e3ccc6c..515a2ada9d 100644
--- a/src/test/regress/expected/prepared_xacts.out
+++ b/src/test/regress/expected/prepared_xacts.out
@@ -17,7 +17,7 @@ SELECT * FROM pxtest1;
bbb
(1 row)
-PREPARE TRANSACTION 'foo1';
+PREPARE TRANSACTION 'regress_foo1';
SELECT * FROM pxtest1;
foobar
--------
@@ -25,21 +25,21 @@ SELECT * FROM pxtest1;
(1 row)
-- Test pg_prepared_xacts system view
-SELECT gid FROM pg_prepared_xacts;
- gid
-------
- foo1
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
+ gid
+--------------
+ regress_foo1
(1 row)
-- Test ROLLBACK PREPARED
-ROLLBACK PREPARED 'foo1';
+ROLLBACK PREPARED 'regress_foo1';
SELECT * FROM pxtest1;
foobar
--------
aaa
(1 row)
-SELECT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
gid
-----
(0 rows)
@@ -54,14 +54,14 @@ SELECT * FROM pxtest1;
ddd
(2 rows)
-PREPARE TRANSACTION 'foo2';
+PREPARE TRANSACTION 'regress_foo2';
SELECT * FROM pxtest1;
foobar
--------
aaa
(1 row)
-COMMIT PREPARED 'foo2';
+COMMIT PREPARED 'regress_foo2';
SELECT * FROM pxtest1;
foobar
--------
@@ -79,18 +79,18 @@ SELECT * FROM pxtest1;
eee
(2 rows)
-PREPARE TRANSACTION 'foo3';
-SELECT gid FROM pg_prepared_xacts;
- gid
-------
- foo3
+PREPARE TRANSACTION 'regress_foo3';
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
+ gid
+--------------
+ regress_foo3
(1 row)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO pxtest1 VALUES ('fff');
-- This should fail, because the gid foo3 is already in use
-PREPARE TRANSACTION 'foo3';
-ERROR: transaction identifier "foo3" is already in use
+PREPARE TRANSACTION 'regress_foo3';
+ERROR: transaction identifier "regress_foo3" is already in use
SELECT * FROM pxtest1;
foobar
--------
@@ -98,7 +98,7 @@ SELECT * FROM pxtest1;
ddd
(2 rows)
-ROLLBACK PREPARED 'foo3';
+ROLLBACK PREPARED 'regress_foo3';
SELECT * FROM pxtest1;
foobar
--------
@@ -116,11 +116,11 @@ SELECT * FROM pxtest1;
eee
(2 rows)
-PREPARE TRANSACTION 'foo4';
-SELECT gid FROM pg_prepared_xacts;
- gid
-------
- foo4
+PREPARE TRANSACTION 'regress_foo4';
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
+ gid
+--------------
+ regress_foo4
(1 row)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
@@ -136,15 +136,15 @@ INSERT INTO pxtest1 VALUES ('fff');
ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during write.
HINT: The transaction might succeed if retried.
-PREPARE TRANSACTION 'foo5';
-SELECT gid FROM pg_prepared_xacts;
- gid
-------
- foo4
+PREPARE TRANSACTION 'regress_foo5';
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
+ gid
+--------------
+ regress_foo4
(1 row)
-ROLLBACK PREPARED 'foo4';
-SELECT gid FROM pg_prepared_xacts;
+ROLLBACK PREPARED 'regress_foo4';
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
gid
-----
(0 rows)
@@ -165,7 +165,7 @@ SELECT pg_advisory_xact_lock_shared(1);
(1 row)
-PREPARE TRANSACTION 'foo6'; -- fails
+PREPARE TRANSACTION 'regress_foo6'; -- fails
ERROR: cannot PREPARE while holding both session-level and transaction-level locks on the same object
-- Test subtransactions
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
@@ -176,7 +176,7 @@ BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ROLLBACK TO a;
SAVEPOINT b;
INSERT INTO pxtest2 VALUES (3);
-PREPARE TRANSACTION 'regress-one';
+PREPARE TRANSACTION 'regress_sub1';
CREATE TABLE pxtest3(fff int);
-- Test shared invalidation
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
@@ -192,7 +192,7 @@ BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1
(1 row)
-PREPARE TRANSACTION 'regress-two';
+PREPARE TRANSACTION 'regress_sub2';
-- No such cursor
FETCH 1 FROM foo;
ERROR: cursor "foo" does not exist
@@ -202,11 +202,11 @@ ERROR: relation "pxtest2" does not exist
LINE 1: SELECT * FROM pxtest2;
^
-- There should be two prepared transactions
-SELECT gid FROM pg_prepared_xacts;
- gid
--------------
- regress-one
- regress-two
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
+ gid
+--------------
+ regress_sub1
+ regress_sub2
(2 rows)
-- pxtest3 should be locked because of the pending DROP
@@ -217,11 +217,11 @@ rollback;
-- Disconnect, we will continue testing in a different backend
\c -
-- There should still be two prepared transactions
-SELECT gid FROM pg_prepared_xacts;
- gid
--------------
- regress-one
- regress-two
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
+ gid
+--------------
+ regress_sub1
+ regress_sub2
(2 rows)
-- pxtest3 should still be locked because of the pending DROP
@@ -230,7 +230,7 @@ lock table pxtest3 in access share mode nowait;
ERROR: could not obtain lock on relation "pxtest3"
rollback;
-- Commit table creation
-COMMIT PREPARED 'regress-one';
+COMMIT PREPARED 'regress_sub1';
\d pxtest2
Table "public.pxtest2"
Column | Type | Collation | Nullable | Default
@@ -245,20 +245,20 @@ SELECT * FROM pxtest2;
(2 rows)
-- There should be one prepared transaction
-SELECT gid FROM pg_prepared_xacts;
- gid
--------------
- regress-two
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
+ gid
+--------------
+ regress_sub2
(1 row)
-- Commit table drop
-COMMIT PREPARED 'regress-two';
+COMMIT PREPARED 'regress_sub2';
SELECT * FROM pxtest3;
ERROR: relation "pxtest3" does not exist
LINE 1: SELECT * FROM pxtest3;
^
-- There should be no prepared transactions
-SELECT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
gid
-----
(0 rows)
diff --git a/src/test/regress/expected/prepared_xacts_1.out b/src/test/regress/expected/prepared_xacts_1.out
index 2cd50ad947..7168f86bf9 100644
--- a/src/test/regress/expected/prepared_xacts_1.out
+++ b/src/test/regress/expected/prepared_xacts_1.out
@@ -17,7 +17,7 @@ SELECT * FROM pxtest1;
bbb
(1 row)
-PREPARE TRANSACTION 'foo1';
+PREPARE TRANSACTION 'regress_foo1';
ERROR: prepared transactions are disabled
HINT: Set max_prepared_transactions to a nonzero value.
SELECT * FROM pxtest1;
@@ -27,21 +27,21 @@ SELECT * FROM pxtest1;
(1 row)
-- Test pg_prepared_xacts system view
-SELECT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
gid
-----
(0 rows)
-- Test ROLLBACK PREPARED
-ROLLBACK PREPARED 'foo1';
-ERROR: prepared transaction with identifier "foo1" does not exist
+ROLLBACK PREPARED 'regress_foo1';
+ERROR: prepared transaction with identifier "regress_foo1" does not exist
SELECT * FROM pxtest1;
foobar
--------
aaa
(1 row)
-SELECT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
gid
-----
(0 rows)
@@ -56,7 +56,7 @@ SELECT * FROM pxtest1;
ddd
(2 rows)
-PREPARE TRANSACTION 'foo2';
+PREPARE TRANSACTION 'regress_foo2';
ERROR: prepared transactions are disabled
HINT: Set max_prepared_transactions to a nonzero value.
SELECT * FROM pxtest1;
@@ -65,8 +65,8 @@ SELECT * FROM pxtest1;
aaa
(1 row)
-COMMIT PREPARED 'foo2';
-ERROR: prepared transaction with identifier "foo2" does not exist
+COMMIT PREPARED 'regress_foo2';
+ERROR: prepared transaction with identifier "regress_foo2" does not exist
SELECT * FROM pxtest1;
foobar
--------
@@ -82,10 +82,10 @@ SELECT * FROM pxtest1;
aaa
(1 row)
-PREPARE TRANSACTION 'foo3';
+PREPARE TRANSACTION 'regress_foo3';
ERROR: prepared transactions are disabled
HINT: Set max_prepared_transactions to a nonzero value.
-SELECT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
gid
-----
(0 rows)
@@ -93,7 +93,7 @@ SELECT gid FROM pg_prepared_xacts;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO pxtest1 VALUES ('fff');
-- This should fail, because the gid foo3 is already in use
-PREPARE TRANSACTION 'foo3';
+PREPARE TRANSACTION 'regress_foo3';
ERROR: prepared transactions are disabled
HINT: Set max_prepared_transactions to a nonzero value.
SELECT * FROM pxtest1;
@@ -102,8 +102,8 @@ SELECT * FROM pxtest1;
aaa
(1 row)
-ROLLBACK PREPARED 'foo3';
-ERROR: prepared transaction with identifier "foo3" does not exist
+ROLLBACK PREPARED 'regress_foo3';
+ERROR: prepared transaction with identifier "regress_foo3" does not exist
SELECT * FROM pxtest1;
foobar
--------
@@ -119,10 +119,10 @@ SELECT * FROM pxtest1;
aaa
(1 row)
-PREPARE TRANSACTION 'foo4';
+PREPARE TRANSACTION 'regress_foo4';
ERROR: prepared transactions are disabled
HINT: Set max_prepared_transactions to a nonzero value.
-SELECT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
gid
-----
(0 rows)
@@ -136,17 +136,17 @@ SELECT * FROM pxtest1;
-- This should fail, because the two transactions have a write-skew anomaly
INSERT INTO pxtest1 VALUES ('fff');
-PREPARE TRANSACTION 'foo5';
+PREPARE TRANSACTION 'regress_foo5';
ERROR: prepared transactions are disabled
HINT: Set max_prepared_transactions to a nonzero value.
-SELECT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
gid
-----
(0 rows)
-ROLLBACK PREPARED 'foo4';
-ERROR: prepared transaction with identifier "foo4" does not exist
-SELECT gid FROM pg_prepared_xacts;
+ROLLBACK PREPARED 'regress_foo4';
+ERROR: prepared transaction with identifier "regress_foo4" does not exist
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
gid
-----
(0 rows)
@@ -167,7 +167,7 @@ SELECT pg_advisory_xact_lock_shared(1);
(1 row)
-PREPARE TRANSACTION 'foo6'; -- fails
+PREPARE TRANSACTION 'regress_foo6'; -- fails
ERROR: prepared transactions are disabled
HINT: Set max_prepared_transactions to a nonzero value.
-- Test subtransactions
@@ -179,7 +179,7 @@ BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ROLLBACK TO a;
SAVEPOINT b;
INSERT INTO pxtest2 VALUES (3);
-PREPARE TRANSACTION 'regress-one';
+PREPARE TRANSACTION 'regress_sub1';
ERROR: prepared transactions are disabled
HINT: Set max_prepared_transactions to a nonzero value.
CREATE TABLE pxtest3(fff int);
@@ -197,7 +197,7 @@ BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1
(1 row)
-PREPARE TRANSACTION 'regress-two';
+PREPARE TRANSACTION 'regress_sub2';
ERROR: prepared transactions are disabled
HINT: Set max_prepared_transactions to a nonzero value.
-- No such cursor
@@ -209,7 +209,7 @@ ERROR: relation "pxtest2" does not exist
LINE 1: SELECT * FROM pxtest2;
^
-- There should be two prepared transactions
-SELECT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
gid
-----
(0 rows)
@@ -221,7 +221,7 @@ rollback;
-- Disconnect, we will continue testing in a different backend
\c -
-- There should still be two prepared transactions
-SELECT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
gid
-----
(0 rows)
@@ -231,29 +231,29 @@ begin;
lock table pxtest3 in access share mode nowait;
rollback;
-- Commit table creation
-COMMIT PREPARED 'regress-one';
-ERROR: prepared transaction with identifier "regress-one" does not exist
+COMMIT PREPARED 'regress_sub1';
+ERROR: prepared transaction with identifier "regress_sub1" does not exist
\d pxtest2
SELECT * FROM pxtest2;
ERROR: relation "pxtest2" does not exist
LINE 1: SELECT * FROM pxtest2;
^
-- There should be one prepared transaction
-SELECT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
gid
-----
(0 rows)
-- Commit table drop
-COMMIT PREPARED 'regress-two';
-ERROR: prepared transaction with identifier "regress-two" does not exist
+COMMIT PREPARED 'regress_sub2';
+ERROR: prepared transaction with identifier "regress_sub2" does not exist
SELECT * FROM pxtest3;
fff
-----
(0 rows)
-- There should be no prepared transactions
-SELECT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
gid
-----
(0 rows)
diff --git a/src/test/regress/sql/prepared_xacts.sql b/src/test/regress/sql/prepared_xacts.sql
index 2f0bb55bb4..ade3a2672a 100644
--- a/src/test/regress/sql/prepared_xacts.sql
+++ b/src/test/regress/sql/prepared_xacts.sql
@@ -17,30 +17,30 @@ INSERT INTO pxtest1 VALUES ('aaa');
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE pxtest1 SET foobar = 'bbb' WHERE foobar = 'aaa';
SELECT * FROM pxtest1;
-PREPARE TRANSACTION 'foo1';
+PREPARE TRANSACTION 'regress_foo1';
SELECT * FROM pxtest1;
-- Test pg_prepared_xacts system view
-SELECT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
-- Test ROLLBACK PREPARED
-ROLLBACK PREPARED 'foo1';
+ROLLBACK PREPARED 'regress_foo1';
SELECT * FROM pxtest1;
-SELECT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
-- Test COMMIT PREPARED
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO pxtest1 VALUES ('ddd');
SELECT * FROM pxtest1;
-PREPARE TRANSACTION 'foo2';
+PREPARE TRANSACTION 'regress_foo2';
SELECT * FROM pxtest1;
-COMMIT PREPARED 'foo2';
+COMMIT PREPARED 'regress_foo2';
SELECT * FROM pxtest1;
@@ -48,19 +48,19 @@ SELECT * FROM pxtest1;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE pxtest1 SET foobar = 'eee' WHERE foobar = 'ddd';
SELECT * FROM pxtest1;
-PREPARE TRANSACTION 'foo3';
+PREPARE TRANSACTION 'regress_foo3';
-SELECT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO pxtest1 VALUES ('fff');
-- This should fail, because the gid foo3 is already in use
-PREPARE TRANSACTION 'foo3';
+PREPARE TRANSACTION 'regress_foo3';
SELECT * FROM pxtest1;
-ROLLBACK PREPARED 'foo3';
+ROLLBACK PREPARED 'regress_foo3';
SELECT * FROM pxtest1;
@@ -68,22 +68,22 @@ SELECT * FROM pxtest1;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE pxtest1 SET foobar = 'eee' WHERE foobar = 'ddd';
SELECT * FROM pxtest1;
-PREPARE TRANSACTION 'foo4';
+PREPARE TRANSACTION 'regress_foo4';
-SELECT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM pxtest1;
-- This should fail, because the two transactions have a write-skew anomaly
INSERT INTO pxtest1 VALUES ('fff');
-PREPARE TRANSACTION 'foo5';
+PREPARE TRANSACTION 'regress_foo5';
-SELECT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
-ROLLBACK PREPARED 'foo4';
+ROLLBACK PREPARED 'regress_foo4';
-SELECT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
-- Clean up
DROP TABLE pxtest1;
@@ -92,7 +92,7 @@ DROP TABLE pxtest1;
BEGIN;
SELECT pg_advisory_lock(1);
SELECT pg_advisory_xact_lock_shared(1);
-PREPARE TRANSACTION 'foo6'; -- fails
+PREPARE TRANSACTION 'regress_foo6'; -- fails
-- Test subtransactions
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
@@ -103,7 +103,7 @@ BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ROLLBACK TO a;
SAVEPOINT b;
INSERT INTO pxtest2 VALUES (3);
-PREPARE TRANSACTION 'regress-one';
+PREPARE TRANSACTION 'regress_sub1';
CREATE TABLE pxtest3(fff int);
@@ -116,7 +116,7 @@ BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
DECLARE foo CURSOR FOR SELECT * FROM pxtest4;
-- Fetch 1 tuple, keeping the cursor open
FETCH 1 FROM foo;
-PREPARE TRANSACTION 'regress-two';
+PREPARE TRANSACTION 'regress_sub2';
-- No such cursor
FETCH 1 FROM foo;
@@ -125,7 +125,7 @@ FETCH 1 FROM foo;
SELECT * FROM pxtest2;
-- There should be two prepared transactions
-SELECT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
-- pxtest3 should be locked because of the pending DROP
begin;
@@ -136,7 +136,7 @@ rollback;
\c -
-- There should still be two prepared transactions
-SELECT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
-- pxtest3 should still be locked because of the pending DROP
begin;
@@ -144,19 +144,19 @@ lock table pxtest3 in access share mode nowait;
rollback;
-- Commit table creation
-COMMIT PREPARED 'regress-one';
+COMMIT PREPARED 'regress_sub1';
\d pxtest2
SELECT * FROM pxtest2;
-- There should be one prepared transaction
-SELECT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
-- Commit table drop
-COMMIT PREPARED 'regress-two';
+COMMIT PREPARED 'regress_sub2';
SELECT * FROM pxtest3;
-- There should be no prepared transactions
-SELECT gid FROM pg_prepared_xacts;
+SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid;
-- Clean up
DROP TABLE pxtest2;
signature.asc
Description: PGP signature
