From 5ded5aea710024b107f1b47648ef8034959aae1e Mon Sep 17 00:00:00 2001 From: jian he Date: Fri, 16 Aug 2024 09:35:51 +0800 Subject: [PATCH v39 1/1] refactor tests. --- .../regress/expected/without_overlaps.out | 107 ++++-------------- src/test/regress/sql/without_overlaps.sql | 66 ++--------- 2 files changed, 32 insertions(+), 141 deletions(-) diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out index 0fe3949f74..6d745ad269 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -402,15 +402,6 @@ BEGIN; ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_rng" ROLLBACK; ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); -DELETE FROM temporal_rng; --- --- range PK: test inserts --- --- okay: -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03')); -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04')); -INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2018-01-01', '2018-01-05')); -INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', daterange('2018-01-01', NULL)); -- should fail: INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', '2018-01-05')); ERROR: conflicting key value violates exclusion constraint "temporal_rng_pk" @@ -497,6 +488,10 @@ SELECT * FROM temporal_rng ORDER BY id, valid_at; [21,22) | [2018-01-02,2018-02-03) (4 rows) +-- ALTER TABLE REPLICA IDENTITY +-- (should fail) +ALTER TABLE temporal_rng REPLICA IDENTITY USING INDEX temporal_rng_pk; +ERROR: cannot use non-unique index "temporal_rng_pk" as replica identity -- -- range UQ: test with existing rows -- @@ -527,17 +522,6 @@ BEGIN; ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_rng3" ROLLBACK; ALTER TABLE temporal_rng3 ADD CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS); -DELETE FROM temporal_rng3; --- --- range UQ: test inserts --- --- okay: -INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03')); -INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04')); -INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[2,3)', daterange('2018-01-01', '2018-01-05')); -INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', daterange('2018-01-01', NULL)); -INSERT INTO temporal_rng3 (id, valid_at) VALUES (NULL, daterange('2018-01-01', '2018-01-05')); -INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', NULL); -- should fail: INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', '2018-01-05')); ERROR: conflicting key value violates exclusion constraint "temporal_rng3_uq" @@ -650,15 +634,6 @@ BEGIN; ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_mltrng" ROLLBACK; ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); -DELETE FROM temporal_mltrng; --- --- multirange PK: test inserts --- --- okay: -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03'))); -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04'))); -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2018-01-01', '2018-01-05'))); -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', datemultirange(daterange('2018-01-01', NULL))); -- should fail: INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-01-05'))); ERROR: conflicting key value violates exclusion constraint "temporal_mltrng_pk" @@ -775,17 +750,6 @@ BEGIN; ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_mltrng3" ROLLBACK; ALTER TABLE temporal_mltrng3 ADD CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS); -DELETE FROM temporal_mltrng3; --- --- multirange UQ: test inserts --- --- okay: -INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03'))); -INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04'))); -INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2018-01-01', '2018-01-05'))); -INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', datemultirange(daterange('2018-01-01', NULL))); -INSERT INTO temporal_mltrng3 (id, valid_at) VALUES (NULL, datemultirange(daterange('2018-01-01', '2018-01-05'))); -INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', NULL); -- should fail: INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-01-05'))); ERROR: conflicting key value violates exclusion constraint "temporal_mltrng3_uq" @@ -920,27 +884,14 @@ INSERT INTO temporal_partitioned (id, valid_at, name) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'); -SELECT * FROM temporal_partitioned ORDER BY id, valid_at; - id | valid_at | name --------+-------------------------+------- - [1,2) | [2000-01-01,2000-02-01) | one - [1,2) | [2000-02-01,2000-03-01) | one - [3,4) | [2000-01-01,2010-01-01) | three +SELECT tableoid::regclass as partition_tbl, * FROM temporal_partitioned ORDER BY id, valid_at; + partition_tbl | id | valid_at | name +---------------+-------+-------------------------+------- + tp1 | [1,2) | [2000-01-01,2000-02-01) | one + tp1 | [1,2) | [2000-02-01,2000-03-01) | one + tp2 | [3,4) | [2000-01-01,2010-01-01) | three (3 rows) -SELECT * FROM tp1 ORDER BY id, valid_at; - id | valid_at | name --------+-------------------------+------ - [1,2) | [2000-01-01,2000-02-01) | one - [1,2) | [2000-02-01,2000-03-01) | one -(2 rows) - -SELECT * FROM tp2 ORDER BY id, valid_at; - id | valid_at | name --------+-------------------------+------- - [3,4) | [2000-01-01,2010-01-01) | three -(1 row) - DROP TABLE temporal_partitioned; -- temporal UNIQUE: CREATE TABLE temporal_partitioned ( @@ -955,35 +906,27 @@ INSERT INTO temporal_partitioned (id, valid_at, name) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'); -SELECT * FROM temporal_partitioned ORDER BY id, valid_at; - id | valid_at | name --------+-------------------------+------- - [1,2) | [2000-01-01,2000-02-01) | one - [1,2) | [2000-02-01,2000-03-01) | one - [3,4) | [2000-01-01,2010-01-01) | three +SELECT tableoid::regclass as partition_tbl, * FROM temporal_partitioned ORDER BY id, valid_at; + partition_tbl | id | valid_at | name +---------------+-------+-------------------------+------- + tp1 | [1,2) | [2000-01-01,2000-02-01) | one + tp1 | [1,2) | [2000-02-01,2000-03-01) | one + tp2 | [3,4) | [2000-01-01,2010-01-01) | three (3 rows) -SELECT * FROM tp1 ORDER BY id, valid_at; - id | valid_at | name --------+-------------------------+------ - [1,2) | [2000-01-01,2000-02-01) | one - [1,2) | [2000-02-01,2000-03-01) | one -(2 rows) - -SELECT * FROM tp2 ORDER BY id, valid_at; - id | valid_at | name --------+-------------------------+------- - [3,4) | [2000-01-01,2010-01-01) | three -(1 row) - DROP TABLE temporal_partitioned; --- ALTER TABLE REPLICA IDENTITY --- (should fail) -ALTER TABLE temporal_rng REPLICA IDENTITY USING INDEX temporal_rng_pk; -ERROR: cannot use non-unique index "temporal_rng_pk" as replica identity -- -- ON CONFLICT: ranges -- +\d temporal_rng + Table "public.temporal_rng" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | int4range | | not null | + valid_at | daterange | | not null | +Indexes: + "temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) + TRUNCATE temporal_rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); -- with a conflict diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql index e05fa1d00c..ff5932845e 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -270,17 +270,6 @@ BEGIN; ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); ROLLBACK; ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); -DELETE FROM temporal_rng; - --- --- range PK: test inserts --- - --- okay: -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03')); -INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04')); -INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2018-01-01', '2018-01-05')); -INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', daterange('2018-01-01', NULL)); -- should fail: INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', '2018-01-05')); @@ -333,6 +322,10 @@ SET id = '[1,2)', WHERE id = '[21,22)'; SELECT * FROM temporal_rng ORDER BY id, valid_at; +-- ALTER TABLE REPLICA IDENTITY +-- (should fail) +ALTER TABLE temporal_rng REPLICA IDENTITY USING INDEX temporal_rng_pk; + -- -- range UQ: test with existing rows -- @@ -363,19 +356,6 @@ BEGIN; ALTER TABLE temporal_rng3 ADD CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS); ROLLBACK; ALTER TABLE temporal_rng3 ADD CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS); -DELETE FROM temporal_rng3; - --- --- range UQ: test inserts --- - --- okay: -INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03')); -INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04')); -INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[2,3)', daterange('2018-01-01', '2018-01-05')); -INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', daterange('2018-01-01', NULL)); -INSERT INTO temporal_rng3 (id, valid_at) VALUES (NULL, daterange('2018-01-01', '2018-01-05')); -INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', NULL); -- should fail: INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', '2018-01-05')); @@ -455,17 +435,6 @@ BEGIN; ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); ROLLBACK; ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); -DELETE FROM temporal_mltrng; - --- --- multirange PK: test inserts --- - --- okay: -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03'))); -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04'))); -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2018-01-01', '2018-01-05'))); -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', datemultirange(daterange('2018-01-01', NULL))); -- should fail: INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-01-05'))); @@ -548,19 +517,6 @@ BEGIN; ALTER TABLE temporal_mltrng3 ADD CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS); ROLLBACK; ALTER TABLE temporal_mltrng3 ADD CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS); -DELETE FROM temporal_mltrng3; - --- --- multirange UQ: test inserts --- - --- okay: -INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03'))); -INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04'))); -INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2018-01-01', '2018-01-05'))); -INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', datemultirange(daterange('2018-01-01', NULL))); -INSERT INTO temporal_mltrng3 (id, valid_at) VALUES (NULL, datemultirange(daterange('2018-01-01', '2018-01-05'))); -INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', NULL); -- should fail: INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-01-05'))); @@ -667,9 +623,7 @@ INSERT INTO temporal_partitioned (id, valid_at, name) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'); -SELECT * FROM temporal_partitioned ORDER BY id, valid_at; -SELECT * FROM tp1 ORDER BY id, valid_at; -SELECT * FROM tp2 ORDER BY id, valid_at; +SELECT tableoid::regclass as partition_tbl, * FROM temporal_partitioned ORDER BY id, valid_at; DROP TABLE temporal_partitioned; -- temporal UNIQUE: @@ -685,19 +639,13 @@ INSERT INTO temporal_partitioned (id, valid_at, name) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'); -SELECT * FROM temporal_partitioned ORDER BY id, valid_at; -SELECT * FROM tp1 ORDER BY id, valid_at; -SELECT * FROM tp2 ORDER BY id, valid_at; +SELECT tableoid::regclass as partition_tbl, * FROM temporal_partitioned ORDER BY id, valid_at; DROP TABLE temporal_partitioned; --- ALTER TABLE REPLICA IDENTITY --- (should fail) -ALTER TABLE temporal_rng REPLICA IDENTITY USING INDEX temporal_rng_pk; - -- -- ON CONFLICT: ranges -- - +\d temporal_rng TRUNCATE temporal_rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01')); -- with a conflict -- 2.34.1