From 00c0561ab20309523058cf01fe425e722c9b487c Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Fri, 15 Mar 2024 17:43:16 -0700 Subject: [PATCH v28 5/9] Support multiranges or any type in temporal FKs Use referencedagg GiST support func to aggregate the PK records into one object that can be compared to the FK side. --- doc/src/sgml/ref/create_table.sgml | 5 +- src/backend/catalog/pg_constraint.c | 39 +- src/backend/commands/tablecmds.c | 3 +- src/backend/utils/adt/ri_triggers.c | 68 ++- src/include/catalog/pg_constraint.h | 7 +- .../regress/expected/without_overlaps.out | 421 ++++++++++++++++++ src/test/regress/sql/without_overlaps.sql | 352 +++++++++++++++ 7 files changed, 873 insertions(+), 22 deletions(-) diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index e4778a4079e..db93d1e5ca6 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1185,7 +1185,10 @@ WITH ( MODULUS numeric_literal, REM whose combined PERIOD values completely cover the referencing record's. In other words, the reference must have a referent for its entire duration. - This column must be a column with a range type. + Normally this column would be a range or multirange type, + although any type whose GiST opclass has a "contained by" operator + and a referencedagg support function is allowed. + (See .) In addition the referenced table must have a primary key or unique constraint declared with WITHOUT PORTION. diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index 27581c055fb..6a039a353cf 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -1607,21 +1607,29 @@ DeconstructFkConstraintRow(HeapTuple tuple, int *numfks, } /* - * FindFkPeriodOpers - + * FindFkPeriodOpersAndProcs - * - * Looks up the operator oids used for the PERIOD part of a temporal foreign key. + * Looks up the operator and support proc oids used for the PERIOD part of a temporal foreign key. * The opclass should be the opclass of that PERIOD element. * Everything else is an output: periodoperoid is the ContainedBy operator for - * types matching the PERIOD element. + * types matching the PERIOD element. periodprocoid is a GiST support function to + * aggregate multiple PERIOD element values into a single value + * (whose return type need not match its inputs, + * e.g. many ranges can be aggregated into a multirange). * And aggedperiodoperoid is also a ContainedBy operator, - * but one whose rhs is anymultirange. + * but one whose rhs matches the type returned by aggedperiodoperoid. * That way foreign keys can compare fkattr <@ range_agg(pkattr). */ void -FindFKPeriodOpers(Oid opclass, - Oid *periodoperoid, - Oid *aggedperiodoperoid) +FindFKPeriodOpersAndProcs(Oid opclass, + Oid *periodoperoid, + Oid *aggedperiodoperoid, + Oid *periodprocoid) { + Oid opfamily; + Oid opcintype; + Oid aggrettype; + Oid funcid = InvalidOid; StrategyNumber strat; /* @@ -1636,6 +1644,21 @@ FindFKPeriodOpers(Oid opclass, periodoperoid, &strat); + /* Now look up the support proc for aggregation. */ + if (get_opclass_opfamily_and_input_type(opclass, &opfamily, &opcintype)) + funcid = get_opfamily_proc(opfamily, opcintype, opcintype, GIST_REFERENCED_AGG_PROC); + + if (!OidIsValid(funcid)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("no support func %u found for FOREIGN KEY constraint", GIST_REFERENCED_AGG_PROC), + errhint("Define a referencedagg support function for your GiST opclass."))); + + *periodprocoid = funcid; + + /* Look up the function's rettype. */ + aggrettype = get_func_rettype(funcid); + /* * Now look up the ContainedBy operator. * Its left arg must be the type of the column (or rather of the opclass). @@ -1643,7 +1666,7 @@ FindFKPeriodOpers(Oid opclass, */ strat = RTContainedByStrategyNumber; GetOperatorFromWellKnownStrategy(opclass, - ANYMULTIRANGEOID, + aggrettype, aggedperiodoperoid, &strat); } diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 9de519ea6e5..48aa45c0b51 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -10257,8 +10257,9 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, { Oid periodoperoid; Oid aggedperiodoperoid; + Oid periodprocoid; - FindFKPeriodOpers(opclasses[numpks - 1], &periodoperoid, &aggedperiodoperoid); + FindFKPeriodOpersAndProcs(opclasses[numpks - 1], &periodoperoid, &aggedperiodoperoid, &periodprocoid); } /* diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c index a84b4ee3387..f9cb8134841 100644 --- a/src/backend/utils/adt/ri_triggers.c +++ b/src/backend/utils/adt/ri_triggers.c @@ -124,8 +124,13 @@ typedef struct RI_ConstraintInfo Oid pf_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (PK = FK) */ Oid pp_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (PK = PK) */ Oid ff_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (FK = FK) */ - Oid period_contained_by_oper; /* anyrange <@ anyrange */ - Oid agged_period_contained_by_oper; /* anyrange <@ anymultirange */ + Oid period_contained_by_oper; /* anyrange <@ anyrange, + (or whatever type is used) */ + Oid agged_period_contained_by_oper; /* anyrange <@ referencedagg result + (or whatever types are used) */ + Oid period_referenced_agg_proc; /* referencedagg GiST support proc + to combine PK ranges */ + Oid period_referenced_agg_rettype; /* rettype for previous */ dlist_node valid_link; /* Link in list of valid entries */ } RI_ConstraintInfo; @@ -233,6 +238,7 @@ static void ri_ReportViolation(const RI_ConstraintInfo *riinfo, Relation pk_rel, Relation fk_rel, TupleTableSlot *violatorslot, TupleDesc tupdesc, int queryno, bool partgone) pg_attribute_noreturn(); +static void lookupPeriodRIProc(const RI_ConstraintInfo *riinfo, char **aggname); /* @@ -421,15 +427,17 @@ RI_FKey_check(TriggerData *trigdata) appendStringInfoString(&querybuf, " FOR KEY SHARE OF x"); if (riinfo->hasperiod) { + char *aggname; Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1]); - Oid agg_rettype = ANYMULTIRANGEOID; + Oid agg_rettype = riinfo->period_referenced_agg_rettype; + lookupPeriodRIProc(riinfo, &aggname); appendStringInfo(&querybuf, ") x1 HAVING "); sprintf(paramname, "$%d", riinfo->nkeys); ri_GenerateQual(&querybuf, "", paramname, fk_type, riinfo->agged_period_contained_by_oper, - "pg_catalog.range_agg", agg_rettype); + aggname, agg_rettype); appendStringInfo(&querybuf, "(x1.r)"); } @@ -591,15 +599,17 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel, appendStringInfoString(&querybuf, " FOR KEY SHARE OF x"); if (riinfo->hasperiod) { + char *aggname; Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1]); - Oid agg_rettype = ANYMULTIRANGEOID; + Oid agg_rettype = riinfo->period_referenced_agg_rettype; + lookupPeriodRIProc(riinfo, &aggname); appendStringInfo(&querybuf, ") x1 HAVING "); sprintf(paramname, "$%d", riinfo->nkeys); ri_GenerateQual(&querybuf, "", paramname, fk_type, riinfo->agged_period_contained_by_oper, - "pg_catalog.range_agg", agg_rettype); + aggname, agg_rettype); appendStringInfo(&querybuf, "(x1.r)"); } @@ -2272,9 +2282,11 @@ ri_LoadConstraintInfo(Oid constraintOid) if (riinfo->hasperiod) { Oid opclass = get_index_column_opclass(conForm->conindid, riinfo->nkeys); - FindFKPeriodOpers(opclass, - &riinfo->period_contained_by_oper, - &riinfo->agged_period_contained_by_oper); + FindFKPeriodOpersAndProcs(opclass, + &riinfo->period_contained_by_oper, + &riinfo->agged_period_contained_by_oper, + &riinfo->period_referenced_agg_proc); + riinfo->period_referenced_agg_rettype = get_func_rettype(riinfo->period_referenced_agg_proc); } ReleaseSysCache(tup); @@ -3137,3 +3149,41 @@ RI_FKey_trigger_type(Oid tgfoid) return RI_TRIGGER_NONE; } + +/* + * lookupPeriodRIProc - + * + * Gets the name of the aggregate function + * used to build the SQL for temporal RI constraints. + * Raises an error if not found. + */ +static void +lookupPeriodRIProc(const RI_ConstraintInfo *riinfo, char **aggname) +{ + Oid oid = riinfo->period_referenced_agg_proc; + HeapTuple tp; + Form_pg_proc functup; + char *namesp; + char *func; + + + if (!OidIsValid(oid)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("no referencedagg support function for foreign key constraint \"%s\"", + NameStr(riinfo->conname)), + errhint("You must use an operator class with a referencedagg support function."))); + + tp = SearchSysCache1(PROCOID, ObjectIdGetDatum(riinfo->period_referenced_agg_proc)); + if (!HeapTupleIsValid(tp)) + elog(ERROR, "cache lookup failed for function %u", oid); + + functup = (Form_pg_proc) GETSTRUCT(tp); + namesp = get_namespace_name(functup->pronamespace); + func = NameStr(functup->proname); + + *aggname = psprintf("%s.%s", quote_identifier(namesp), quote_identifier(func)); + + pfree(namesp); + ReleaseSysCache(tp); +} diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h index c644c7ddbbe..e4f4c2ffaf1 100644 --- a/src/include/catalog/pg_constraint.h +++ b/src/include/catalog/pg_constraint.h @@ -283,9 +283,10 @@ extern void DeconstructFkConstraintRow(HeapTuple tuple, int *numfks, AttrNumber *conkey, AttrNumber *confkey, Oid *pf_eq_oprs, Oid *pp_eq_oprs, Oid *ff_eq_oprs, int *num_fk_del_set_cols, AttrNumber *fk_del_set_cols); -extern void FindFKPeriodOpers(Oid opclass, - Oid *periodoperoid, - Oid *aggedperiodoperoid); +extern void FindFKPeriodOpersAndProcs(Oid opclass, + Oid *periodoperoid, + Oid *aggedperiodoperoid, + Oid *periodprocoid); extern bool check_functional_grouping(Oid relid, Index varno, Index varlevelsup, diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out index cdc55395133..a3eecf1d79b 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -99,6 +99,51 @@ CREATE TABLE temporal_rng3 ( ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk; DROP TABLE temporal_rng3; DROP TYPE textrange2; +-- PK with one column plus a multirange: +CREATE TABLE temporal_mltrng ( + id int4range, + valid_at datemultirange, + CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +\d temporal_mltrng + Table "public.temporal_mltrng" + Column | Type | Collation | Nullable | Default +----------+----------------+-----------+----------+--------- + id | int4range | | not null | + valid_at | datemultirange | | not null | +Indexes: + "temporal_mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) + +-- PK with two columns plus a multirange: +-- We don't drop this table because tests below also need multiple scalar columns. +CREATE TABLE temporal_mltrng2 ( + id1 int4range, + id2 int4range, + valid_at datemultirange, + CONSTRAINT temporal_mltrng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) +); +\d temporal_mltrng2 + Table "public.temporal_mltrng2" + Column | Type | Collation | Nullable | Default +----------+----------------+-----------+----------+--------- + id1 | int4range | | not null | + id2 | int4range | | not null | + valid_at | datemultirange | | not null | +Indexes: + "temporal_mltrng2_pk" PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) + +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk'; + pg_get_constraintdef +--------------------------------------------------- + PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) +(1 row) + +SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk'; + pg_get_indexdef +--------------------------------------------------------------------------------------------- + CREATE UNIQUE INDEX temporal_mltrng2_pk ON temporal_mltrng2 USING gist (id1, id2, valid_at) +(1 row) + -- UNIQUE with no columns just WITHOUT OVERLAPS: CREATE TABLE temporal_rng3 ( valid_at daterange, @@ -275,6 +320,30 @@ DETAIL: Failing row contains (null, [2018-01-01,2018-01-05)). INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', NULL); ERROR: null value in column "valid_at" of relation "temporal_rng" violates not-null constraint DETAIL: Failing row contains ([3,4), null). +-- 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" +DETAIL: Key (id, valid_at)=([1,2), {[2018-01-01,2018-01-05)}) conflicts with existing key (id, valid_at)=([1,2), {[2018-01-02,2018-02-03)}). +INSERT INTO temporal_mltrng (id, valid_at) VALUES (NULL, datemultirange(daterange('2018-01-01', '2018-01-05'))); +ERROR: null value in column "id" of relation "temporal_mltrng" violates not-null constraint +DETAIL: Failing row contains (null, {[2018-01-01,2018-01-05)}). +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', NULL); +ERROR: null value in column "valid_at" of relation "temporal_mltrng" violates not-null constraint +DETAIL: Failing row contains ([3,4), null). +SELECT * FROM temporal_mltrng ORDER BY id, valid_at; + id | valid_at +-------+--------------------------- + [1,2) | {[2018-01-02,2018-02-03)} + [1,2) | {[2018-03-03,2018-04-04)} + [2,3) | {[2018-01-01,2018-01-05)} + [3,4) | {[2018-01-01,)} +(4 rows) + -- -- test a range with both a PK and a UNIQUE constraint -- @@ -828,6 +897,358 @@ ALTER TABLE temporal_fk_rng2rng ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; ERROR: invalid ON DELETE action for foreign key constraint using PERIOD -- +-- test FOREIGN KEY, multirange references multirange +-- +-- Can't create a FK with a mismatched multirange type +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at int4multirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk2 PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_mltrng2mltrng_fk2 FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (id, PERIOD valid_at) +); +ERROR: foreign key constraint "temporal_fk_mltrng2mltrng_fk2" cannot be implemented +DETAIL: Key columns "valid_at" and "valid_at" are of incompatible types: int4multirange and datemultirange. +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (id, PERIOD valid_at) +); +DROP TABLE temporal_fk_mltrng2mltrng; +-- with mismatched PERIOD columns: +-- (parent_id, PERIOD valid_at) REFERENCES (id, valid_at) +-- REFERENCES part should specify PERIOD +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (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) +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, valid_at) + REFERENCES temporal_mltrng (id, PERIOD valid_at) +); +ERROR: foreign key uses PERIOD on the referenced table but not the referencing table +-- (parent_id, valid_at) REFERENCES [implicit] +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, valid_at) + REFERENCES temporal_mltrng +); +ERROR: number of referencing and referenced columns for foreign key disagree +-- (parent_id, PERIOD valid_at) REFERENCES (id) +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (id) +); +ERROR: foreign key uses PERIOD on the referencing table but not the referenced table +-- (parent_id) REFERENCES (id, PERIOD valid_at) +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id) + REFERENCES temporal_mltrng (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: +-- (This is not permitted by the SQL standard. See 11.8 syntax rule 4b.) +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng +); +ERROR: foreign key uses PERIOD on the referencing table but not the referenced table +-- (parent_id) REFERENCES [implicit] +-- This finds the PK (omitting the WITHOUT OVERLAPS element), +-- but it's not a b-tree index, so it fails anyway. +-- Anyway it must fail because the two sides have a different definition of "unique". +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id) + REFERENCES temporal_mltrng +); +ERROR: only b-tree indexes are supported for foreign keys +-- should fail because of duplicate referenced columns: +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD parent_id) + REFERENCES temporal_mltrng (id, PERIOD id) +); +ERROR: foreign key referenced-columns list must not contain duplicates +-- Two scalar columns +CREATE TABLE temporal_fk2_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id1 int4range, + parent_id2 int4range, + CONSTRAINT temporal_fk2_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk2_mltrng2mltrng_fk FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_mltrng2 (id1, id2, PERIOD valid_at) +); +\d temporal_fk2_mltrng2mltrng + Table "public.temporal_fk2_mltrng2mltrng" + Column | Type | Collation | Nullable | Default +------------+----------------+-----------+----------+--------- + id | int4range | | not null | + valid_at | datemultirange | | not null | + parent_id1 | int4range | | | + parent_id2 | int4range | | | +Indexes: + "temporal_fk2_mltrng2mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +Foreign-key constraints: + "temporal_fk2_mltrng2mltrng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_mltrng2(id1, id2, PERIOD valid_at) + +DROP TABLE temporal_fk2_mltrng2mltrng; +-- +-- test ALTER TABLE ADD CONSTRAINT +-- +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (id, PERIOD valid_at); +-- Two scalar columns: +CREATE TABLE temporal_fk2_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id1 int4range, + parent_id2 int4range, + CONSTRAINT temporal_fk2_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +ALTER TABLE temporal_fk2_mltrng2mltrng + ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_mltrng2 (id1, id2, PERIOD valid_at); +\d temporal_fk2_mltrng2mltrng + Table "public.temporal_fk2_mltrng2mltrng" + Column | Type | Collation | Nullable | Default +------------+----------------+-----------+----------+--------- + id | int4range | | not null | + valid_at | datemultirange | | not null | + parent_id1 | int4range | | | + parent_id2 | int4range | | | +Indexes: + "temporal_fk2_mltrng2mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +Foreign-key constraints: + "temporal_fk2_mltrng2mltrng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_mltrng2(id1, id2, PERIOD valid_at) + +-- should fail because of duplicate referenced columns: +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk2 + FOREIGN KEY (parent_id, PERIOD parent_id) + REFERENCES temporal_mltrng (id, PERIOD id); +ERROR: foreign key referenced-columns list must not contain duplicates +-- +-- test with rows already +-- +DELETE FROM temporal_fk_mltrng2mltrng; +ALTER TABLE temporal_fk_mltrng2mltrng + DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-01')), '[1,2)'); +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (id, PERIOD valid_at); +ALTER TABLE temporal_fk_mltrng2mltrng + DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,2)'); +-- should fail: +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (id, PERIOD valid_at); +ERROR: insert or update on table "temporal_fk_mltrng2mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" +DETAIL: Key (parent_id, valid_at)=([1,2), {[2018-01-02,2018-04-01)}) is not present in table "temporal_mltrng". +-- okay again: +DELETE FROM temporal_fk_mltrng2mltrng; +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (id, PERIOD valid_at); +-- +-- test pg_get_constraintdef +-- +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_mltrng2mltrng_fk'; + pg_get_constraintdef +------------------------------------------------------------------------------------------ + FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_mltrng(id, PERIOD valid_at) +(1 row) + +-- +-- test FK referencing inserts +-- +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-01')), '[1,2)'); +-- should fail: +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,2)'); +ERROR: insert or update on table "temporal_fk_mltrng2mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" +DETAIL: Key (parent_id, valid_at)=([1,2), {[2018-01-02,2018-04-01)}) is not present in table "temporal_mltrng". +-- now it should work: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-02-03', '2018-03-03'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,2)'); +-- +-- test FK referencing updates +-- +UPDATE temporal_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2018-01-02', '2018-03-01')) WHERE id = '[1,2)'; +-- should fail: +UPDATE temporal_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2018-01-02', '2018-05-01')) WHERE id = '[1,2)'; +ERROR: insert or update on table "temporal_fk_mltrng2mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" +DETAIL: Key (parent_id, valid_at)=([1,2), {[2018-01-02,2018-05-01)}) is not present in table "temporal_mltrng". +UPDATE temporal_fk_mltrng2mltrng SET parent_id = '[8,9)' WHERE id = '[1,2)'; +ERROR: insert or update on table "temporal_fk_mltrng2mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" +DETAIL: Key (parent_id, valid_at)=([8,9), {[2018-01-02,2018-03-01)}) is not present in table "temporal_mltrng". +-- ALTER FK DEFERRABLE +BEGIN; + INSERT INTO temporal_mltrng (id, valid_at) VALUES + ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), + ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); + INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES + ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); + ALTER TABLE temporal_fk_mltrng2mltrng + ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk + DEFERRABLE INITIALLY DEFERRED; + DELETE FROM temporal_mltrng WHERE id = '[5,6)'; --should not fail yet. +COMMIT; -- should fail here. +ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" +DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- +-- test FK referenced updates NO ACTION +-- +TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; +-- a PK update that succeeds because the numeric id isn't referenced: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); +UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) WHERE id = '[5,6)'; +-- a PK update that succeeds even though the numeric id is referenced because the range isn't: +DELETE FROM temporal_mltrng WHERE id = '[5,6)'; +INSERT INTO temporal_mltrng (id, valid_at) VALUES + ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), + ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); +UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03-01')) +WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); +-- a PK update that fails because both are referenced: +UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) +WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" +DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- changing the scalar part fails: +UPDATE temporal_mltrng SET id = '[7,8)' +WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" +DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- +-- test FK referenced updates RESTRICT +-- +TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; +ALTER TABLE temporal_fk_mltrng2mltrng + DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (id, PERIOD valid_at) + ON UPDATE RESTRICT; +-- a PK update that succeeds because the numeric id isn't referenced: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); +UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) WHERE id = '[5,6)'; +-- a PK update that succeeds even though the numeric id is referenced because the range isn't: +DELETE FROM temporal_mltrng WHERE id = '[5,6)'; +INSERT INTO temporal_mltrng (id, valid_at) VALUES + ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), + ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); +UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03-01')) +WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); +-- a PK update that fails because both are referenced: +UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) +WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" +DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- changing the scalar part fails: +UPDATE temporal_mltrng SET id = '[7,8)' +WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" +DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- +-- test FK referenced deletes NO ACTION +-- +TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; +ALTER TABLE temporal_fk_mltrng2mltrng + DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (id, PERIOD valid_at); +-- a PK delete that succeeds because the numeric id isn't referenced: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); +DELETE FROM temporal_mltrng WHERE id = '[5,6)'; +-- a PK delete that succeeds even though the numeric id is referenced because the range isn't: +INSERT INTO temporal_mltrng (id, valid_at) VALUES + ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), + ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); +DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); +-- a PK delete that fails because both are referenced: +DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" +DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- +-- test FK referenced deletes RESTRICT +-- +TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; +ALTER TABLE temporal_fk_mltrng2mltrng + DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (id, PERIOD valid_at) + ON DELETE RESTRICT; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); +DELETE FROM temporal_mltrng WHERE id = '[5,6)'; +-- a PK delete that succeeds even though the numeric id is referenced because the range isn't: +INSERT INTO temporal_mltrng (id, valid_at) VALUES + ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), + ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); +DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); +-- a PK delete that fails because both are referenced: +DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" +DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- -- FK between partitioned tables -- CREATE TABLE temporal_partitioned_rng ( diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql index 48bcaa18a09..8f9832ba781 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -69,6 +69,26 @@ ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk; DROP TABLE temporal_rng3; DROP TYPE textrange2; +-- PK with one column plus a multirange: +CREATE TABLE temporal_mltrng ( + id int4range, + valid_at datemultirange, + CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +\d temporal_mltrng + +-- PK with two columns plus a multirange: +-- We don't drop this table because tests below also need multiple scalar columns. +CREATE TABLE temporal_mltrng2 ( + id1 int4range, + id2 int4range, + valid_at datemultirange, + CONSTRAINT temporal_mltrng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) +); +\d temporal_mltrng2 +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk'; +SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk'; + -- UNIQUE with no columns just WITHOUT OVERLAPS: CREATE TABLE temporal_rng3 ( @@ -207,6 +227,19 @@ INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', INSERT INTO temporal_rng (id, valid_at) VALUES (NULL, daterange('2018-01-01', '2018-01-05')); INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', NULL); +-- 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'))); +INSERT INTO temporal_mltrng (id, valid_at) VALUES (NULL, datemultirange(daterange('2018-01-01', '2018-01-05'))); +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', NULL); + +SELECT * FROM temporal_mltrng ORDER BY id, valid_at; + -- -- test a range with both a PK and a UNIQUE constraint -- @@ -699,6 +732,325 @@ ALTER TABLE temporal_fk_rng2rng REFERENCES temporal_rng (id, PERIOD valid_at) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; +-- +-- test FOREIGN KEY, multirange references multirange +-- + +-- Can't create a FK with a mismatched multirange type +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at int4multirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk2 PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_mltrng2mltrng_fk2 FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (id, PERIOD valid_at) +); + +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (id, PERIOD valid_at) +); +DROP TABLE temporal_fk_mltrng2mltrng; + +-- with mismatched PERIOD columns: +-- (parent_id, PERIOD valid_at) REFERENCES (id, valid_at) +-- REFERENCES part should specify PERIOD +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (id, valid_at) +); +-- (parent_id, valid_at) REFERENCES (id, PERIOD valid_at) +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, valid_at) + REFERENCES temporal_mltrng (id, PERIOD valid_at) +); +-- (parent_id, valid_at) REFERENCES [implicit] +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, valid_at) + REFERENCES temporal_mltrng +); +-- (parent_id, PERIOD valid_at) REFERENCES (id) +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (id) +); +-- (parent_id) REFERENCES (id, PERIOD valid_at) +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id) + REFERENCES temporal_mltrng (id, PERIOD valid_at) +); + +-- with inferred PK on the referenced table: +-- (This is not permitted by the SQL standard. See 11.8 syntax rule 4b.) +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng +); +-- (parent_id) REFERENCES [implicit] +-- This finds the PK (omitting the WITHOUT OVERLAPS element), +-- but it's not a b-tree index, so it fails anyway. +-- Anyway it must fail because the two sides have a different definition of "unique". +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id) + REFERENCES temporal_mltrng +); + +-- should fail because of duplicate referenced columns: +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD parent_id) + REFERENCES temporal_mltrng (id, PERIOD id) +); + +-- Two scalar columns +CREATE TABLE temporal_fk2_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id1 int4range, + parent_id2 int4range, + CONSTRAINT temporal_fk2_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk2_mltrng2mltrng_fk FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_mltrng2 (id1, id2, PERIOD valid_at) +); +\d temporal_fk2_mltrng2mltrng +DROP TABLE temporal_fk2_mltrng2mltrng; + +-- +-- test ALTER TABLE ADD CONSTRAINT +-- + +CREATE TABLE temporal_fk_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id int4range, + CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (id, PERIOD valid_at); +-- Two scalar columns: +CREATE TABLE temporal_fk2_mltrng2mltrng ( + id int4range, + valid_at datemultirange, + parent_id1 int4range, + parent_id2 int4range, + CONSTRAINT temporal_fk2_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +ALTER TABLE temporal_fk2_mltrng2mltrng + ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_mltrng2 (id1, id2, PERIOD valid_at); +\d temporal_fk2_mltrng2mltrng + +-- should fail because of duplicate referenced columns: +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk2 + FOREIGN KEY (parent_id, PERIOD parent_id) + REFERENCES temporal_mltrng (id, PERIOD id); + +-- +-- test with rows already +-- + +DELETE FROM temporal_fk_mltrng2mltrng; +ALTER TABLE temporal_fk_mltrng2mltrng + DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-01')), '[1,2)'); +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (id, PERIOD valid_at); +ALTER TABLE temporal_fk_mltrng2mltrng + DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,2)'); +-- should fail: +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (id, PERIOD valid_at); +-- okay again: +DELETE FROM temporal_fk_mltrng2mltrng; +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (id, PERIOD valid_at); + +-- +-- test pg_get_constraintdef +-- + +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_mltrng2mltrng_fk'; + +-- +-- test FK referencing inserts +-- + +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-01')), '[1,2)'); +-- should fail: +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,2)'); +-- now it should work: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-02-03', '2018-03-03'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,2)'); + +-- +-- test FK referencing updates +-- + +UPDATE temporal_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2018-01-02', '2018-03-01')) WHERE id = '[1,2)'; +-- should fail: +UPDATE temporal_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2018-01-02', '2018-05-01')) WHERE id = '[1,2)'; +UPDATE temporal_fk_mltrng2mltrng SET parent_id = '[8,9)' WHERE id = '[1,2)'; + +-- ALTER FK DEFERRABLE + +BEGIN; + INSERT INTO temporal_mltrng (id, valid_at) VALUES + ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), + ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); + INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES + ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); + ALTER TABLE temporal_fk_mltrng2mltrng + ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk + DEFERRABLE INITIALLY DEFERRED; + + DELETE FROM temporal_mltrng WHERE id = '[5,6)'; --should not fail yet. +COMMIT; -- should fail here. + +-- +-- test FK referenced updates NO ACTION +-- + +TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; +-- a PK update that succeeds because the numeric id isn't referenced: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); +UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) WHERE id = '[5,6)'; +-- a PK update that succeeds even though the numeric id is referenced because the range isn't: +DELETE FROM temporal_mltrng WHERE id = '[5,6)'; +INSERT INTO temporal_mltrng (id, valid_at) VALUES + ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), + ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); +UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03-01')) +WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); +-- a PK update that fails because both are referenced: +UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) +WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +-- changing the scalar part fails: +UPDATE temporal_mltrng SET id = '[7,8)' +WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); + +-- +-- test FK referenced updates RESTRICT +-- + +TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; +ALTER TABLE temporal_fk_mltrng2mltrng + DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (id, PERIOD valid_at) + ON UPDATE RESTRICT; +-- a PK update that succeeds because the numeric id isn't referenced: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); +UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) WHERE id = '[5,6)'; +-- a PK update that succeeds even though the numeric id is referenced because the range isn't: +DELETE FROM temporal_mltrng WHERE id = '[5,6)'; +INSERT INTO temporal_mltrng (id, valid_at) VALUES + ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), + ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); +UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03-01')) +WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); +-- a PK update that fails because both are referenced: +UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) +WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +-- changing the scalar part fails: +UPDATE temporal_mltrng SET id = '[7,8)' +WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); + +-- +-- test FK referenced deletes NO ACTION +-- + +TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; +ALTER TABLE temporal_fk_mltrng2mltrng + DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (id, PERIOD valid_at); +-- a PK delete that succeeds because the numeric id isn't referenced: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); +DELETE FROM temporal_mltrng WHERE id = '[5,6)'; +-- a PK delete that succeeds even though the numeric id is referenced because the range isn't: +INSERT INTO temporal_mltrng (id, valid_at) VALUES + ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), + ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); +DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); +-- a PK delete that fails because both are referenced: +DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); + +-- +-- test FK referenced deletes RESTRICT +-- + +TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; +ALTER TABLE temporal_fk_mltrng2mltrng + DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng (id, PERIOD valid_at) + ON DELETE RESTRICT; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); +DELETE FROM temporal_mltrng WHERE id = '[5,6)'; +-- a PK delete that succeeds even though the numeric id is referenced because the range isn't: +INSERT INTO temporal_mltrng (id, valid_at) VALUES + ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), + ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); +DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); +-- a PK delete that fails because both are referenced: +DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); + -- -- FK between partitioned tables -- -- 2.42.0