From: Marina Polyakova <m.polyakova@postgrespro.ru>
To: Michael Paquier <michael@paquier.xyz>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org, Noah Misch <noah@leadboat.com>
Cc: exclusion@gmail.com
Subject: Re: Conflict between regression tests namespace & transactions due to recent changes
Date: Tue, 16 May 2023 11:02:45 +0300
Message-ID: <ac5bfbf1012b59f56af5ad6715adc0d3@postgrespro.ru> (raw)
In-Reply-To: <ZGK96KZaT1DUKGgp@paquier.xyz>
References: <80d0201636665d82185942e7112257b4@postgrespro.ru>
<2165312.1684167368@sss.pgh.pa.us>
<f62cd65e1b179783521f424a42ef3b27@postgrespro.ru>
<ZGK96KZaT1DUKGgp@paquier.xyz>
On 2023-05-16 02:19, Michael Paquier wrote:
> On Mon, May 15, 2023 at 11:23:18PM +0300, Marina Polyakova wrote:
>> Maybe use a separate schema for all new objects in the transaction
>> test?..
>> See diff_set_tx_schema.patch.
>
> Sure, you could do that to bypass the failure (without the "public"
> actually?), leaving non-generic names around. Still I'd agree with
> Tom here and just rename the objects to something more in line with
> the context of the test to make things a bit more greppable. These
> could be renamed as transaction_tab or transaction_view, for example.
> --
> Michael
It confuses me a little that different methods are used for the same
purpose. But the namespace test checks schemas. So see
diff_abc_to_txn_table.patch which replaces abc with txn_table in the
transaction test.
--
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachments:
[text/x-diff] diff_abc_to_txn_table.patch (15.3K, ../ac5bfbf1012b59f56af5ad6715adc0d3@postgrespro.ru/2-diff_abc_to_txn_table.patch)
download | inline diff:diff --git a/src/test/regress/expected/transactions.out b/src/test/regress/expected/transactions.outindex 2b2cff7d9120a139532d1a6a2f2bc79e463bc4fa..21efb900ef1bc30338f1102f6a40f1d25be0db9c 100644--- a/src/test/regress/expected/transactions.out+++ b/src/test/regress/expected/transactions.out@@ -609,10 +609,10 @@ drop function inverse(int);
-- performed in the aborted subtransaction
begin;
savepoint x;
-create table abc (a int);-insert into abc values (5);-insert into abc values (10);-declare foo cursor for select * from abc;+create table txn_table (a int);+insert into txn_table values (5);+insert into txn_table values (10);+declare foo cursor for select * from txn_table;
fetch from foo;
a
---
@@ -625,11 +625,11 @@ fetch from foo;
ERROR: cursor "foo" does not exist
commit;
begin;
-create table abc (a int);-insert into abc values (5);-insert into abc values (10);-insert into abc values (15);-declare foo cursor for select * from abc;+create table txn_table (a int);+insert into txn_table values (5);+insert into txn_table values (10);+insert into txn_table values (15);+declare foo cursor for select * from txn_table;
fetch from foo;
a
---
@@ -698,7 +698,7 @@ COMMIT;
DROP FUNCTION create_temp_tab();
DROP FUNCTION invert(x float8);
-- Tests for AND CHAIN
-CREATE TABLE abc (a int);+CREATE TABLE txn_table (a int);
-- set nondefault value so we have something to override below
SET default_transaction_read_only = on;
START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE;
@@ -720,8 +720,8 @@ SHOW transaction_deferrable;
on
(1 row)
-INSERT INTO abc VALUES (1);-INSERT INTO abc VALUES (2);+INSERT INTO txn_table VALUES (1);+INSERT INTO txn_table VALUES (2);
COMMIT AND CHAIN; -- TBLOCK_END
SHOW transaction_isolation;
transaction_isolation
@@ -741,11 +741,11 @@ SHOW transaction_deferrable;
on
(1 row)
-INSERT INTO abc VALUES ('error');+INSERT INTO txn_table VALUES ('error');
ERROR: invalid input syntax for type integer: "error"
-LINE 1: INSERT INTO abc VALUES ('error');- ^-INSERT INTO abc VALUES (3); -- check it's really aborted+LINE 1: INSERT INTO txn_table VALUES ('error');+ ^+INSERT INTO txn_table VALUES (3); -- check it's really aborted
ERROR: current transaction is aborted, commands ignored until end of transaction block
COMMIT AND CHAIN; -- TBLOCK_ABORT_END
SHOW transaction_isolation;
@@ -766,7 +766,7 @@ SHOW transaction_deferrable;
on
(1 row)
-INSERT INTO abc VALUES (4);+INSERT INTO txn_table VALUES (4);
COMMIT;
START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE;
SHOW transaction_isolation;
@@ -788,10 +788,10 @@ SHOW transaction_deferrable;
(1 row)
SAVEPOINT x;
-INSERT INTO abc VALUES ('error');+INSERT INTO txn_table VALUES ('error');
ERROR: invalid input syntax for type integer: "error"
-LINE 1: INSERT INTO abc VALUES ('error');- ^+LINE 1: INSERT INTO txn_table VALUES ('error');+ ^
COMMIT AND CHAIN; -- TBLOCK_ABORT_PENDING
SHOW transaction_isolation;
transaction_isolation
@@ -811,7 +811,7 @@ SHOW transaction_deferrable;
on
(1 row)
-INSERT INTO abc VALUES (5);+INSERT INTO txn_table VALUES (5);
COMMIT;
START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE;
SHOW transaction_isolation;
@@ -873,7 +873,7 @@ SHOW transaction_deferrable;
off
(1 row)
-INSERT INTO abc VALUES (6);+INSERT INTO txn_table VALUES (6);
ROLLBACK AND CHAIN; -- TBLOCK_ABORT_PENDING
SHOW transaction_isolation;
transaction_isolation
@@ -893,10 +893,10 @@ SHOW transaction_deferrable;
off
(1 row)
-INSERT INTO abc VALUES ('error');+INSERT INTO txn_table VALUES ('error');
ERROR: invalid input syntax for type integer: "error"
-LINE 1: INSERT INTO abc VALUES ('error');- ^+LINE 1: INSERT INTO txn_table VALUES ('error');+ ^
ROLLBACK AND CHAIN; -- TBLOCK_ABORT_END
SHOW transaction_isolation;
transaction_isolation
@@ -922,7 +922,7 @@ COMMIT AND CHAIN; -- error
ERROR: COMMIT AND CHAIN can only be used in transaction blocks
ROLLBACK AND CHAIN; -- error
ERROR: ROLLBACK AND CHAIN can only be used in transaction blocks
-SELECT * FROM abc ORDER BY 1;+SELECT * FROM txn_table ORDER BY 1;
a
---
1
@@ -932,7 +932,7 @@ SELECT * FROM abc ORDER BY 1;
(4 rows)
RESET default_transaction_read_only;
-DROP TABLE abc;+DROP TABLE txn_table;
-- Test assorted behaviors around the implicit transaction block created
-- when multiple SQL commands are sent in a single Query message. These
-- tests rely on the fact that psql will not break SQL commands apart at a
@@ -1090,21 +1090,21 @@ SHOW transaction_read_only;
off
(1 row)
-CREATE TABLE abc (a int);+CREATE TABLE txn_table (a int);
-- COMMIT/ROLLBACK + COMMIT/ROLLBACK AND CHAIN
-INSERT INTO abc VALUES (7)\; COMMIT\; INSERT INTO abc VALUES (8)\; COMMIT AND CHAIN; -- 7 commit, 8 error+INSERT INTO txn_table VALUES (7)\; COMMIT\; INSERT INTO txn_table VALUES (8)\; COMMIT AND CHAIN; -- 7 commit, 8 error
WARNING: there is no transaction in progress
ERROR: COMMIT AND CHAIN can only be used in transaction blocks
-INSERT INTO abc VALUES (9)\; ROLLBACK\; INSERT INTO abc VALUES (10)\; ROLLBACK AND CHAIN; -- 9 rollback, 10 error+INSERT INTO txn_table VALUES (9)\; ROLLBACK\; INSERT INTO txn_table VALUES (10)\; ROLLBACK AND CHAIN; -- 9 rollback, 10 error
WARNING: there is no transaction in progress
ERROR: ROLLBACK AND CHAIN can only be used in transaction blocks
-- COMMIT/ROLLBACK AND CHAIN + COMMIT/ROLLBACK
-INSERT INTO abc VALUES (11)\; COMMIT AND CHAIN\; INSERT INTO abc VALUES (12)\; COMMIT; -- 11 error, 12 not reached+INSERT INTO txn_table VALUES (11)\; COMMIT AND CHAIN\; INSERT INTO txn_table VALUES (12)\; COMMIT; -- 11 error, 12 not reached
ERROR: COMMIT AND CHAIN can only be used in transaction blocks
-INSERT INTO abc VALUES (13)\; ROLLBACK AND CHAIN\; INSERT INTO abc VALUES (14)\; ROLLBACK; -- 13 error, 14 not reached+INSERT INTO txn_table VALUES (13)\; ROLLBACK AND CHAIN\; INSERT INTO txn_table VALUES (14)\; ROLLBACK; -- 13 error, 14 not reached
ERROR: ROLLBACK AND CHAIN can only be used in transaction blocks
-- START TRANSACTION + COMMIT/ROLLBACK AND CHAIN
-START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO abc VALUES (15)\; COMMIT AND CHAIN; -- 15 ok+START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO txn_table VALUES (15)\; COMMIT AND CHAIN; -- 15 ok
SHOW transaction_isolation; -- transaction is active at this point
transaction_isolation
-----------------------
@@ -1112,7 +1112,7 @@ SHOW transaction_isolation; -- transaction is active at this point
(1 row)
COMMIT;
-START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO abc VALUES (16)\; ROLLBACK AND CHAIN; -- 16 ok+START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO txn_table VALUES (16)\; ROLLBACK AND CHAIN; -- 16 ok
SHOW transaction_isolation; -- transaction is active at this point
transaction_isolation
-----------------------
@@ -1122,7 +1122,7 @@ SHOW transaction_isolation; -- transaction is active at this point
ROLLBACK;
SET default_transaction_isolation = 'read committed';
-- START TRANSACTION + COMMIT/ROLLBACK + COMMIT/ROLLBACK AND CHAIN
-START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO abc VALUES (17)\; COMMIT\; INSERT INTO abc VALUES (18)\; COMMIT AND CHAIN; -- 17 commit, 18 error+START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO txn_table VALUES (17)\; COMMIT\; INSERT INTO txn_table VALUES (18)\; COMMIT AND CHAIN; -- 17 commit, 18 error
ERROR: COMMIT AND CHAIN can only be used in transaction blocks
SHOW transaction_isolation; -- out of transaction block
transaction_isolation
@@ -1130,7 +1130,7 @@ SHOW transaction_isolation; -- out of transaction block
read committed
(1 row)
-START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO abc VALUES (19)\; ROLLBACK\; INSERT INTO abc VALUES (20)\; ROLLBACK AND CHAIN; -- 19 rollback, 20 error+START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO txn_table VALUES (19)\; ROLLBACK\; INSERT INTO txn_table VALUES (20)\; ROLLBACK AND CHAIN; -- 19 rollback, 20 error
ERROR: ROLLBACK AND CHAIN can only be used in transaction blocks
SHOW transaction_isolation; -- out of transaction block
transaction_isolation
@@ -1139,7 +1139,7 @@ SHOW transaction_isolation; -- out of transaction block
(1 row)
RESET default_transaction_isolation;
-SELECT * FROM abc ORDER BY 1;+SELECT * FROM txn_table ORDER BY 1;
a
----
7
@@ -1147,7 +1147,7 @@ SELECT * FROM abc ORDER BY 1;
17
(3 rows)
-DROP TABLE abc;+DROP TABLE txn_table;
-- Test for successful cleanup of an aborted transaction at session exit.
-- THIS MUST BE THE LAST TEST IN THIS FILE.
begin;
diff --git a/src/test/regress/sql/transactions.sql b/src/test/regress/sql/transactions.sqlindex 7ee5f6aaa55a686f5484664c3fae224c08bde42a..fd1b645403868da18f9f79b4ba8a03a3f7d1da6b 100644--- a/src/test/regress/sql/transactions.sql+++ b/src/test/regress/sql/transactions.sql@@ -378,10 +378,10 @@ drop function inverse(int);
begin;
savepoint x;
-create table abc (a int);-insert into abc values (5);-insert into abc values (10);-declare foo cursor for select * from abc;+create table txn_table (a int);+insert into txn_table values (5);+insert into txn_table values (10);+declare foo cursor for select * from txn_table;
fetch from foo;
rollback to x;
@@ -391,11 +391,11 @@ commit;
begin;
-create table abc (a int);-insert into abc values (5);-insert into abc values (10);-insert into abc values (15);-declare foo cursor for select * from abc;+create table txn_table (a int);+insert into txn_table values (5);+insert into txn_table values (10);+insert into txn_table values (15);+declare foo cursor for select * from txn_table;
fetch from foo;
@@ -441,7 +441,7 @@ DROP FUNCTION invert(x float8);
-- Tests for AND CHAIN
-CREATE TABLE abc (a int);+CREATE TABLE txn_table (a int);
-- set nondefault value so we have something to override below
SET default_transaction_read_only = on;
@@ -450,19 +450,19 @@ START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE;
SHOW transaction_isolation;
SHOW transaction_read_only;
SHOW transaction_deferrable;
-INSERT INTO abc VALUES (1);-INSERT INTO abc VALUES (2);+INSERT INTO txn_table VALUES (1);+INSERT INTO txn_table VALUES (2);
COMMIT AND CHAIN; -- TBLOCK_END
SHOW transaction_isolation;
SHOW transaction_read_only;
SHOW transaction_deferrable;
-INSERT INTO abc VALUES ('error');-INSERT INTO abc VALUES (3); -- check it's really aborted+INSERT INTO txn_table VALUES ('error');+INSERT INTO txn_table VALUES (3); -- check it's really aborted
COMMIT AND CHAIN; -- TBLOCK_ABORT_END
SHOW transaction_isolation;
SHOW transaction_read_only;
SHOW transaction_deferrable;
-INSERT INTO abc VALUES (4);+INSERT INTO txn_table VALUES (4);
COMMIT;
START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE;
@@ -470,12 +470,12 @@ SHOW transaction_isolation;
SHOW transaction_read_only;
SHOW transaction_deferrable;
SAVEPOINT x;
-INSERT INTO abc VALUES ('error');+INSERT INTO txn_table VALUES ('error');
COMMIT AND CHAIN; -- TBLOCK_ABORT_PENDING
SHOW transaction_isolation;
SHOW transaction_read_only;
SHOW transaction_deferrable;
-INSERT INTO abc VALUES (5);+INSERT INTO txn_table VALUES (5);
COMMIT;
START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE;
@@ -494,12 +494,12 @@ START TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE, NOT DEFERRABLE;
SHOW transaction_isolation;
SHOW transaction_read_only;
SHOW transaction_deferrable;
-INSERT INTO abc VALUES (6);+INSERT INTO txn_table VALUES (6);
ROLLBACK AND CHAIN; -- TBLOCK_ABORT_PENDING
SHOW transaction_isolation;
SHOW transaction_read_only;
SHOW transaction_deferrable;
-INSERT INTO abc VALUES ('error');+INSERT INTO txn_table VALUES ('error');
ROLLBACK AND CHAIN; -- TBLOCK_ABORT_END
SHOW transaction_isolation;
SHOW transaction_read_only;
@@ -510,11 +510,11 @@ ROLLBACK;
COMMIT AND CHAIN; -- error
ROLLBACK AND CHAIN; -- error
-SELECT * FROM abc ORDER BY 1;+SELECT * FROM txn_table ORDER BY 1;
RESET default_transaction_read_only;
-DROP TABLE abc;+DROP TABLE txn_table;
-- Test assorted behaviors around the implicit transaction block created
@@ -579,39 +579,39 @@ SHOW transaction_read_only;
SET TRANSACTION READ ONLY\; ROLLBACK AND CHAIN; -- error
SHOW transaction_read_only;
-CREATE TABLE abc (a int);+CREATE TABLE txn_table (a int);
-- COMMIT/ROLLBACK + COMMIT/ROLLBACK AND CHAIN
-INSERT INTO abc VALUES (7)\; COMMIT\; INSERT INTO abc VALUES (8)\; COMMIT AND CHAIN; -- 7 commit, 8 error-INSERT INTO abc VALUES (9)\; ROLLBACK\; INSERT INTO abc VALUES (10)\; ROLLBACK AND CHAIN; -- 9 rollback, 10 error+INSERT INTO txn_table VALUES (7)\; COMMIT\; INSERT INTO txn_table VALUES (8)\; COMMIT AND CHAIN; -- 7 commit, 8 error+INSERT INTO txn_table VALUES (9)\; ROLLBACK\; INSERT INTO txn_table VALUES (10)\; ROLLBACK AND CHAIN; -- 9 rollback, 10 error
-- COMMIT/ROLLBACK AND CHAIN + COMMIT/ROLLBACK
-INSERT INTO abc VALUES (11)\; COMMIT AND CHAIN\; INSERT INTO abc VALUES (12)\; COMMIT; -- 11 error, 12 not reached-INSERT INTO abc VALUES (13)\; ROLLBACK AND CHAIN\; INSERT INTO abc VALUES (14)\; ROLLBACK; -- 13 error, 14 not reached+INSERT INTO txn_table VALUES (11)\; COMMIT AND CHAIN\; INSERT INTO txn_table VALUES (12)\; COMMIT; -- 11 error, 12 not reached+INSERT INTO txn_table VALUES (13)\; ROLLBACK AND CHAIN\; INSERT INTO txn_table VALUES (14)\; ROLLBACK; -- 13 error, 14 not reached
-- START TRANSACTION + COMMIT/ROLLBACK AND CHAIN
-START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO abc VALUES (15)\; COMMIT AND CHAIN; -- 15 ok+START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO txn_table VALUES (15)\; COMMIT AND CHAIN; -- 15 ok
SHOW transaction_isolation; -- transaction is active at this point
COMMIT;
-START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO abc VALUES (16)\; ROLLBACK AND CHAIN; -- 16 ok+START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO txn_table VALUES (16)\; ROLLBACK AND CHAIN; -- 16 ok
SHOW transaction_isolation; -- transaction is active at this point
ROLLBACK;
SET default_transaction_isolation = 'read committed';
-- START TRANSACTION + COMMIT/ROLLBACK + COMMIT/ROLLBACK AND CHAIN
-START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO abc VALUES (17)\; COMMIT\; INSERT INTO abc VALUES (18)\; COMMIT AND CHAIN; -- 17 commit, 18 error+START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO txn_table VALUES (17)\; COMMIT\; INSERT INTO txn_table VALUES (18)\; COMMIT AND CHAIN; -- 17 commit, 18 error
SHOW transaction_isolation; -- out of transaction block
-START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO abc VALUES (19)\; ROLLBACK\; INSERT INTO abc VALUES (20)\; ROLLBACK AND CHAIN; -- 19 rollback, 20 error+START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO txn_table VALUES (19)\; ROLLBACK\; INSERT INTO txn_table VALUES (20)\; ROLLBACK AND CHAIN; -- 19 rollback, 20 error
SHOW transaction_isolation; -- out of transaction block
RESET default_transaction_isolation;
-SELECT * FROM abc ORDER BY 1;+SELECT * FROM txn_table ORDER BY 1;-DROP TABLE abc;+DROP TABLE txn_table;
-- Test for successful cleanup of an aborted transaction at session exit.
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: m.polyakova@postgrespro.ru, michael@paquier.xyz, tgl@sss.pgh.pa.us, noah@leadboat.com, exclusion@gmail.com
Subject: Re: Conflict between regression tests namespace & transactions due to recent changes
In-Reply-To: <ac5bfbf1012b59f56af5ad6715adc0d3@postgrespro.ru>
* 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