v39-0001-refactor-tests.no-cfbot
application/octet-stream
Filename: v39-0001-refactor-tests.no-cfbot
Type: application/octet-stream
Part: 0
Message:
Re: SQL:2011 application time
From 5ded5aea710024b107f1b47648ef8034959aae1e Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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