On Mon, Jan 29, 2024 at 8:00 AM jian he <jian.universal...@gmail.com> wrote:
>
> I fixed your tests, some of your tests can be simplified, (mainly
> primary key constraint is unnecessary for the failed tests)
> also your foreign key patch test table, temporal_rng is created at
> line 141, and we use it at around line 320.
> it's hard to get the definition of temporal_rng. I drop the table
> and recreate it.
> So people can view the patch with tests more easily.
>
I've attached a new patch that further simplified the tests. (scope
v24 patch's 0002 and 0003)
Please ignore previous email attachments.
I've only applied the v24, 0002, 0003.
seems in doc/src/sgml/ref/create_table.sgml
lack the explanation of `<replaceable
class="parameter">temporal_interval</replaceable>`
since foreign key ON {UPDATE | DELETE} {CASCADE,SET NULL,SET DEFAULT}
not yet supported,
v24-0003 create_table.sgml should reflect that.
+ /*
+ * For FKs with PERIOD we need an operator and aggregate function
+ * to check whether the referencing row's range is contained
+ * by the aggregated ranges of the referenced row(s).
+ * For rangetypes this is fk.periodatt <@ range_agg(pk.periodatt).
+ * FKs will look these up at "runtime", but we should make sure
+ * the lookup works here.
+ */
+ if (is_temporal)
+ FindFKPeriodOpersAndProcs(opclasses[numpks - 1], &periodoperoid,
&periodprocoid);
within the function ATAddForeignKeyConstraint, you called
FindFKPeriodOpersAndProcs,
but never used the computed outputs: periodoperoid, periodprocoid,
opclasses.
We validate these(periodoperoid, periodprocoid) at
lookupTRIOperAndProc, FindFKPeriodOpersAndProcs.
I'm not sure whether FindFKPeriodOpersAndProcs in
ATAddForeignKeyConstraint is necessary.
+ * Check if all key values in OLD and NEW are "equivalent":
+ * For normal FKs we check for equality.
+ * For temporal FKs we check that the PK side is a superset of its old
value,
+ * or the FK side is a subset.
"or the FK side is a subset." is misleading, should it be something
like "or the FK side is a subset of X"?
+ if (indexStruct->indisexclusion) return i - 1;
+ else return i;
I believe our style should be (with proper indent)
if (indexStruct->indisexclusion)
return i - 1;
else
return i;
in transformFkeyCheckAttrs
+ if (found && is_temporal)
+ {
+ found = false;
+ for (j = 0; j < numattrs + 1; j++)
+ {
+ if (periodattnum == indexStruct->indkey.values[j])
+ {
+ opclasses[numattrs] = indclass->values[j];
+ found = true;
+ break;
+ }
+ }
+ }
can be simplified:
{
found = false;
if (periodattnum == indexStruct->indkey.values[numattrs])
{
opclasses[numattrs] = indclass->values[numattrs];
found = true;
}
}
Also wondering, at the end of the function transformFkeyCheckAttrs `if
(!found)` part:
do we need another error message handle is_temporal is true?
@@ -212,8 +213,11 @@ typedef struct NewConstraint
ConstrType contype; /* CHECK or FOREIGN */
Oid refrelid; /* PK rel, if FOREIGN */
Oid refindid; /* OID of PK's index, if FOREIGN */
+ bool conwithperiod; /* Whether the new FOREIGN KEY uses PERIOD */
Oid conid; /* OID of pg_constraint entry, if FOREIGN */
Node *qual; /* Check expr or CONSTR_FOREIGN Constraint */
+ Oid *operoids; /* oper oids for FOREIGN KEY with PERIOD */
+ Oid *procoids; /* proc oids for FOREIGN KEY with PERIOD */
ExprState *qualstate; /* Execution state for CHECK expr */
} NewConstraint;
primary key can only one WITHOUT OVERLAPS,
so *operoids and *procoids
can be replaced with just
`operoids, procoids`.
Also these two elements in struct NewConstraint not used in v24, 0002, 0003.
From 8ba03aa6a442d57bd0f2117e32e703fb211b68fd Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Fri, 2 Feb 2024 10:31:16 +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 | 76 ++++++++++---------
src/test/regress/sql/without_overlaps.sql | 76 ++++++++++---------
2 files changed, 82 insertions(+), 70 deletions(-)
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index c633738c..2d0f5e21 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)
@@ -601,6 +606,13 @@ ERROR: foreign key referenced-columns list must not contain duplicates
-- test with rows already
--
DELETE FROM temporal_fk_rng2rng;
+DELETE FROM temporal_rng;
+-- okay:
+INSERT INTO temporal_rng VALUES
+('[1,1]', tsrange('2018-01-02', '2018-02-03')),
+('[1,1]', tsrange('2018-03-03', '2018-04-04')),
+('[2,2]', tsrange('2018-01-01', '2018-01-05')),
+('[3,3]', tsrange('2018-01-01', NULL));
ALTER TABLE temporal_fk_rng2rng
DROP CONSTRAINT temporal_fk_rng2rng_fk;
INSERT INTO temporal_fk_rng2rng VALUES ('[1,1]', tsrange('2018-01-02', '2018-02-01'), '[1,1]');
@@ -677,8 +689,9 @@ INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') WHERE id = '[5,5]';
-- a PK update that succeeds even though the numeric id is referenced because the range isn't:
DELETE FROM temporal_rng WHERE id = '[5,5]';
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
+INSERT INTO temporal_rng VALUES
+ ('[5,5]', tsrange('2018-01-01', '2018-02-01')),
+ ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
INSERT INTO temporal_fk_rng2rng VALUES ('[3,3]', tsrange('2018-01-05', '2018-01-10'), '[5,5]');
UPDATE temporal_rng SET valid_at = tsrange('2016-02-01', '2016-03-01')
WHERE id = '[5,5]' AND valid_at = tsrange('2018-02-01', '2018-03-01');
@@ -708,14 +721,15 @@ ALTER TABLE temporal_fk_rng2rng
ADD CONSTRAINT temporal_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_rng
- ON DELETE RESTRICT;
+ ON UPDATE RESTRICT;
-- a PK update that succeeds because the numeric id isn't referenced:
INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') WHERE id = '[5,5]';
-- a PK update that succeeds even though the numeric id is referenced because the range isn't:
DELETE FROM temporal_rng WHERE id = '[5,5]';
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
+INSERT INTO temporal_rng VALUES
+ ('[5,5]', tsrange('2018-01-01', '2018-02-01')),
+ ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
INSERT INTO temporal_fk_rng2rng VALUES ('[3,3]', tsrange('2018-01-05', '2018-01-10'), '[5,5]');
UPDATE temporal_rng SET valid_at = tsrange('2016-02-01', '2016-03-01')
WHERE id = '[5,5]' AND valid_at = tsrange('2018-02-01', '2018-03-01');
@@ -749,8 +763,9 @@ ALTER TABLE temporal_fk_rng2rng
INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
DELETE FROM temporal_rng WHERE id = '[5,5]';
-- a PK delete that succeeds even though the numeric id is referenced because the range isn't:
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
+INSERT INTO temporal_rng VALUES
+ ('[5,5]', tsrange('2018-01-01', '2018-02-01')),
+ ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
INSERT INTO temporal_fk_rng2rng VALUES ('[3,3]', tsrange('2018-01-05', '2018-01-10'), '[5,5]');
DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-02-01', '2018-03-01');
-- a PK delete that fails because both are referenced:
@@ -773,8 +788,9 @@ ALTER TABLE temporal_fk_rng2rng
INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
DELETE FROM temporal_rng WHERE id = '[5,5]';
-- a PK delete that succeeds even though the numeric id is referenced because the range isn't:
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
+INSERT INTO temporal_rng VALUES
+ ('[5,5]', tsrange('2018-01-01', '2018-02-01')),
+ ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
INSERT INTO temporal_fk_rng2rng VALUES ('[3,3]', tsrange('2018-01-05', '2018-01-10'), '[5,5]');
DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-02-01', '2018-03-01');
-- a PK delete that fails because both are referenced:
@@ -785,11 +801,9 @@ DETAIL: Key (id, valid_at)=([5,6), ["Mon Jan 01 00:00:00 2018","Thu Feb 01 00:0
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,3]';
DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01');
--
--- test ON UPDATE/DELETE options
+-- test ON UPDATE/DELETE {set null | set default | CASCADE}
--
-- test FK parent updates CASCADE
-INSERT INTO temporal_rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng VALUES ('[4,4]', tsrange('2018-01-01', '2021-01-01'), '[6,6]');
ALTER TABLE temporal_fk_rng2rng
DROP CONSTRAINT temporal_fk_rng2rng_fk,
ADD CONSTRAINT temporal_fk_rng2rng_fk
@@ -798,8 +812,6 @@ ALTER TABLE temporal_fk_rng2rng
ON DELETE CASCADE ON UPDATE CASCADE;
ERROR: action not supported for temporal foreign keys
-- test FK parent updates SET NULL
-INSERT INTO temporal_rng VALUES ('[9,9]', tsrange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01'), '[9,9]');
ALTER TABLE temporal_fk_rng2rng
DROP CONSTRAINT temporal_fk_rng2rng_fk,
ADD CONSTRAINT temporal_fk_rng2rng_fk
@@ -808,9 +820,6 @@ ALTER TABLE temporal_fk_rng2rng
ON DELETE SET NULL ON UPDATE SET NULL;
ERROR: action not supported for temporal foreign keys
-- test FK parent updates SET DEFAULT
-INSERT INTO temporal_rng VALUES ('[-1,-1]', tsrange(null, null));
-INSERT INTO temporal_rng VALUES ('[12,12]', tsrange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng VALUES ('[8,8]', tsrange('2018-01-01', '2021-01-01'), '[12,12]');
ALTER TABLE temporal_fk_rng2rng
ALTER COLUMN parent_id SET DEFAULT '[-1,-1]',
DROP CONSTRAINT temporal_fk_rng2rng_fk,
@@ -932,7 +941,6 @@ ALTER TABLE temporal_partitioned_fk_rng2rng
REFERENCES temporal_partitioned_rng
ON DELETE CASCADE ON UPDATE CASCADE;
ERROR: action not supported for temporal foreign keys
--- partitioned FK parent deletes CASCADE
-- partitioned FK parent updates SET NULL
ALTER TABLE temporal_partitioned_fk_rng2rng
DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk,
@@ -941,7 +949,6 @@ ALTER TABLE temporal_partitioned_fk_rng2rng
REFERENCES temporal_partitioned_rng
ON DELETE SET NULL ON UPDATE SET NULL;
ERROR: action not supported for temporal foreign keys
--- partitioned FK parent deletes SET NULL
-- partitioned FK parent updates SET DEFAULT
ALTER TABLE temporal_partitioned_fk_rng2rng
ALTER COLUMN parent_id SET DEFAULT '[-1,-1]',
@@ -951,6 +958,5 @@ ALTER TABLE temporal_partitioned_fk_rng2rng
REFERENCES temporal_partitioned_rng
ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
ERROR: action not supported for temporal foreign keys
--- partitioned FK parent deletes SET DEFAULT
DROP TABLE temporal_partitioned_fk_rng2rng;
DROP TABLE temporal_partitioned_rng;
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index 641cdd5b..17bf7bdf 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)
);
@@ -473,6 +481,14 @@ ALTER TABLE temporal_fk_rng2rng
--
DELETE FROM temporal_fk_rng2rng;
+DELETE FROM temporal_rng;
+-- okay:
+INSERT INTO temporal_rng VALUES
+('[1,1]', tsrange('2018-01-02', '2018-02-03')),
+('[1,1]', tsrange('2018-03-03', '2018-04-04')),
+('[2,2]', tsrange('2018-01-01', '2018-01-05')),
+('[3,3]', tsrange('2018-01-01', NULL));
+
ALTER TABLE temporal_fk_rng2rng
DROP CONSTRAINT temporal_fk_rng2rng_fk;
INSERT INTO temporal_fk_rng2rng VALUES ('[1,1]', tsrange('2018-01-02', '2018-02-01'), '[1,1]');
@@ -545,8 +561,9 @@ INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') WHERE id = '[5,5]';
-- a PK update that succeeds even though the numeric id is referenced because the range isn't:
DELETE FROM temporal_rng WHERE id = '[5,5]';
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
+INSERT INTO temporal_rng VALUES
+ ('[5,5]', tsrange('2018-01-01', '2018-02-01')),
+ ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
INSERT INTO temporal_fk_rng2rng VALUES ('[3,3]', tsrange('2018-01-05', '2018-01-10'), '[5,5]');
UPDATE temporal_rng SET valid_at = tsrange('2016-02-01', '2016-03-01')
WHERE id = '[5,5]' AND valid_at = tsrange('2018-02-01', '2018-03-01');
@@ -574,14 +591,15 @@ ALTER TABLE temporal_fk_rng2rng
ADD CONSTRAINT temporal_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_rng
- ON DELETE RESTRICT;
+ ON UPDATE RESTRICT;
-- a PK update that succeeds because the numeric id isn't referenced:
INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') WHERE id = '[5,5]';
-- a PK update that succeeds even though the numeric id is referenced because the range isn't:
DELETE FROM temporal_rng WHERE id = '[5,5]';
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
+INSERT INTO temporal_rng VALUES
+ ('[5,5]', tsrange('2018-01-01', '2018-02-01')),
+ ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
INSERT INTO temporal_fk_rng2rng VALUES ('[3,3]', tsrange('2018-01-05', '2018-01-10'), '[5,5]');
UPDATE temporal_rng SET valid_at = tsrange('2016-02-01', '2016-03-01')
WHERE id = '[5,5]' AND valid_at = tsrange('2018-02-01', '2018-03-01');
@@ -611,8 +629,9 @@ ALTER TABLE temporal_fk_rng2rng
INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
DELETE FROM temporal_rng WHERE id = '[5,5]';
-- a PK delete that succeeds even though the numeric id is referenced because the range isn't:
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
+INSERT INTO temporal_rng VALUES
+ ('[5,5]', tsrange('2018-01-01', '2018-02-01')),
+ ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
INSERT INTO temporal_fk_rng2rng VALUES ('[3,3]', tsrange('2018-01-05', '2018-01-10'), '[5,5]');
DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-02-01', '2018-03-01');
-- a PK delete that fails because both are referenced:
@@ -635,8 +654,9 @@ ALTER TABLE temporal_fk_rng2rng
INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
DELETE FROM temporal_rng WHERE id = '[5,5]';
-- a PK delete that succeeds even though the numeric id is referenced because the range isn't:
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
+INSERT INTO temporal_rng VALUES
+ ('[5,5]', tsrange('2018-01-01', '2018-02-01')),
+ ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
INSERT INTO temporal_fk_rng2rng VALUES ('[3,3]', tsrange('2018-01-05', '2018-01-10'), '[5,5]');
DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-02-01', '2018-03-01');
-- a PK delete that fails because both are referenced:
@@ -646,12 +666,10 @@ DELETE FROM temporal_fk_rng2rng WHERE id = '[3,3]';
DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01');
--
--- test ON UPDATE/DELETE options
+-- test ON UPDATE/DELETE {set null | set default | CASCADE}
--
-- test FK parent updates CASCADE
-INSERT INTO temporal_rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng VALUES ('[4,4]', tsrange('2018-01-01', '2021-01-01'), '[6,6]');
ALTER TABLE temporal_fk_rng2rng
DROP CONSTRAINT temporal_fk_rng2rng_fk,
ADD CONSTRAINT temporal_fk_rng2rng_fk
@@ -660,8 +678,6 @@ ALTER TABLE temporal_fk_rng2rng
ON DELETE CASCADE ON UPDATE CASCADE;
-- test FK parent updates SET NULL
-INSERT INTO temporal_rng VALUES ('[9,9]', tsrange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01'), '[9,9]');
ALTER TABLE temporal_fk_rng2rng
DROP CONSTRAINT temporal_fk_rng2rng_fk,
ADD CONSTRAINT temporal_fk_rng2rng_fk
@@ -670,9 +686,6 @@ ALTER TABLE temporal_fk_rng2rng
ON DELETE SET NULL ON UPDATE SET NULL;
-- test FK parent updates SET DEFAULT
-INSERT INTO temporal_rng VALUES ('[-1,-1]', tsrange(null, null));
-INSERT INTO temporal_rng VALUES ('[12,12]', tsrange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng VALUES ('[8,8]', tsrange('2018-01-01', '2021-01-01'), '[12,12]');
ALTER TABLE temporal_fk_rng2rng
ALTER COLUMN parent_id SET DEFAULT '[-1,-1]',
DROP CONSTRAINT temporal_fk_rng2rng_fk,
@@ -796,8 +809,6 @@ ALTER TABLE temporal_partitioned_fk_rng2rng
REFERENCES temporal_partitioned_rng
ON DELETE CASCADE ON UPDATE CASCADE;
--- partitioned FK parent deletes CASCADE
-
-- partitioned FK parent updates SET NULL
ALTER TABLE temporal_partitioned_fk_rng2rng
@@ -807,10 +818,7 @@ ALTER TABLE temporal_partitioned_fk_rng2rng
REFERENCES temporal_partitioned_rng
ON DELETE SET NULL ON UPDATE SET NULL;
--- partitioned FK parent deletes SET NULL
-
-- partitioned FK parent updates SET DEFAULT
-
ALTER TABLE temporal_partitioned_fk_rng2rng
ALTER COLUMN parent_id SET DEFAULT '[-1,-1]',
DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk,
@@ -819,7 +827,5 @@ ALTER TABLE temporal_partitioned_fk_rng2rng
REFERENCES temporal_partitioned_rng
ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
--- partitioned FK parent deletes SET DEFAULT
-
DROP TABLE temporal_partitioned_fk_rng2rng;
DROP TABLE temporal_partitioned_rng;
--
2.34.1
