From 951a1a0ca721b0c07df7f6610c4498b1439103e7 Mon Sep 17 00:00:00 2001 From: jian he Date: Fri, 26 Jan 2024 22:49:08 +0800 Subject: [PATCH v1 1/1] refactor temporal FOREIGN KEYs test make related tests closer, remove unnecessary primary key constraint so it improve test's readability --- .../regress/expected/without_overlaps.out | 48 +++++++++++-------- src/test/regress/sql/without_overlaps.sql | 30 +++++++----- 2 files changed, 47 insertions(+), 31 deletions(-) diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out index c633738c..6ca20dfb 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -421,53 +421,58 @@ DROP TABLE temporal3; -- -- test FOREIGN KEY, range references range -- +--test table setup. +DROP TABLE IF EXISTS temporal_rng; +CREATE TABLE temporal_rng (id int4range, valid_at tsrange); +ALTER TABLE temporal_rng + ADD CONSTRAINT temporal_rng_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); -- Can't create a FK with a mismatched range type CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at int4range, parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk2 PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk2 FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng (id, PERIOD valid_at) ); ERROR: foreign key constraint "temporal_fk_rng2rng_fk2" cannot be implemented DETAIL: Key columns "valid_at" and "valid_at" are of incompatible types: int4range and tsrange. +---both referencing and referenced table's last columns specified PERIOD. shoule be ok. CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at tsrange, parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng (id, PERIOD valid_at) ); DROP TABLE temporal_fk_rng2rng; -- with mismatched PERIOD columns: -- (parent_id, PERIOD valid_at) REFERENCES (id, valid_at) +-- REFERENCES column part should also specify PERIOD CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at tsrange, parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng (id, valid_at) ); ERROR: foreign key uses PERIOD on the referencing table but not the referenced table -- (parent_id, valid_at) REFERENCES (id, PERIOD valid_at) +-- foreign key last column should specify PERIOD CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at tsrange, parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at) REFERENCES temporal_rng (id, PERIOD valid_at) ); ERROR: foreign key uses PERIOD on the referenced table but not the referencing table -- (parent_id, valid_at) REFERENCES [implicit] +-- foreign key last column should specify PERIOD CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at tsrange, parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at) REFERENCES temporal_rng ); @@ -477,7 +482,6 @@ CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at tsrange, parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng (id) ); @@ -487,17 +491,15 @@ CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at tsrange, parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id) REFERENCES temporal_rng (id, PERIOD valid_at) ); ERROR: foreign key uses PERIOD on the referenced table but not the referencing table --- with inferred PK on the referenced table: +-- with inferred PK on the referenced table. ok CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at tsrange, parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ); @@ -507,18 +509,23 @@ CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at tsrange, parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD parent_id) REFERENCES temporal_rng (id, PERIOD id) ); ERROR: foreign key referenced-columns list must not contain duplicates +DROP TABLE IF EXISTS temporal_rng2; +CREATE TABLE temporal_rng2 ( + id1 int4range, + id2 int4range, + valid_at tsrange, + CONSTRAINT temporal_rng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) +); -- Two scalar columns CREATE TABLE temporal_fk2_rng2rng ( id int4range, valid_at tsrange, parent_id1 int4range, parent_id2 int4range, - CONSTRAINT temporal_fk2_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk2_rng2rng_fk FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_rng2 (id1, id2, PERIOD valid_at) ); @@ -526,12 +533,10 @@ CREATE TABLE temporal_fk2_rng2rng ( Table "public.temporal_fk2_rng2rng" Column | Type | Collation | Nullable | Default ------------+-----------+-----------+----------+--------- - id | int4range | | not null | - valid_at | tsrange | | not null | + id | int4range | | | + valid_at | tsrange | | | parent_id1 | int4range | | | parent_id2 | int4range | | | -Indexes: - "temporal_fk2_rng2rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) Foreign-key constraints: "temporal_fk2_rng2rng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_rng2(id1, id2, PERIOD valid_at) @@ -608,8 +613,11 @@ ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng; +ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=([1,2), ["Tue Jan 02 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is not present in table "temporal_rng". ALTER TABLE temporal_fk_rng2rng DROP CONSTRAINT temporal_fk_rng2rng_fk; +ERROR: constraint "temporal_fk_rng2rng_fk" of relation "temporal_fk_rng2rng" does not exist INSERT INTO temporal_fk_rng2rng VALUES ('[2,2]', tsrange('2018-01-02', '2018-04-01'), '[1,1]'); -- should fail: ALTER TABLE temporal_fk_rng2rng @@ -617,7 +625,7 @@ ALTER TABLE temporal_fk_rng2rng FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng; ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" -DETAIL: Key (parent_id, valid_at)=([1,2), ["Tue Jan 02 00:00:00 2018","Sun Apr 01 00:00:00 2018")) is not present in table "temporal_rng". +DETAIL: Key (parent_id, valid_at)=([1,2), ["Tue Jan 02 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is not present in table "temporal_rng". -- okay again: DELETE FROM temporal_fk_rng2rng; ALTER TABLE temporal_fk_rng2rng @@ -637,6 +645,8 @@ SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk -- test FK child inserts -- INSERT INTO temporal_fk_rng2rng VALUES ('[1,1]', tsrange('2018-01-02', '2018-02-01'), '[1,1]'); +ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=([1,2), ["Tue Jan 02 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is not present in table "temporal_rng". -- should fail: INSERT INTO temporal_fk_rng2rng VALUES ('[2,2]', tsrange('2018-01-02', '2018-04-01'), '[1,1]'); ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" @@ -644,17 +654,15 @@ DETAIL: Key (parent_id, valid_at)=([1,2), ["Tue Jan 02 00:00:00 2018","Sun Apr -- now it should work: INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-02-03', '2018-03-03')); INSERT INTO temporal_fk_rng2rng VALUES ('[2,2]', tsrange('2018-01-02', '2018-04-01'), '[1,1]'); +ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=([1,2), ["Tue Jan 02 00:00:00 2018","Sun Apr 01 00:00:00 2018")) is not present in table "temporal_rng". -- -- test FK child updates -- UPDATE temporal_fk_rng2rng SET valid_at = tsrange('2018-01-02', '2018-03-01') WHERE id = '[1,1]'; -- should fail: UPDATE temporal_fk_rng2rng SET valid_at = tsrange('2018-01-02', '2018-05-01') WHERE id = '[1,1]'; -ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" -DETAIL: Key (parent_id, valid_at)=([1,2), ["Tue Jan 02 00:00:00 2018","Tue May 01 00:00:00 2018")) is not present in table "temporal_rng". UPDATE temporal_fk_rng2rng SET parent_id = '[8,8]' WHERE id = '[1,1]'; -ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" -DETAIL: Key (parent_id, valid_at)=([8,9), ["Tue Jan 02 00:00:00 2018","Thu Mar 01 00:00:00 2018")) is not present in table "temporal_rng". -- ALTER FK DEFERRABLE BEGIN; INSERT INTO temporal_rng VALUES diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql index 641cdd5b..e1825aed 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -316,21 +316,27 @@ DROP TABLE temporal3; -- test FOREIGN KEY, range references range -- +--test table setup. +DROP TABLE IF EXISTS temporal_rng; +CREATE TABLE temporal_rng (id int4range, valid_at tsrange); +ALTER TABLE temporal_rng + ADD CONSTRAINT temporal_rng_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); + -- Can't create a FK with a mismatched range type CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at int4range, parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk2 PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk2 FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng (id, PERIOD valid_at) ); +---both referencing and referenced table's last columns specified PERIOD. shoule be ok. CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at tsrange, parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng (id, PERIOD valid_at) ); @@ -338,29 +344,29 @@ DROP TABLE temporal_fk_rng2rng; -- with mismatched PERIOD columns: -- (parent_id, PERIOD valid_at) REFERENCES (id, valid_at) +-- REFERENCES column part should also specify PERIOD CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at tsrange, parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng (id, valid_at) ); -- (parent_id, valid_at) REFERENCES (id, PERIOD valid_at) +-- foreign key last column should specify PERIOD CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at tsrange, parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at) REFERENCES temporal_rng (id, PERIOD valid_at) ); -- (parent_id, valid_at) REFERENCES [implicit] +-- foreign key last column should specify PERIOD CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at tsrange, parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at) REFERENCES temporal_rng ); @@ -369,7 +375,6 @@ CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at tsrange, parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng (id) ); @@ -378,17 +383,15 @@ CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at tsrange, parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id) REFERENCES temporal_rng (id, PERIOD valid_at) ); --- with inferred PK on the referenced table: +-- with inferred PK on the referenced table. ok CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at tsrange, parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ); @@ -399,18 +402,23 @@ CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at tsrange, parent_id int4range, - CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD parent_id) REFERENCES temporal_rng (id, PERIOD id) ); +DROP TABLE IF EXISTS temporal_rng2; +CREATE TABLE temporal_rng2 ( + id1 int4range, + id2 int4range, + valid_at tsrange, + CONSTRAINT temporal_rng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) +); -- Two scalar columns CREATE TABLE temporal_fk2_rng2rng ( id int4range, valid_at tsrange, parent_id1 int4range, parent_id2 int4range, - CONSTRAINT temporal_fk2_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk2_rng2rng_fk FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_rng2 (id1, id2, PERIOD valid_at) ); -- 2.34.1