v1-0001-refactor-temporal-FOREIGN-KEYs-test.based_on_v24
application/octet-stream
Filename: v1-0001-refactor-temporal-FOREIGN-KEYs-test.based_on_v24
Type: application/octet-stream
Part: 0
Message:
Re: SQL:2011 application time
From 951a1a0ca721b0c07df7f6610c4498b1439103e7 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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