Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s1J6g-00B9Ae-VC for pgsql-hackers@arkaria.postgresql.org; Mon, 29 Apr 2024 04:59:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1s1J6e-00F4Dh-8K for pgsql-hackers@arkaria.postgresql.org; Mon, 29 Apr 2024 04:59:13 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s1J6d-00F4DY-RR for pgsql-hackers@lists.postgresql.org; Mon, 29 Apr 2024 04:59:12 +0000 Received: from wfhigh1-smtp.messagingengine.com ([64.147.123.152]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s1J6b-000cVE-L7 for pgsql-hackers@postgresql.org; Mon, 29 Apr 2024 04:59:12 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailfhigh.west.internal (Postfix) with ESMTP id DF6691800123; Mon, 29 Apr 2024 00:59:07 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute4.internal (MEProxy); Mon, 29 Apr 2024 00:59:08 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= cc:cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm3; t=1714366747; x=1714453147; bh=lhwDquWS0r +WbUHYJWY+o8cN8l+Hr+ccMqndSxy4IE4=; b=BN6T6oZavu+KSxqfYPs0iC+3WM rYVO6VaVBAsAPToMryiDSlhWw6SRuG2vJXtHUcSR5zuwLcjKKnfnJ2o8PNTcDyDS 4LREnZTzY/+MOJqAnGonrnDnLSGwkafNCR4YZF8speIDkUJ1pcKtRHLVe7IMIzfZ UZnNSs5heMssOJfkqjk9m0MuEDW0WkUZ/ETzm88g/fsn0Te4cCwGV2r/ayF58Fvc wv99MZRU/0m6q0xrsg4PoVotPqeBRtRqFGypv3tL9KHWxL8fmNzjO+N6QGccU+Md BNK95VgXdurXVHpQVqCnVpgYNl8/Bs01dkGUaV5gDPrYSwA4ZOqQeWyLgpPQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s= fm3; t=1714366747; x=1714453147; bh=lhwDquWS0r+WbUHYJWY+o8cN8l+H r+ccMqndSxy4IE4=; b=VA5/7TBmnFi2TJUdnhiLAYdbXvWrT/rim0Uqco7Da3qy 1QQEpZ3AsYlZP/oyJ2zmwJ6Avt26aFsDo51kBm2losQgoogm2uigovao7Wfw3sUc R0BgwtvbUiWZ/0BFTp9NIZ4mLqF9u8qGFpGu5zal9U8P+FoKddSwa7vmK1VMZ4bv YI45m74OgY7DRik2AzUY8YDyE8Yc1YH8gENSzdxt/aeRgGKcWyE9TlGfneWbHNII 3viDXP/e8sCpXnPqPik8bwSShZX6PbTfPbdcBtBGl++cMdpaMEpKbVYnI1engYny hAI/PXo0udaX6dFN9GBedjTGGiIsE0s6l3ffO7XJoQ== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrvddtledguddtucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucgfrhhlucfvnfffucdljedtmdenucfjughrpeffhf fvvefukfhfgggtuggjsehgtderredttddvnecuhfhrohhmpefoihgthhgrvghlucfrrghq uhhivghruceomhhitghhrggvlhesphgrqhhuihgvrhdrgiihiieqnecuggftrfgrthhtvg hrnhepveefteekkeduffdujedvteektdekleeggeefveeuteekjeethfdtffefgfevgedt necuffhomhgrihhnpehpghgtrdhsohenucevlhhushhtvghrufhiiigvpedtnecurfgrrh grmhepmhgrihhlfhhrohhmpehmihgthhgrvghlsehprghquhhivghrrdighiii X-ME-Proxy: Feedback-ID: i0fe9450f:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Mon, 29 Apr 2024 00:59:05 -0400 (EDT) Date: Mon, 29 Apr 2024 13:58:51 +0900 From: Michael Paquier To: Richard Guo Cc: PostgreSQL-development Subject: Re: A failure in prepared_xacts test Message-ID: References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="IyjmpWAlfQbo1B/o" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --IyjmpWAlfQbo1B/o Content-Type: multipart/mixed; boundary="5xg4ws7mrl98znrm" Content-Disposition: inline --5xg4ws7mrl98znrm Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Mon, Apr 29, 2024 at 09:12:48AM +0800, Richard Guo wrote: > Does anyone have any clue to this failure? >=20 > 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 --5xg4ws7mrl98znrm Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="2pc-tests.patch" Content-Transfer-Encoding: quoted-printable diff --git a/src/test/regress/expected/prepared_xacts.out b/src/test/regres= s/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) =20 -PREPARE TRANSACTION 'foo1'; +PREPARE TRANSACTION 'regress_foo1'; SELECT * FROM pxtest1; foobar=20 -------- @@ -25,21 +25,21 @@ SELECT * FROM pxtest1; (1 row) =20 -- Test pg_prepared_xacts system view -SELECT gid FROM pg_prepared_xacts; - gid =20 ------- - foo1 +SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid; + gid =20 +-------------- + regress_foo1 (1 row) =20 -- Test ROLLBACK PREPARED -ROLLBACK PREPARED 'foo1'; +ROLLBACK PREPARED 'regress_foo1'; SELECT * FROM pxtest1; foobar=20 -------- aaa (1 row) =20 -SELECT gid FROM pg_prepared_xacts; +SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid; gid=20 ----- (0 rows) @@ -54,14 +54,14 @@ SELECT * FROM pxtest1; ddd (2 rows) =20 -PREPARE TRANSACTION 'foo2'; +PREPARE TRANSACTION 'regress_foo2'; SELECT * FROM pxtest1; foobar=20 -------- aaa (1 row) =20 -COMMIT PREPARED 'foo2'; +COMMIT PREPARED 'regress_foo2'; SELECT * FROM pxtest1; foobar=20 -------- @@ -79,18 +79,18 @@ SELECT * FROM pxtest1; eee (2 rows) =20 -PREPARE TRANSACTION 'foo3'; -SELECT gid FROM pg_prepared_xacts; - gid =20 ------- - foo3 +PREPARE TRANSACTION 'regress_foo3'; +SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid; + gid =20 +-------------- + regress_foo3 (1 row) =20 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=20 -------- @@ -98,7 +98,7 @@ SELECT * FROM pxtest1; ddd (2 rows) =20 -ROLLBACK PREPARED 'foo3'; +ROLLBACK PREPARED 'regress_foo3'; SELECT * FROM pxtest1; foobar=20 -------- @@ -116,11 +116,11 @@ SELECT * FROM pxtest1; eee (2 rows) =20 -PREPARE TRANSACTION 'foo4'; -SELECT gid FROM pg_prepared_xacts; - gid =20 ------- - foo4 +PREPARE TRANSACTION 'regress_foo4'; +SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid; + gid =20 +-------------- + regress_foo4 (1 row) =20 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 tr= ansactions 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 =20 ------- - foo4 +PREPARE TRANSACTION 'regress_foo5'; +SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid; + gid =20 +-------------- + regress_foo4 (1 row) =20 -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=20 ----- (0 rows) @@ -165,7 +165,7 @@ SELECT pg_advisory_xact_lock_shared(1); =20 (1 row) =20 -PREPARE TRANSACTION 'foo6'; -- fails +PREPARE TRANSACTION 'regress_foo6'; -- fails ERROR: cannot PREPARE while holding both session-level and transaction-le= vel 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) =20 -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 =20 -------------- - regress-one - regress-two +SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid; + gid =20 +-------------- + regress_sub1 + regress_sub2 (2 rows) =20 -- 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 =20 -------------- - regress-one - regress-two +SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid; + gid =20 +-------------- + regress_sub1 + regress_sub2 (2 rows) =20 -- 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=20 @@ -245,20 +245,20 @@ SELECT * FROM pxtest2; (2 rows) =20 -- There should be one prepared transaction -SELECT gid FROM pg_prepared_xacts; - gid =20 -------------- - regress-two +SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid; + gid =20 +-------------- + regress_sub2 (1 row) =20 -- 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=20 ----- (0 rows) diff --git a/src/test/regress/expected/prepared_xacts_1.out b/src/test/regr= ess/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) =20 -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) =20 -- 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=20 ----- (0 rows) =20 -- 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=20 -------- aaa (1 row) =20 -SELECT gid FROM pg_prepared_xacts; +SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid; gid=20 ----- (0 rows) @@ -56,7 +56,7 @@ SELECT * FROM pxtest1; ddd (2 rows) =20 -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) =20 -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=20 -------- @@ -82,10 +82,10 @@ SELECT * FROM pxtest1; aaa (1 row) =20 -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=20 ----- (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) =20 -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=20 -------- @@ -119,10 +119,10 @@ SELECT * FROM pxtest1; aaa (1 row) =20 -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=20 ----- (0 rows) @@ -136,17 +136,17 @@ SELECT * FROM pxtest1; =20 -- 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=20 ----- (0 rows) =20 -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=20 ----- (0 rows) @@ -167,7 +167,7 @@ SELECT pg_advisory_xact_lock_shared(1); =20 (1 row) =20 -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) =20 -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=20 ----- (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=20 ----- (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=20 ----- (0 rows) =20 -- 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=20 ----- (0 rows) =20 -- 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=20 ----- (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 =3D 'bbb' WHERE foobar =3D 'aaa'; SELECT * FROM pxtest1; -PREPARE TRANSACTION 'foo1'; +PREPARE TRANSACTION 'regress_foo1'; =20 SELECT * FROM pxtest1; =20 -- Test pg_prepared_xacts system view -SELECT gid FROM pg_prepared_xacts; +SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid; =20 -- Test ROLLBACK PREPARED -ROLLBACK PREPARED 'foo1'; +ROLLBACK PREPARED 'regress_foo1'; =20 SELECT * FROM pxtest1; =20 -SELECT gid FROM pg_prepared_xacts; +SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid; =20 =20 -- Test COMMIT PREPARED BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; INSERT INTO pxtest1 VALUES ('ddd'); SELECT * FROM pxtest1; -PREPARE TRANSACTION 'foo2'; +PREPARE TRANSACTION 'regress_foo2'; =20 SELECT * FROM pxtest1; =20 -COMMIT PREPARED 'foo2'; +COMMIT PREPARED 'regress_foo2'; =20 SELECT * FROM pxtest1; =20 @@ -48,19 +48,19 @@ SELECT * FROM pxtest1; BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; UPDATE pxtest1 SET foobar =3D 'eee' WHERE foobar =3D 'ddd'; SELECT * FROM pxtest1; -PREPARE TRANSACTION 'foo3'; +PREPARE TRANSACTION 'regress_foo3'; =20 -SELECT gid FROM pg_prepared_xacts; +SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid; =20 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; INSERT INTO pxtest1 VALUES ('fff'); =20 -- This should fail, because the gid foo3 is already in use -PREPARE TRANSACTION 'foo3'; +PREPARE TRANSACTION 'regress_foo3'; =20 SELECT * FROM pxtest1; =20 -ROLLBACK PREPARED 'foo3'; +ROLLBACK PREPARED 'regress_foo3'; =20 SELECT * FROM pxtest1; =20 @@ -68,22 +68,22 @@ SELECT * FROM pxtest1; BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; UPDATE pxtest1 SET foobar =3D 'eee' WHERE foobar =3D 'ddd'; SELECT * FROM pxtest1; -PREPARE TRANSACTION 'foo4'; +PREPARE TRANSACTION 'regress_foo4'; =20 -SELECT gid FROM pg_prepared_xacts; +SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid; =20 BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT * FROM pxtest1; =20 -- This should fail, because the two transactions have a write-skew anomaly INSERT INTO pxtest1 VALUES ('fff'); -PREPARE TRANSACTION 'foo5'; +PREPARE TRANSACTION 'regress_foo5'; =20 -SELECT gid FROM pg_prepared_xacts; +SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid; =20 -ROLLBACK PREPARED 'foo4'; +ROLLBACK PREPARED 'regress_foo4'; =20 -SELECT gid FROM pg_prepared_xacts; +SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid; =20 -- 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 =20 -- 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'; =20 CREATE TABLE pxtest3(fff int); =20 @@ -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'; =20 -- No such cursor FETCH 1 FROM foo; @@ -125,7 +125,7 @@ FETCH 1 FROM foo; SELECT * FROM pxtest2; =20 -- There should be two prepared transactions -SELECT gid FROM pg_prepared_xacts; +SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid; =20 -- pxtest3 should be locked because of the pending DROP begin; @@ -136,7 +136,7 @@ rollback; \c - =20 -- 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; =20 -- pxtest3 should still be locked because of the pending DROP begin; @@ -144,19 +144,19 @@ lock table pxtest3 in access share mode nowait; rollback; =20 -- Commit table creation -COMMIT PREPARED 'regress-one'; +COMMIT PREPARED 'regress_sub1'; \d pxtest2 SELECT * FROM pxtest2; =20 -- There should be one prepared transaction -SELECT gid FROM pg_prepared_xacts; +SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid; =20 -- Commit table drop -COMMIT PREPARED 'regress-two'; +COMMIT PREPARED 'regress_sub2'; SELECT * FROM pxtest3; =20 -- There should be no prepared transactions -SELECT gid FROM pg_prepared_xacts; +SELECT gid FROM pg_prepared_xacts WHERE gid ~ '^regress_' ORDER BY gid; =20 -- Clean up DROP TABLE pxtest2; --5xg4ws7mrl98znrm-- --IyjmpWAlfQbo1B/o Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmYvKQsACgkQnvQgOdby QH0WBg/+ODdDa/3MtCBEDLFRwquWKW1gxIwNylgIj1BDkW84yVr/bF4K/1kD3KdL Qgzewjo5ncpr/djPMwwXENMqkFlKvsSrKanV/kY0kTzB1f3Sa1S/G4erOu5aQ5aY iJkNFE6WPcOu9b+Nki5jlCiK1ibwjKDHkji3wFbtDHGtlPoBACiwJ/JtdWHm1Tzq qCHYgnqjwcIqyDsRuS8PKBeh4qqfouTcwhrRNPjephKt1l554Cfu24kQuTgWFY+X e32uLdF01wmRARkD/ZzX6k/EzAwB3pDivjg3H5kwADOEaBfKliub4I0T5h2q4hf9 ozpZ4Pfc/6zEk5fqgK5qFoqcNiBbrJPEUrg2AGSyBGf386HDny6EHjtR9YqnFxXq QnMF6AFLOrBcLZXYOWZcQAIXUOwyMWpnHXbnAyjSNCc3do84NedCTP8EGs2dpPPh Gg07oZ5vJ5ZhzQ/vEq6leOZu3lCIgwhxkRu0dmmKo1PDeEljFgF7Q2D0QjwfDOyV K2tFNQMAc6jBHKVvd8gQB6jwq1YC9nAf4Y0ykLmH2ARId3J/vhZu68Tvf+8P86VQ 60bd7Jwf5ThSyXg/r73pasiXZVQ4LjQEoIw7tFCd70OroRwKXp9C3khT+BzardOo lSOf+54o0Dk2zu5ZfIbtrnnqtEyJ9sWIrC2TWlHRAzQDEbR65Ac= =j1MK -----END PGP SIGNATURE----- --IyjmpWAlfQbo1B/o--