From 7412fd4886e30f3924e37cf95fab5329f0ae39a1 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Sun, 27 Jun 2021 12:58:51 -0700 Subject: [PATCH v27 4/8] Add temporal FOREIGN KEYs - Added bison support for temporal FOREIGN KEYs. They should have a range/multirange/etc column for the temporal component. - Added temporal trigger functions akin to existing RI trigger functions. - Don't yet support ON {UPDATE,DELETE} {CASCADE,SET NULL,SET DEFAULT}. - Added pg_dump support. - Show the correct syntax in psql \d output for foreign keys. - Added tests and documentation. --- .../btree_gist/expected/without_overlaps.out | 48 + contrib/btree_gist/sql/without_overlaps.sql | 28 + doc/src/sgml/ref/create_table.sgml | 46 +- src/backend/catalog/pg_constraint.c | 56 + src/backend/commands/indexcmds.c | 48 +- src/backend/commands/tablecmds.c | 387 +++++-- src/backend/parser/gram.y | 45 +- src/backend/utils/adt/ri_triggers.c | 300 ++++- src/backend/utils/adt/ruleutils.c | 19 +- src/include/catalog/pg_constraint.h | 20 +- src/include/catalog/pg_proc.dat | 24 + src/include/commands/defrem.h | 2 +- src/include/nodes/parsenodes.h | 2 + src/include/parser/kwlist.h | 1 + src/test/regress/expected/btree_index.out | 16 +- .../regress/expected/without_overlaps.out | 1018 ++++++++++++++++- src/test/regress/sql/without_overlaps.sql | 924 ++++++++++++++- 17 files changed, 2815 insertions(+), 169 deletions(-) diff --git a/contrib/btree_gist/expected/without_overlaps.out b/contrib/btree_gist/expected/without_overlaps.out index be52c522e89..18856900ded 100644 --- a/contrib/btree_gist/expected/without_overlaps.out +++ b/contrib/btree_gist/expected/without_overlaps.out @@ -42,3 +42,51 @@ INSERT INTO temporal_rng VALUES (1, '[2000-06-01,2001-01-01)'); ERROR: conflicting key value violates exclusion constraint "temporal_rng_pk" DETAIL: Key (id, valid_at)=(1, [06-01-2000,01-01-2001)) conflicts with existing key (id, valid_at)=(1, [01-01-2000,01-01-2001)). +-- Foreign key +CREATE TABLE temporal_fk_rng2rng ( + id integer, + valid_at daterange, + parent_id integer, + 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) +); +\d temporal_fk_rng2rng + Table "public.temporal_fk_rng2rng" + Column | Type | Collation | Nullable | Default +-----------+-----------+-----------+----------+--------- + id | integer | | not null | + valid_at | daterange | | not null | + parent_id | integer | | | +Indexes: + "temporal_fk_rng2rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +Foreign-key constraints: + "temporal_fk_rng2rng_fk" FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng(id, PERIOD valid_at) + +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_rng2rng_fk'; + pg_get_constraintdef +--------------------------------------------------------------------------------------- + FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng(id, PERIOD valid_at) +(1 row) + +-- okay +INSERT INTO temporal_fk_rng2rng VALUES + (1, '[2000-01-01,2001-01-01)', 1); +-- okay spanning two parent records: +INSERT INTO temporal_fk_rng2rng VALUES + (2, '[2000-01-01,2002-01-01)', 1); +-- key is missing +INSERT INTO temporal_fk_rng2rng VALUES + (3, '[2000-01-01,2001-01-01)', 3); +ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=(3, [01-01-2000,01-01-2001)) is not present in table "temporal_rng". +-- key exist but is outside range +INSERT INTO temporal_fk_rng2rng VALUES + (4, '[2001-01-01,2002-01-01)', 2); +ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=(2, [01-01-2001,01-01-2002)) is not present in table "temporal_rng". +-- key exist but is partly outside range +INSERT INTO temporal_fk_rng2rng VALUES + (5, '[2000-01-01,2002-01-01)', 2); +ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=(2, [01-01-2000,01-01-2002)) is not present in table "temporal_rng". diff --git a/contrib/btree_gist/sql/without_overlaps.sql b/contrib/btree_gist/sql/without_overlaps.sql index 433c609ab22..b1b581fcabc 100644 --- a/contrib/btree_gist/sql/without_overlaps.sql +++ b/contrib/btree_gist/sql/without_overlaps.sql @@ -23,3 +23,31 @@ INSERT INTO temporal_rng VALUES -- should fail: INSERT INTO temporal_rng VALUES (1, '[2000-06-01,2001-01-01)'); + +-- Foreign key +CREATE TABLE temporal_fk_rng2rng ( + id integer, + valid_at daterange, + parent_id integer, + 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) +); +\d temporal_fk_rng2rng +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_rng2rng_fk'; + +-- okay +INSERT INTO temporal_fk_rng2rng VALUES + (1, '[2000-01-01,2001-01-01)', 1); +-- okay spanning two parent records: +INSERT INTO temporal_fk_rng2rng VALUES + (2, '[2000-01-01,2002-01-01)', 1); +-- key is missing +INSERT INTO temporal_fk_rng2rng VALUES + (3, '[2000-01-01,2001-01-01)', 3); +-- key exist but is outside range +INSERT INTO temporal_fk_rng2rng VALUES + (4, '[2001-01-01,2002-01-01)', 2); +-- key exist but is partly outside range +INSERT INTO temporal_fk_rng2rng VALUES + (5, '[2000-01-01,2002-01-01)', 2); diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 4cbaaccaf7c..0c39bad6c76 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -81,7 +81,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | - FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] + FOREIGN KEY ( column_name [, ... ] [, PERIOD column_name ] ) REFERENCES reftable [ ( refcolumn [, ... ] [, PERIOD column_name ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] @@ -1152,8 +1152,8 @@ WITH ( MODULUS numeric_literal, REM REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] (column constraint) - FOREIGN KEY ( column_name [, ... ] ) - REFERENCES reftable [ ( refcolumn [, ... ] ) ] + FOREIGN KEY ( column_name [, ... ] [, PERIOD column_name ] ) + REFERENCES reftable [ ( refcolumn [, ... ] [, PERIOD column_name ] ) ] [ MATCH matchtype ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] @@ -1164,12 +1164,36 @@ WITH ( MODULUS numeric_literal, REM These clauses specify a foreign key constraint, which requires that a group of one or more columns of the new table must only contain values that match values in the referenced - column(s) of some row of the referenced table. If the refcolumn list is omitted, the primary key of the reftable is used. Otherwise, the refcolumn list must refer to the columns of a non-deferrable unique or primary key - constraint or be the columns of a non-partial unique index. The user + constraint or be the columns of a non-partial unique index. + + + + If the last column is marked with PERIOD, + it is treated in a special way. + While the non-PERIOD columns are treated normally + (and there must be at least one of them), + the PERIOD column is not compared for equality. + Instead the constraint is considered satisfied + if the referenced table has matching records + (based on the non-PERIOD parts of the key) + whose combined PERIOD values completely cover + the referencing record's. + In other words, the reference must have a referent for its entire duration. + Normally this column would be a range or multirange type, + although any type whose GiST opclass has a "contained by" operator + and a referenced_agg support function is allowed. + (See .) + In addition the referenced table must have a primary key + or unique constraint declared with WITHOUT PORTION. + + + + The user must have REFERENCES permission on the referenced table (either the whole table, or the specific referenced columns). The addition of a foreign key constraint requires a @@ -1243,6 +1267,10 @@ WITH ( MODULUS numeric_literal, REM values of the referencing column(s) to the new values of the referenced columns, respectively. + + + In a temporal foreign key, this option is not supported. + @@ -1254,6 +1282,10 @@ WITH ( MODULUS numeric_literal, REM referencing columns, to null. A subset of columns can only be specified for ON DELETE actions. + + + In a temporal foreign key, this option is not supported. + @@ -1267,6 +1299,10 @@ WITH ( MODULUS numeric_literal, REM (There must be a row in the referenced table matching the default values, if they are not null, or the operation will fail.) + + + In a temporal foreign key, this option is not supported. + diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index 3516fd58493..bac1f8fc916 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -15,6 +15,7 @@ #include "postgres.h" #include "access/genam.h" +#include "access/gist.h" #include "access/htup_details.h" #include "access/sysattr.h" #include "access/table.h" @@ -1607,6 +1608,61 @@ DeconstructFkConstraintRow(HeapTuple tuple, int *numfks, *numfks = numkeys; } +/* + * FindFkPeriodOpersAndProcs - + * + * Looks up the oper and proc oids used by foreign keys with a PERIOD part. + */ +void +FindFKPeriodOpersAndProcs(Oid opclass, + Oid *periodoperoid, + Oid *aggedperiodoperoid, + Oid *periodprocoid) +{ + Oid opfamily; + Oid opcintype; + Oid aggrettype; + Oid funcid = InvalidOid; + StrategyNumber strat = RTContainedByStrategyNumber; + + /* + * Look up the ContainedBy operator with symmetric types. + * We use this to optimize RI checks: if the new value includes all + * of the old value, then we can treat the attribute as if it didn't change, + * and skip the RI check. + */ + GetOperatorFromWellKnownStrategy(opclass, + InvalidOid, + 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). + * Its right arg must match the return type of the support proc. + */ + strat = RTContainedByStrategyNumber; + GetOperatorFromWellKnownStrategy(opclass, + aggrettype, + aggedperiodoperoid, + &strat); +} + /* * Determine whether a relation can be proven functionally dependent on * a set of grouping columns. If so, return true and add the pg_constraint diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 7a87626f5f0..5c7cc967e79 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -2181,8 +2181,10 @@ ComputeIndexAttrs(IndexInfo *indexInfo, strat = RTOverlapStrategyNumber; else strat = RTEqualStrategyNumber; - GetOperatorFromWellKnownStrategy(opclassOids[attn], atttype, - &opid, &strat); + GetOperatorFromWellKnownStrategy(opclassOids[attn], + InvalidOid, + &opid, + &strat); indexInfo->ii_ExclusionOps[attn] = opid; indexInfo->ii_ExclusionProcs[attn] = get_opcode(opid); indexInfo->ii_ExclusionStrats[attn] = strat; @@ -2421,7 +2423,7 @@ GetDefaultOpClass(Oid type_id, Oid am_id) * GetOperatorFromWellKnownStrategy * * opclass - the opclass to use - * atttype - the type to ask about + * rhstype - the type for the right-hand side * opid - holds the operator we found * strat - holds the input and output strategy number * @@ -2434,14 +2436,30 @@ GetDefaultOpClass(Oid type_id, Oid am_id) * InvalidStrategy. */ void -GetOperatorFromWellKnownStrategy(Oid opclass, Oid atttype, - Oid *opid, StrategyNumber *strat) +GetOperatorFromWellKnownStrategy(Oid opclass, + Oid rhstype, + Oid *opid, + StrategyNumber *strat) { Oid opfamily; Oid opcintype; StrategyNumber instrat = *strat; + char *errstr; - Assert(instrat == RTEqualStrategyNumber || instrat == RTOverlapStrategyNumber); + switch (instrat) + { + case RTEqualStrategyNumber: + errstr = "could not identify an equality operator for type %s"; + break; + case RTOverlapStrategyNumber: + errstr = "could not identify an overlaps operator for type %s"; + break; + case RTContainedByStrategyNumber: + errstr = "could not identify a contained by operator for type %s"; + break; + default: + elog(ERROR, "unsupported strategy number %u", instrat); + } *opid = InvalidOid; @@ -2464,16 +2482,20 @@ GetOperatorFromWellKnownStrategy(Oid opclass, Oid atttype, ereport(ERROR, errcode(ERRCODE_UNDEFINED_OBJECT), - instrat == RTEqualStrategyNumber ? - errmsg("could not identify an equality operator for type %s", format_type_be(atttype)) : - errmsg("could not identify an overlaps operator for type %s", format_type_be(atttype)), + errmsg(errstr, format_type_be(opcintype)), errdetail("Could not translate strategy number %d for operator class \"%s\" for access method \"%s\".", instrat, NameStr(((Form_pg_opclass) GETSTRUCT(tuple))->opcname), "gist")); - ReleaseSysCache(tuple); } - *opid = get_opfamily_member(opfamily, opcintype, opcintype, *strat); + /* + * We parameterize rhstype so foreign keys can ask for a <@ operator + * whose rhs matches the aggregate function. For example range_agg + * returns anymultirange. + */ + if (!OidIsValid(rhstype)) + rhstype = opcintype; + *opid = get_opfamily_member(opfamily, opcintype, rhstype, *strat); } if (!OidIsValid(*opid)) @@ -2486,9 +2508,7 @@ GetOperatorFromWellKnownStrategy(Oid opclass, Oid atttype, ereport(ERROR, errcode(ERRCODE_UNDEFINED_OBJECT), - instrat == RTEqualStrategyNumber ? - errmsg("could not identify an equality operator for type %s", format_type_be(atttype)) : - errmsg("could not identify an overlaps operator for type %s", format_type_be(atttype)), + errmsg(errstr, format_type_be(opcintype)), errdetail("There is no suitable operator in operator family \"%s\" for access method \"%s\".", NameStr(((Form_pg_opfamily) GETSTRUCT(tuple))->opfname), "gist")); } diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 02e5d8f8e38..457a40b66a0 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -16,6 +16,7 @@ #include "access/attmap.h" #include "access/genam.h" +#include "access/gist.h" #include "access/heapam.h" #include "access/heapam_xlog.h" #include "access/multixact.h" @@ -212,6 +213,7 @@ 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 */ ExprState *qualstate; /* Execution state for CHECK expr */ @@ -387,16 +389,16 @@ static int transformColumnNameList(Oid relId, List *colList, static int transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid, List **attnamelist, int16 *attnums, Oid *atttypids, - Oid *opclasses); + Oid *opclasses, bool *pk_period); static Oid transformFkeyCheckAttrs(Relation pkrel, int numattrs, int16 *attnums, - Oid *opclasses); + bool is_temporal, Oid *opclasses); static void checkFkeyPermissions(Relation rel, int16 *attnums, int natts); static CoercionPathType findFkeyCast(Oid targetTypeId, Oid sourceTypeId, Oid *funcid); static void validateForeignKeyConstraint(char *conname, Relation rel, Relation pkrel, - Oid pkindOid, Oid constraintOid); + Oid pkindOid, Oid constraintOid, bool temporal); static void ATController(AlterTableStmt *parsetree, Relation rel, List *cmds, bool recurse, LOCKMODE lockmode, AlterTableUtilityContext *context); @@ -509,7 +511,8 @@ static ObjectAddress addFkRecurseReferenced(List **wqueue, Constraint *fkconstra Oid *pfeqoperators, Oid *ppeqoperators, Oid *ffeqoperators, int numfkdelsetcols, int16 *fkdelsetcols, bool old_check_ok, - Oid parentDelTrigger, Oid parentUpdTrigger); + Oid parentDelTrigger, Oid parentUpdTrigger, + bool is_temporal); static void validateFkOnDeleteSetColumns(int numfks, const int16 *fkattnums, int numfksetcols, const int16 *fksetcolsattnums, List *fksetcols); @@ -519,7 +522,9 @@ static void addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Oid *pfeqoperators, Oid *ppeqoperators, Oid *ffeqoperators, int numfkdelsetcols, int16 *fkdelsetcols, bool old_check_ok, LOCKMODE lockmode, - Oid parentInsTrigger, Oid parentUpdTrigger); + Oid parentInsTrigger, Oid parentUpdTrigger, + bool is_temporal); + static void CloneForeignKeyConstraints(List **wqueue, Relation parentRel, Relation partitionRel); static void CloneFkReferenced(Relation parentRel, Relation partitionRel); @@ -558,6 +563,7 @@ static void FindFKComparisonOperators(Constraint *fkconstraint, bool *old_check_ok, ListCell **old_pfeqop_item, Oid pktype, Oid fktype, Oid opclass, + bool is_temporal, bool for_overlaps, Oid *pfeqopOut, Oid *ppeqopOut, Oid *ffeqopOut); static void ATExecDropConstraint(Relation rel, const char *constrName, DropBehavior behavior, bool recurse, @@ -5973,7 +5979,8 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode, validateForeignKeyConstraint(fkconstraint->conname, rel, refrel, con->refindid, - con->conid); + con->conid, + con->conwithperiod); /* * No need to mark the constraint row as validated, we did @@ -9823,6 +9830,8 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, Oid ppeqoperators[INDEX_MAX_KEYS] = {0}; Oid ffeqoperators[INDEX_MAX_KEYS] = {0}; int16 fkdelsetcols[INDEX_MAX_KEYS] = {0}; + bool is_temporal; + bool pk_with_period; int i; int numfks, numpks, @@ -9917,6 +9926,14 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, numfks = transformColumnNameList(RelationGetRelid(rel), fkconstraint->fk_attrs, fkattnum, fktypoid); + is_temporal = fkconstraint->fk_with_period || fkconstraint->pk_with_period; + if (is_temporal) + { + if (!fkconstraint->fk_with_period) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("foreign key uses PERIOD on the referenced table but not the referencing table"))); + } numfkdelsetcols = transformColumnNameList(RelationGetRelid(rel), fkconstraint->fk_del_set_cols, @@ -9936,16 +9953,32 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, numpks = transformFkeyGetPrimaryKey(pkrel, &indexOid, &fkconstraint->pk_attrs, pkattnum, pktypoid, - opclasses); + opclasses, &pk_with_period); + + /* If the primary key uses WITHOUT OVERLAPS, the fk must use PERIOD */ + if (pk_with_period && !fkconstraint->fk_with_period) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("foreign key uses PERIOD on the referenced table but not the referencing table"))); } else { numpks = transformColumnNameList(RelationGetRelid(pkrel), fkconstraint->pk_attrs, pkattnum, pktypoid); + + if (is_temporal) + { + if (!fkconstraint->pk_with_period) + /* Since we got pk_attrs, one should be a period. */ + ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("foreign key uses PERIOD on the referencing table but not the referenced table"))); + } + /* Look for an index matching the column list */ indexOid = transformFkeyCheckAttrs(pkrel, numpks, pkattnum, - opclasses); + is_temporal, opclasses); } /* @@ -10006,10 +10039,28 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, FindFKComparisonOperators(fkconstraint, tab, i, fkattnum, &old_check_ok, &old_pfeqop_item, pktypoid[i], fktypoid[i], opclasses[i], + is_temporal, is_temporal && i == numpks - 1, &pfeqoperators[i], &ppeqoperators[i], &ffeqoperators[i]); } + /* + * 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, even if we don't use the values. + */ + if (is_temporal) + { + Oid periodoperoid; + Oid aggedperiodoperoid; + Oid periodprocoid; + + FindFKPeriodOpersAndProcs(opclasses[numpks - 1], &periodoperoid, &aggedperiodoperoid, &periodprocoid); + } + /* * Create all the constraint and trigger objects, recursing to partitions * as necessary. First handle the referenced side. @@ -10026,7 +10077,8 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, numfkdelsetcols, fkdelsetcols, old_check_ok, - InvalidOid, InvalidOid); + InvalidOid, InvalidOid, + is_temporal); /* Now handle the referencing side. */ addFkRecurseReferencing(wqueue, fkconstraint, rel, pkrel, @@ -10042,7 +10094,8 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, fkdelsetcols, old_check_ok, lockmode, - InvalidOid, InvalidOid); + InvalidOid, InvalidOid, + is_temporal); /* * Done. Close pk table, but keep lock until we've committed. @@ -10127,7 +10180,8 @@ addFkRecurseReferenced(List **wqueue, Constraint *fkconstraint, Relation rel, Oid *ppeqoperators, Oid *ffeqoperators, int numfkdelsetcols, int16 *fkdelsetcols, bool old_check_ok, - Oid parentDelTrigger, Oid parentUpdTrigger) + Oid parentDelTrigger, Oid parentUpdTrigger, + bool is_temporal) { ObjectAddress address; Oid constrOid; @@ -10213,7 +10267,7 @@ addFkRecurseReferenced(List **wqueue, Constraint *fkconstraint, Relation rel, conislocal, /* islocal */ coninhcount, /* inhcount */ connoinherit, /* conNoInherit */ - false, /* conWithoutOverlaps */ + is_temporal, /* conWithoutOverlaps */ false); /* is_internal */ ObjectAddressSet(address, ConstraintRelationId, constrOid); @@ -10289,7 +10343,8 @@ addFkRecurseReferenced(List **wqueue, Constraint *fkconstraint, Relation rel, pfeqoperators, ppeqoperators, ffeqoperators, numfkdelsetcols, fkdelsetcols, old_check_ok, - deleteTriggerOid, updateTriggerOid); + deleteTriggerOid, updateTriggerOid, + is_temporal); /* Done -- clean up (but keep the lock) */ table_close(partRel, NoLock); @@ -10347,7 +10402,8 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, Oid *pfeqoperators, Oid *ppeqoperators, Oid *ffeqoperators, int numfkdelsetcols, int16 *fkdelsetcols, bool old_check_ok, LOCKMODE lockmode, - Oid parentInsTrigger, Oid parentUpdTrigger) + Oid parentInsTrigger, Oid parentUpdTrigger, + bool is_temporal) { Oid insertTriggerOid, updateTriggerOid; @@ -10395,6 +10451,7 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, newcon->refrelid = RelationGetRelid(pkrel); newcon->refindid = indexOid; newcon->conid = parentConstr; + newcon->conwithperiod = fkconstraint->fk_with_period; newcon->qual = (Node *) fkconstraint; tab->constraints = lappend(tab->constraints, newcon); @@ -10512,7 +10569,7 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, false, 1, false, - false, /* conWithoutOverlaps */ + is_temporal, /* conWithoutOverlaps */ false); /* @@ -10543,7 +10600,8 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, old_check_ok, lockmode, insertTriggerOid, - updateTriggerOid); + updateTriggerOid, + is_temporal); table_close(partition, NoLock); } @@ -10779,7 +10837,8 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel) confdelsetcols, true, deleteTriggerOid, - updateTriggerOid); + updateTriggerOid, + constrForm->conperiod); table_close(fkRel, NoLock); ReleaseSysCache(tuple); @@ -10872,6 +10931,7 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) ListCell *lc; Oid insertTriggerOid, updateTriggerOid; + bool is_temporal; tuple = SearchSysCache1(CONSTROID, ObjectIdGetDatum(parentConstrOid)); if (!HeapTupleIsValid(tuple)) @@ -10987,6 +11047,7 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) fkconstraint->conname = pstrdup(NameStr(constrForm->conname)); indexOid = constrForm->conindid; + is_temporal = constrForm->conperiod; constrOid = CreateConstraintEntry(fkconstraint->conname, constrForm->connamespace, @@ -11018,7 +11079,7 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) false, /* islocal */ 1, /* inhcount */ false, /* conNoInherit */ - false, /* conWithoutOverlaps */ + is_temporal, /* conWithoutOverlaps */ true); /* Set up partition dependencies for the new constraint */ @@ -11052,7 +11113,8 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) false, /* no old check exists */ AccessExclusiveLock, insertTriggerOid, - updateTriggerOid); + updateTriggerOid, + is_temporal); table_close(pkrel, NoLock); } @@ -11074,6 +11136,7 @@ FindFKComparisonOperators(Constraint *fkconstraint, bool *old_check_ok, ListCell **old_pfeqop_item, Oid pktype, Oid fktype, Oid opclass, + bool is_temporal, bool for_overlaps, Oid *pfeqopOut, Oid *ppeqopOut, Oid *ffeqopOut) { Oid fktyped; @@ -11085,8 +11148,10 @@ FindFKComparisonOperators(Constraint *fkconstraint, Oid pfeqop; Oid ppeqop; Oid ffeqop; + StrategyNumber rtstrategy; int16 eqstrategy; Oid pfeqop_right; + char *stratname; /* We need several fields out of the pg_opclass entry */ cla_ht = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclass)); @@ -11098,16 +11163,51 @@ FindFKComparisonOperators(Constraint *fkconstraint, opcintype = cla_tup->opcintype; ReleaseSysCache(cla_ht); - /* - * Check it's a btree; currently this can never fail since no other - * index AMs support unique indexes. If we ever did have other types - * of unique indexes, we'd need a way to determine which operator - * strategy number is equality. (Is it reasonable to insist that - * every such index AM use btree's number for equality?) - */ - if (amid != BTREE_AM_OID) - elog(ERROR, "only b-tree indexes are supported for foreign keys"); - eqstrategy = BTEqualStrategyNumber; + if (is_temporal) + { + /* + * GiST indexes are required to support temporal foreign keys + * because they combine equals and overlaps. + */ + if (amid != GIST_AM_OID) + elog(ERROR, "only GiST indexes are supported for temporal foreign keys"); + if (for_overlaps) + { + stratname = "overlaps"; + rtstrategy = RTOverlapStrategyNumber; + } + else + { + stratname = "equality"; + rtstrategy = RTEqualStrategyNumber; + } + /* + * An opclass can use whatever strategy numbers it wants, so we ask + * the opclass what number it actually uses instead of our + * RT* constants. + */ + eqstrategy = GistTranslateStratnum(opclass, rtstrategy); + if (eqstrategy == InvalidStrategy) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("no %s operator found for foreign key", stratname), + errdetail("Could not translate strategy number %d for opclass %d.", + rtstrategy, opclass), + errhint("Define a stratnum support function for your GiST opclass."))); + } + else + { + /* + * Check it's a btree; currently this can never fail since no other + * index AMs support unique indexes. If we ever did have other types + * of unique indexes, we'd need a way to determine which operator + * strategy number is equality. (Is it reasonable to insist that + * every such index AM use btree's number for equality?) + */ + if (amid != BTREE_AM_OID) + elog(ERROR, "only b-tree indexes are supported for foreign keys"); + eqstrategy = BTEqualStrategyNumber; + } /* * There had better be a primary equality operator for the index. @@ -11763,7 +11863,11 @@ ATExecAlterConstrRecurse(Constraint *cmdcon, Relation conrel, Relation tgrel, if (tgform->tgfoid != F_RI_FKEY_NOACTION_DEL && tgform->tgfoid != F_RI_FKEY_NOACTION_UPD && tgform->tgfoid != F_RI_FKEY_CHECK_INS && - tgform->tgfoid != F_RI_FKEY_CHECK_UPD) + tgform->tgfoid != F_RI_FKEY_CHECK_UPD && + tgform->tgfoid != F_RI_FKEY_PERIOD_NOACTION_DEL && + tgform->tgfoid != F_RI_FKEY_PERIOD_NOACTION_UPD && + tgform->tgfoid != F_RI_FKEY_PERIOD_CHECK_INS && + tgform->tgfoid != F_RI_FKEY_PERIOD_CHECK_UPD) continue; tgCopyTuple = heap_copytuple(tgtuple); @@ -12077,7 +12181,7 @@ static int transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid, List **attnamelist, int16 *attnums, Oid *atttypids, - Oid *opclasses) + Oid *opclasses, bool *pk_period) { List *indexoidlist; ListCell *indexoidscan; @@ -12141,7 +12245,8 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid, /* * Now build the list of PK attributes from the indkey definition (we - * assume a primary key cannot have expressional elements) + * assume a primary key cannot have expressional elements, unless it + * has a PERIOD) */ *attnamelist = NIL; for (i = 0; i < indexStruct->indnkeyatts; i++) @@ -12155,6 +12260,8 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid, makeString(pstrdup(NameStr(*attnumAttName(pkrel, pkattno))))); } + *pk_period = (indexStruct->indisexclusion); + ReleaseSysCache(indexTuple); return i; @@ -12175,7 +12282,7 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid, static Oid transformFkeyCheckAttrs(Relation pkrel, int numattrs, int16 *attnums, - Oid *opclasses) + bool is_temporal, Oid *opclasses) { Oid indexoid = InvalidOid; bool found = false; @@ -12222,12 +12329,13 @@ transformFkeyCheckAttrs(Relation pkrel, indexStruct = (Form_pg_index) GETSTRUCT(indexTuple); /* - * Must have the right number of columns; must be unique and not a + * Must have the right number of columns; must be unique + * (or if temporal then exclusion instead) and not a * partial index; forget it if there are any expressions, too. Invalid * indexes are out as well. */ if (indexStruct->indnkeyatts == numattrs && - indexStruct->indisunique && + (is_temporal ? indexStruct->indisexclusion : indexStruct->indisunique) && indexStruct->indisvalid && heap_attisnull(indexTuple, Anum_pg_index_indpred, NULL) && heap_attisnull(indexTuple, Anum_pg_index_indexprs, NULL)) @@ -12265,6 +12373,13 @@ transformFkeyCheckAttrs(Relation pkrel, if (!found) break; } + /* The last attribute in the index must be the PERIOD FK part */ + if (found && is_temporal) + { + int16 periodattnum = attnums[numattrs - 1]; + + found = (periodattnum == indexStruct->indkey.values[numattrs - 1]); + } /* * Refuse to use a deferrable unique/primary key. This is per SQL @@ -12374,7 +12489,8 @@ validateForeignKeyConstraint(char *conname, Relation rel, Relation pkrel, Oid pkindOid, - Oid constraintOid) + Oid constraintOid, + bool temporal) { TupleTableSlot *slot; TableScanDesc scan; @@ -12403,8 +12519,10 @@ validateForeignKeyConstraint(char *conname, /* * See if we can do it with a single LEFT JOIN query. A false result * indicates we must proceed with the fire-the-trigger method. + * We can't do a LEFT JOIN for temporal FKs yet, + * but we can once we support temporal left joins. */ - if (RI_Initial_Check(&trig, rel, pkrel)) + if (!temporal && RI_Initial_Check(&trig, rel, pkrel)) return; /* @@ -12473,6 +12591,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint, { ObjectAddress trigAddress; CreateTrigStmt *fk_trigger; + bool is_temporal = fkconstraint->fk_with_period; /* * Note: for a self-referential FK (referencing and referenced tables are @@ -12492,12 +12611,18 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint, /* Either ON INSERT or ON UPDATE */ if (on_insert) { - fk_trigger->funcname = SystemFuncName("RI_FKey_check_ins"); + if (is_temporal) + fk_trigger->funcname = SystemFuncName("RI_FKey_period_check_ins"); + else + fk_trigger->funcname = SystemFuncName("RI_FKey_check_ins"); fk_trigger->events = TRIGGER_TYPE_INSERT; } else { - fk_trigger->funcname = SystemFuncName("RI_FKey_check_upd"); + if (is_temporal) + fk_trigger->funcname = SystemFuncName("RI_FKey_period_check_upd"); + else + fk_trigger->funcname = SystemFuncName("RI_FKey_check_upd"); fk_trigger->events = TRIGGER_TYPE_UPDATE; } @@ -12555,37 +12680,69 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr fk_trigger->whenClause = NULL; fk_trigger->transitionRels = NIL; fk_trigger->constrrel = NULL; - switch (fkconstraint->fk_del_action) + if (fkconstraint->fk_with_period) { - case FKCONSTR_ACTION_NOACTION: - fk_trigger->deferrable = fkconstraint->deferrable; - fk_trigger->initdeferred = fkconstraint->initdeferred; - fk_trigger->funcname = SystemFuncName("RI_FKey_noaction_del"); - break; - case FKCONSTR_ACTION_RESTRICT: - fk_trigger->deferrable = false; - fk_trigger->initdeferred = false; - fk_trigger->funcname = SystemFuncName("RI_FKey_restrict_del"); - break; - case FKCONSTR_ACTION_CASCADE: - fk_trigger->deferrable = false; - fk_trigger->initdeferred = false; - fk_trigger->funcname = SystemFuncName("RI_FKey_cascade_del"); - break; - case FKCONSTR_ACTION_SETNULL: - fk_trigger->deferrable = false; - fk_trigger->initdeferred = false; - fk_trigger->funcname = SystemFuncName("RI_FKey_setnull_del"); - break; - case FKCONSTR_ACTION_SETDEFAULT: - fk_trigger->deferrable = false; - fk_trigger->initdeferred = false; - fk_trigger->funcname = SystemFuncName("RI_FKey_setdefault_del"); - break; - default: - elog(ERROR, "unrecognized FK action type: %d", - (int) fkconstraint->fk_del_action); - break; + /* Temporal foreign keys */ + switch (fkconstraint->fk_del_action) + { + case FKCONSTR_ACTION_NOACTION: + fk_trigger->deferrable = fkconstraint->deferrable; + fk_trigger->initdeferred = fkconstraint->initdeferred; + fk_trigger->funcname = SystemFuncName("RI_FKey_period_noaction_del"); + break; + case FKCONSTR_ACTION_RESTRICT: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("RI_FKey_period_restrict_del"); + break; + case FKCONSTR_ACTION_CASCADE: + case FKCONSTR_ACTION_SETNULL: + case FKCONSTR_ACTION_SETDEFAULT: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("invalid %s action for foreign key constraint using PERIOD", + "ON DELETE"))); + break; + default: + elog(ERROR, "unrecognized FK action type: %d", + (int) fkconstraint->fk_del_action); + break; + } + } + else + { + switch (fkconstraint->fk_del_action) + { + case FKCONSTR_ACTION_NOACTION: + fk_trigger->deferrable = fkconstraint->deferrable; + fk_trigger->initdeferred = fkconstraint->initdeferred; + fk_trigger->funcname = SystemFuncName("RI_FKey_noaction_del"); + break; + case FKCONSTR_ACTION_RESTRICT: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("RI_FKey_restrict_del"); + break; + case FKCONSTR_ACTION_CASCADE: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("RI_FKey_cascade_del"); + break; + case FKCONSTR_ACTION_SETNULL: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("RI_FKey_setnull_del"); + break; + case FKCONSTR_ACTION_SETDEFAULT: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("RI_FKey_setdefault_del"); + break; + default: + elog(ERROR, "unrecognized FK action type: %d", + (int) fkconstraint->fk_del_action); + break; + } } trigAddress = CreateTrigger(fk_trigger, NULL, refRelOid, @@ -12615,37 +12772,69 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr fk_trigger->whenClause = NULL; fk_trigger->transitionRels = NIL; fk_trigger->constrrel = NULL; - switch (fkconstraint->fk_upd_action) + if (fkconstraint->fk_with_period) { - case FKCONSTR_ACTION_NOACTION: - fk_trigger->deferrable = fkconstraint->deferrable; - fk_trigger->initdeferred = fkconstraint->initdeferred; - fk_trigger->funcname = SystemFuncName("RI_FKey_noaction_upd"); - break; - case FKCONSTR_ACTION_RESTRICT: - fk_trigger->deferrable = false; - fk_trigger->initdeferred = false; - fk_trigger->funcname = SystemFuncName("RI_FKey_restrict_upd"); - break; - case FKCONSTR_ACTION_CASCADE: - fk_trigger->deferrable = false; - fk_trigger->initdeferred = false; - fk_trigger->funcname = SystemFuncName("RI_FKey_cascade_upd"); - break; - case FKCONSTR_ACTION_SETNULL: - fk_trigger->deferrable = false; - fk_trigger->initdeferred = false; - fk_trigger->funcname = SystemFuncName("RI_FKey_setnull_upd"); - break; - case FKCONSTR_ACTION_SETDEFAULT: - fk_trigger->deferrable = false; - fk_trigger->initdeferred = false; - fk_trigger->funcname = SystemFuncName("RI_FKey_setdefault_upd"); - break; - default: - elog(ERROR, "unrecognized FK action type: %d", - (int) fkconstraint->fk_upd_action); - break; + /* Temporal foreign keys */ + switch (fkconstraint->fk_upd_action) + { + case FKCONSTR_ACTION_NOACTION: + fk_trigger->deferrable = fkconstraint->deferrable; + fk_trigger->initdeferred = fkconstraint->initdeferred; + fk_trigger->funcname = SystemFuncName("RI_FKey_period_noaction_upd"); + break; + case FKCONSTR_ACTION_RESTRICT: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("RI_FKey_period_restrict_upd"); + break; + case FKCONSTR_ACTION_CASCADE: + case FKCONSTR_ACTION_SETNULL: + case FKCONSTR_ACTION_SETDEFAULT: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("invalid %s action for foreign key constraint using PERIOD", + "ON UPDATE"))); + break; + default: + elog(ERROR, "unrecognized FK action type: %d", + (int) fkconstraint->fk_upd_action); + break; + } + } + else + { + switch (fkconstraint->fk_upd_action) + { + case FKCONSTR_ACTION_NOACTION: + fk_trigger->deferrable = fkconstraint->deferrable; + fk_trigger->initdeferred = fkconstraint->initdeferred; + fk_trigger->funcname = SystemFuncName("RI_FKey_noaction_upd"); + break; + case FKCONSTR_ACTION_RESTRICT: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("RI_FKey_restrict_upd"); + break; + case FKCONSTR_ACTION_CASCADE: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("RI_FKey_cascade_upd"); + break; + case FKCONSTR_ACTION_SETNULL: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("RI_FKey_setnull_upd"); + break; + case FKCONSTR_ACTION_SETDEFAULT: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("RI_FKey_setdefault_upd"); + break; + default: + elog(ERROR, "unrecognized FK action type: %d", + (int) fkconstraint->fk_upd_action); + break; + } } trigAddress = CreateTrigger(fk_trigger, NULL, refRelOid, diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 130f7fc7c3f..a9366733f20 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -522,12 +522,13 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); SetResetClause FunctionSetResetClause %type TableElement TypedTableElement ConstraintElem TableFuncElement -%type columnDef columnOptions +%type columnDef columnOptions optionalPeriodName %type def_elem reloption_elem old_aggr_elem operator_def_elem %type def_arg columnElem where_clause where_or_current_clause a_expr b_expr c_expr AexprConst indirection_el opt_slice_bound columnref in_expr having_clause func_table xmltable array_expr OptWhereClause operator_def_arg +%type opt_column_and_period_list %type rowsfrom_item rowsfrom_list opt_col_def_list %type opt_ordinality opt_without_overlaps %type ExclusionConstraintList ExclusionConstraintElem @@ -745,7 +746,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD - PLACING PLANS POLICY + PERIOD PLACING PLANS POLICY POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION @@ -4222,21 +4223,31 @@ ConstraintElem: NULL, yyscanner); $$ = (Node *) n; } - | FOREIGN KEY '(' columnList ')' REFERENCES qualified_name - opt_column_list key_match key_actions ConstraintAttributeSpec + | FOREIGN KEY '(' columnList optionalPeriodName ')' REFERENCES qualified_name + opt_column_and_period_list key_match key_actions ConstraintAttributeSpec { Constraint *n = makeNode(Constraint); n->contype = CONSTR_FOREIGN; n->location = @1; - n->pktable = $7; + n->pktable = $8; n->fk_attrs = $4; - n->pk_attrs = $8; - n->fk_matchtype = $9; - n->fk_upd_action = ($10)->updateAction->action; - n->fk_del_action = ($10)->deleteAction->action; - n->fk_del_set_cols = ($10)->deleteAction->cols; - processCASbits($11, @11, "FOREIGN KEY", + if ($5) + { + n->fk_attrs = lappend(n->fk_attrs, $5); + n->fk_with_period = true; + } + n->pk_attrs = linitial($9); + if (lsecond($9)) + { + n->pk_attrs = lappend(n->pk_attrs, lsecond($9)); + n->pk_with_period = true; + } + n->fk_matchtype = $10; + n->fk_upd_action = ($11)->updateAction->action; + n->fk_del_action = ($11)->deleteAction->action; + n->fk_del_set_cols = ($11)->deleteAction->cols; + processCASbits($12, @12, "FOREIGN KEY", &n->deferrable, &n->initdeferred, &n->skip_validation, NULL, yyscanner); @@ -4264,6 +4275,16 @@ columnList: | columnList ',' columnElem { $$ = lappend($1, $3); } ; +optionalPeriodName: + ',' PERIOD columnElem { $$ = $3; } + | /*EMPTY*/ { $$ = NULL; } + ; + +opt_column_and_period_list: + '(' columnList optionalPeriodName ')' { $$ = list_make2($2, $3); } + | /*EMPTY*/ { $$ = list_make2(NIL, NULL); } + ; + columnElem: ColId { $$ = (Node *) makeString($1); @@ -17618,6 +17639,7 @@ reserved_keyword: | ONLY | OR | ORDER + | PERIOD | PLACING | PRIMARY | REFERENCES @@ -17927,6 +17949,7 @@ bare_label_keyword: | PARTITION | PASSING | PASSWORD + | PERIOD | PLACING | PLANS | POLICY diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c index 708d9c8efc9..f5c65712b4b 100644 --- a/src/backend/utils/adt/ri_triggers.c +++ b/src/backend/utils/adt/ri_triggers.c @@ -31,6 +31,7 @@ #include "catalog/pg_collation.h" #include "catalog/pg_constraint.h" #include "catalog/pg_operator.h" +#include "catalog/pg_proc.h" #include "catalog/pg_type.h" #include "commands/trigger.h" #include "executor/executor.h" @@ -48,6 +49,7 @@ #include "utils/inval.h" #include "utils/lsyscache.h" #include "utils/memutils.h" +#include "utils/rangetypes.h" #include "utils/rel.h" #include "utils/rls.h" #include "utils/ruleutils.h" @@ -118,12 +120,17 @@ typedef struct RI_ConstraintInfo int16 confdelsetcols[RI_MAX_NUMKEYS]; /* attnums of cols to set on * delete */ char confmatchtype; /* foreign key's match type */ + bool temporal; /* if the foreign key is temporal */ int nkeys; /* number of key columns */ int16 pk_attnums[RI_MAX_NUMKEYS]; /* attnums of referenced cols */ int16 fk_attnums[RI_MAX_NUMKEYS]; /* attnums of referencing cols */ 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; /* operator for PERIOD SQL */ + Oid agged_period_contained_by_oper; /* operator for PERIOD SQL */ + Oid period_referenced_agg_proc; /* proc for PERIOD SQL */ + Oid period_referenced_agg_rettype; /* rettype for previous */ dlist_node valid_link; /* Link in list of valid entries */ } RI_ConstraintInfo; @@ -231,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); /* @@ -365,14 +373,41 @@ RI_FKey_check(TriggerData *trigdata) * FOR KEY SHARE OF x * The type id's for the $ parameters are those of the * corresponding FK attributes. + * + * But for temporal FKs we need to make sure + * the FK's range is completely covered. + * So we use this query instead: + * SELECT 1 + * FROM ( + * SELECT pkperiodatt AS r + * FROM [ONLY] pktable x + * WHERE pkatt1 = $1 [AND ...] + * AND pkperiodatt && $n + * FOR KEY SHARE OF x + * ) x1 + * HAVING $n <@ range_agg(x1.r) + * Note if FOR KEY SHARE ever allows GROUP BY and HAVING + * we can make this a bit simpler. * ---------- */ initStringInfo(&querybuf); pk_only = pk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ? "" : "ONLY "; quoteRelationName(pkrelname, pk_rel); - appendStringInfo(&querybuf, "SELECT 1 FROM %s%s x", - pk_only, pkrelname); + if (riinfo->temporal) + { + quoteOneName(attname, + RIAttName(pk_rel, riinfo->pk_attnums[riinfo->nkeys - 1])); + + appendStringInfo(&querybuf, + "SELECT 1 FROM (SELECT %s AS r FROM %s%s x", + attname, pk_only, pkrelname); + } + else + { + appendStringInfo(&querybuf, "SELECT 1 FROM %s%s x", + pk_only, pkrelname); + } querysep = "WHERE"; for (int i = 0; i < riinfo->nkeys; i++) { @@ -390,6 +425,21 @@ RI_FKey_check(TriggerData *trigdata) queryoids[i] = fk_type; } appendStringInfoString(&querybuf, " FOR KEY SHARE OF x"); + if (riinfo->temporal) + { + char *aggname; + Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1]); + 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, + aggname, agg_rettype); + appendStringInfo(&querybuf, "(x1.r)"); + } /* Prepare and save the plan */ qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids, @@ -497,14 +547,40 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel, * FOR KEY SHARE OF x * The type id's for the $ parameters are those of the * PK attributes themselves. + * But for temporal FKs we need to make sure + * the FK's range is completely covered. + * So we use this query instead: + * SELECT 1 + * FROM ( + * SELECT pkperiodatt AS r + * FROM [ONLY] pktable x + * WHERE pkatt1 = $1 [AND ...] + * AND pkperiodatt && $n + * FOR KEY SHARE OF x + * ) x1 + * HAVING $n <@ range_agg(x1.r) + * Note if FOR KEY SHARE ever allows GROUP BY and HAVING + * we can make this a bit simpler. * ---------- */ initStringInfo(&querybuf); pk_only = pk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ? "" : "ONLY "; quoteRelationName(pkrelname, pk_rel); - appendStringInfo(&querybuf, "SELECT 1 FROM %s%s x", - pk_only, pkrelname); + if (riinfo->temporal) + { + quoteOneName(attname, RIAttName(pk_rel, riinfo->pk_attnums[riinfo->nkeys - 1])); + + appendStringInfo(&querybuf, + "SELECT 1 FROM (SELECT %s AS r FROM %s%s x", + attname, pk_only, pkrelname); + } + else + { + appendStringInfo(&querybuf, "SELECT 1 FROM %s%s x", + pk_only, pkrelname); + } + querysep = "WHERE"; for (int i = 0; i < riinfo->nkeys; i++) { @@ -521,6 +597,21 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel, queryoids[i] = pk_type; } appendStringInfoString(&querybuf, " FOR KEY SHARE OF x"); + if (riinfo->temporal) + { + char *aggname; + Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1]); + 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, + aggname, agg_rettype); + appendStringInfo(&querybuf, "(x1.r)"); + } /* Prepare and save the plan */ qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids, @@ -1213,6 +1304,126 @@ ri_set(TriggerData *trigdata, bool is_set_null, int tgkind) } } +/* ---------- + * RI_FKey_period_check_ins - + * + * Check temporal foreign key existence at insert event on FK table. + * ---------- + */ +Datum +RI_FKey_period_check_ins(PG_FUNCTION_ARGS) +{ + /* + * Check that this is a valid trigger call on the right time and event. + */ + ri_CheckTrigger(fcinfo, "RI_FKey_check_ins", RI_TRIGTYPE_INSERT); + + /* + * Share code with UPDATE case. + */ + return RI_FKey_check((TriggerData *) fcinfo->context); +} + + +/* ---------- + * RI_FKey_period_check_upd - + * + * Check temporal foreign key existence at update event on FK table. + * ---------- + */ +Datum +RI_FKey_period_check_upd(PG_FUNCTION_ARGS) +{ + /* + * Check that this is a valid trigger call on the right time and event. + */ + ri_CheckTrigger(fcinfo, "RI_FKey_check_upd", RI_TRIGTYPE_UPDATE); + + /* + * Share code with INSERT case. + */ + return RI_FKey_check((TriggerData *) fcinfo->context); +} + + +/* ---------- + * RI_FKey_period_noaction_del - + * + * Give an error and roll back the current transaction if the + * delete has resulted in a violation of the given temporal + * referential integrity constraint. + * ---------- + */ +Datum +RI_FKey_period_noaction_del(PG_FUNCTION_ARGS) +{ + /* + * Check that this is a valid trigger call on the right time and event. + */ + ri_CheckTrigger(fcinfo, "RI_FKey_period_noaction_del", RI_TRIGTYPE_DELETE); + + /* + * Share code with RESTRICT/UPDATE cases. + */ + return ri_restrict((TriggerData *) fcinfo->context, true); +} + +/* + * RI_FKey_period_restrict_del - + * + * Restrict delete from PK table to rows unreferenced by foreign key. + * + * The SQL standard intends that this referential action occur exactly when + * the delete is performed, rather than after. This appears to be + * the only difference between "NO ACTION" and "RESTRICT". In Postgres + * we still implement this as an AFTER trigger, but it's non-deferrable. + */ +Datum +RI_FKey_period_restrict_del(PG_FUNCTION_ARGS) +{ + /* Check that this is a valid trigger call on the right time and event. */ + ri_CheckTrigger(fcinfo, "RI_FKey_period_restrict_del", RI_TRIGTYPE_DELETE); + + /* Share code with NO ACTION/UPDATE cases. */ + return ri_restrict((TriggerData *) fcinfo->context, false); +} + +/* + * TRI_FKey_noaction_upd - + * + * Give an error and roll back the current transaction if the + * update has resulted in a violation of the given referential + * integrity constraint. + */ +Datum +RI_FKey_period_noaction_upd(PG_FUNCTION_ARGS) +{ + /* Check that this is a valid trigger call on the right time and event. */ + ri_CheckTrigger(fcinfo, "RI_FKey_period_noaction_upd", RI_TRIGTYPE_UPDATE); + + /* Share code with RESTRICT/DELETE cases. */ + return ri_restrict((TriggerData *) fcinfo->context, true); +} + +/* + * RI_FKey_period_restrict_upd - + * + * Restrict update of PK to rows unreferenced by foreign key. + * + * The SQL standard intends that this referential action occur exactly when + * the update is performed, rather than after. This appears to be + * the only difference between "NO ACTION" and "RESTRICT". In Postgres + * we still implement this as an AFTER trigger, but it's non-deferrable. + */ +Datum +RI_FKey_period_restrict_upd(PG_FUNCTION_ARGS) +{ + /* Check that this is a valid trigger call on the right time and event. */ + ri_CheckTrigger(fcinfo, "RI_FKey_period_restrict_upd", RI_TRIGTYPE_UPDATE); + + /* Share code with NO ACTION/DELETE cases. */ + return ri_restrict((TriggerData *) fcinfo->context, false); +} /* * RI_FKey_pk_upd_check_required - @@ -2171,6 +2382,7 @@ ri_LoadConstraintInfo(Oid constraintOid) riinfo->confupdtype = conForm->confupdtype; riinfo->confdeltype = conForm->confdeltype; riinfo->confmatchtype = conForm->confmatchtype; + riinfo->temporal = conForm->conperiod; DeconstructFkConstraintRow(tup, &riinfo->nkeys, @@ -2182,6 +2394,22 @@ ri_LoadConstraintInfo(Oid constraintOid) &riinfo->ndelsetcols, riinfo->confdelsetcols); + /* + * For temporal FKs, get the operators and functions we need. + * We ask the opclass of the PK element for these. + * This all gets cached (as does the generated plan), + * so there's no performance issue. + */ + if (riinfo->temporal) + { + Oid opclass = get_index_column_opclass(conForm->conindid, riinfo->nkeys); + 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); /* @@ -2793,7 +3021,10 @@ ri_HashPreparedPlan(RI_QueryKey *key, SPIPlanPtr plan) /* * ri_KeysEqual - * - * Check if all key values in OLD and NEW are equal. + * 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 of its old value. * * Note: at some point we might wish to redefine this as checking for * "IS NOT DISTINCT" rather than "=", that is, allow two nulls to be @@ -2849,13 +3080,23 @@ ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot, } else { + Oid eq_opr = riinfo->ff_eq_oprs[i]; + + /* + * When comparing the PERIOD columns we can skip the check + * whenever the referencing column stayed equal or shrank, + * so test with the contained-by operator instead. + */ + if (riinfo->temporal && i == riinfo->nkeys - 1) + eq_opr = riinfo->period_contained_by_oper; + /* * For the FK table, compare with the appropriate equality * operator. Changes that compare equal will still satisfy the * constraint after the update. */ - if (!ri_AttributesEqual(riinfo->ff_eq_oprs[i], RIAttType(rel, attnums[i]), - oldvalue, newvalue)) + if (!ri_AttributesEqual(eq_opr, RIAttType(rel, attnums[i]), + newvalue, oldvalue)) return false; } } @@ -2863,7 +3104,6 @@ ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot, return true; } - /* * ri_AttributesEqual - * @@ -3021,12 +3261,56 @@ RI_FKey_trigger_type(Oid tgfoid) case F_RI_FKEY_SETDEFAULT_UPD: case F_RI_FKEY_NOACTION_DEL: case F_RI_FKEY_NOACTION_UPD: + case F_RI_FKEY_PERIOD_RESTRICT_DEL: + case F_RI_FKEY_PERIOD_RESTRICT_UPD: + case F_RI_FKEY_PERIOD_NOACTION_DEL: + case F_RI_FKEY_PERIOD_NOACTION_UPD: return RI_TRIGGER_PK; case F_RI_FKEY_CHECK_INS: case F_RI_FKEY_CHECK_UPD: + case F_RI_FKEY_PERIOD_CHECK_INS: + case F_RI_FKEY_PERIOD_CHECK_UPD: return RI_TRIGGER_FK; } 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/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 5727dfc4031..f7a44c7b330 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -339,7 +339,7 @@ static char *pg_get_viewdef_worker(Oid viewoid, int prettyFlags, int wrapColumn); static char *pg_get_triggerdef_worker(Oid trigid, bool pretty); static int decompile_column_index_array(Datum column_index_array, Oid relId, - StringInfo buf); + bool withPeriod, StringInfo buf); static char *pg_get_ruledef_worker(Oid ruleoid, int prettyFlags); static char *pg_get_indexdef_worker(Oid indexrelid, int colno, const Oid *excludeOps, @@ -2246,7 +2246,9 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, val = SysCacheGetAttrNotNull(CONSTROID, tup, Anum_pg_constraint_conkey); - decompile_column_index_array(val, conForm->conrelid, &buf); + /* If it is a temporal foreign key then it uses PERIOD. */ + decompile_column_index_array(val, conForm->conrelid, + conForm->conperiod, &buf); /* add foreign relation name */ appendStringInfo(&buf, ") REFERENCES %s(", @@ -2257,7 +2259,8 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, val = SysCacheGetAttrNotNull(CONSTROID, tup, Anum_pg_constraint_confkey); - decompile_column_index_array(val, conForm->confrelid, &buf); + decompile_column_index_array(val, conForm->confrelid, + conForm->conperiod, &buf); appendStringInfoChar(&buf, ')'); @@ -2343,7 +2346,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, if (!isnull) { appendStringInfoString(&buf, " ("); - decompile_column_index_array(val, conForm->conrelid, &buf); + decompile_column_index_array(val, conForm->conrelid, false, &buf); appendStringInfoChar(&buf, ')'); } @@ -2378,7 +2381,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, val = SysCacheGetAttrNotNull(CONSTROID, tup, Anum_pg_constraint_conkey); - keyatts = decompile_column_index_array(val, conForm->conrelid, &buf); + keyatts = decompile_column_index_array(val, conForm->conrelid, false, &buf); if (conForm->conperiod) appendStringInfoString(&buf, " WITHOUT OVERLAPS"); @@ -2576,7 +2579,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, */ static int decompile_column_index_array(Datum column_index_array, Oid relId, - StringInfo buf) + bool withPeriod, StringInfo buf) { Datum *keys; int nKeys; @@ -2595,7 +2598,9 @@ decompile_column_index_array(Datum column_index_array, Oid relId, if (j == 0) appendStringInfoString(buf, quote_identifier(colName)); else - appendStringInfo(buf, ", %s", quote_identifier(colName)); + appendStringInfo(buf, ", %s%s", + (withPeriod && j == nKeys - 1) ? "PERIOD " : "", + quote_identifier(colName)); } return nKeys; diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h index a33b4f17ea8..e4f4c2ffaf1 100644 --- a/src/include/catalog/pg_constraint.h +++ b/src/include/catalog/pg_constraint.h @@ -108,8 +108,8 @@ CATALOG(pg_constraint,2606,ConstraintRelationId) bool connoinherit; /* - * For primary keys and unique constraints, signifies the last column uses - * overlaps instead of equals. + * For primary keys, unique constraints, and foreign keys, + * signifies the last column uses overlaps instead of equals. */ bool conperiod; @@ -127,20 +127,20 @@ CATALOG(pg_constraint,2606,ConstraintRelationId) int16 confkey[1]; /* - * If a foreign key, the OIDs of the PK = FK equality operators for each - * column of the constraint + * If a foreign key, the OIDs of the PK = FK equality/overlap operators for + * each column of the constraint */ Oid conpfeqop[1] BKI_LOOKUP(pg_operator); /* - * If a foreign key, the OIDs of the PK = PK equality operators for each - * column of the constraint (i.e., equality for the referenced columns) + * If a foreign key, the OIDs of the PK = PK equality/overlap operators for + * each column of the constraint (i.e., equality for the referenced columns) */ Oid conppeqop[1] BKI_LOOKUP(pg_operator); /* - * If a foreign key, the OIDs of the FK = FK equality operators for each - * column of the constraint (i.e., equality for the referencing columns) + * If a foreign key, the OIDs of the FK = FK equality/overlap operators for + * each column of the constraint (i.e., equality for the referencing columns) */ Oid conffeqop[1] BKI_LOOKUP(pg_operator); @@ -283,6 +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 FindFKPeriodOpersAndProcs(Oid opclass, + Oid *periodoperoid, + Oid *aggedperiodoperoid, + Oid *periodprocoid); extern bool check_functional_grouping(Oid relid, Index varno, Index varlevelsup, diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 9c120fc2b7f..a8367ff5843 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -3945,6 +3945,30 @@ prorettype => 'trigger', proargtypes => '', prosrc => 'RI_FKey_noaction_upd' }, +# Temporal referential integrity constraint triggers +{ oid => '6122', descr => 'temporal referential integrity FOREIGN KEY ... REFERENCES', + proname => 'RI_FKey_period_check_ins', provolatile => 'v', prorettype => 'trigger', + proargtypes => '', prosrc => 'RI_FKey_period_check_ins' }, +{ oid => '6123', descr => 'temporal referential integrity FOREIGN KEY ... REFERENCES', + proname => 'RI_FKey_period_check_upd', provolatile => 'v', prorettype => 'trigger', + proargtypes => '', prosrc => 'RI_FKey_period_check_upd' }, +{ oid => '6126', descr => 'temporal referential integrity ON DELETE RESTRICT', + proname => 'RI_FKey_period_restrict_del', provolatile => 'v', + prorettype => 'trigger', proargtypes => '', + prosrc => 'RI_FKey_period_restrict_del' }, +{ oid => '6127', descr => 'temporal referential integrity ON UPDATE RESTRICT', + proname => 'RI_FKey_period_restrict_upd', provolatile => 'v', + prorettype => 'trigger', proargtypes => '', + prosrc => 'RI_FKey_period_restrict_upd' }, +{ oid => '6132', descr => 'temporal referential integrity ON DELETE NO ACTION', + proname => 'RI_FKey_period_noaction_del', provolatile => 'v', + prorettype => 'trigger', proargtypes => '', + prosrc => 'RI_FKey_period_noaction_del' }, +{ oid => '6133', descr => 'temporal referential integrity ON UPDATE NO ACTION', + proname => 'RI_FKey_period_noaction_upd', provolatile => 'v', + prorettype => 'trigger', proargtypes => '', + prosrc => 'RI_FKey_period_noaction_upd' }, + { oid => '1666', proname => 'varbiteq', proleakproof => 't', prorettype => 'bool', proargtypes => 'varbit varbit', prosrc => 'biteq' }, diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h index 0c53d67d3ee..5fd095ea177 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -50,7 +50,7 @@ extern bool CheckIndexCompatible(Oid oldId, extern Oid GetDefaultOpClass(Oid type_id, Oid am_id); extern Oid ResolveOpClass(const List *opclass, Oid attrType, const char *accessMethodName, Oid accessMethodId); -extern void GetOperatorFromWellKnownStrategy(Oid opclass, Oid atttype, +extern void GetOperatorFromWellKnownStrategy(Oid opclass, Oid rhstype, Oid *opid, StrategyNumber *strat); /* commands/functioncmds.c */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index d60e148ff26..94f5c221f64 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2609,6 +2609,8 @@ typedef struct Constraint RangeVar *pktable; /* Primary key table */ List *fk_attrs; /* Attributes of foreign key */ List *pk_attrs; /* Corresponding attrs in PK table */ + bool fk_with_period; /* Last attribute of FK uses PERIOD */ + bool pk_with_period; /* Last attribute of PK uses PERIOD */ char fk_matchtype; /* FULL, PARTIAL, SIMPLE */ char fk_upd_action; /* ON UPDATE action */ char fk_del_action; /* ON DELETE action */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 2331acac091..1d3f7bd37f2 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -326,6 +326,7 @@ PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("period", PERIOD, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD, BARE_LABEL) diff --git a/src/test/regress/expected/btree_index.out b/src/test/regress/expected/btree_index.out index 8311a03c3df..f3595d4ea3b 100644 --- a/src/test/regress/expected/btree_index.out +++ b/src/test/regress/expected/btree_index.out @@ -206,14 +206,16 @@ select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1; (3 rows) select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1; - proname ------------------------- + proname +----------------------------- RI_FKey_cascade_del RI_FKey_noaction_del + RI_FKey_period_noaction_del + RI_FKey_period_restrict_del RI_FKey_restrict_del RI_FKey_setdefault_del RI_FKey_setnull_del -(5 rows) +(7 rows) explain (costs off) select proname from pg_proc where proname ilike '00%foo' order by 1; @@ -252,14 +254,16 @@ select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1; (6 rows) select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1; - proname ------------------------- + proname +----------------------------- RI_FKey_cascade_del RI_FKey_noaction_del + RI_FKey_period_noaction_del + RI_FKey_period_restrict_del RI_FKey_restrict_del RI_FKey_setdefault_del RI_FKey_setnull_del -(5 rows) +(7 rows) explain (costs off) select proname from pg_proc where proname ilike '00%foo' order by 1; diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out index 726e94102bf..2139a393dde 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -98,21 +98,51 @@ CREATE TABLE temporal_rng3 ( ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk; DROP TABLE temporal_rng3; DROP TYPE textrange2; --- PK with a multirange: +-- PK with one column plus a multirange: CREATE TABLE temporal_mltrng ( id int4range, - valid_at tsmultirange, + 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 | tsmultirange | | not null | + 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 tsrange, @@ -289,6 +319,30 @@ DETAIL: Failing row contains (null, ["Mon Jan 01 00:00:00 2018","Fri Jan 05 00: INSERT INTO temporal_rng VALUES ('[3,3]', 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 VALUES ('[1,1]', datemultirange(daterange('2018-01-02', '2018-02-03'))); +INSERT INTO temporal_mltrng VALUES ('[1,1]', datemultirange(daterange('2018-03-03', '2018-04-04'))); +INSERT INTO temporal_mltrng VALUES ('[2,2]', datemultirange(daterange('2018-01-01', '2018-01-05'))); +INSERT INTO temporal_mltrng VALUES ('[3,3]', datemultirange(daterange('2018-01-01', NULL))); +-- should fail: +INSERT INTO temporal_mltrng VALUES ('[1,1]', 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), {[01-01-2018,01-05-2018)}) conflicts with existing key (id, valid_at)=([1,2), {[01-02-2018,02-03-2018)}). +INSERT INTO temporal_mltrng 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, {[01-01-2018,01-05-2018)}). +INSERT INTO temporal_mltrng VALUES ('[3,3]', 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) | {[01-02-2018,02-03-2018)} + [1,2) | {[03-03-2018,04-04-2018)} + [2,3) | {[01-01-2018,01-05-2018)} + [3,4) | {[01-01-2018,)} +(4 rows) + -- -- test a range with both a PK and a UNIQUE constraint -- @@ -393,3 +447,953 @@ SELECT * FROM tp2 ORDER BY id, valid_at; (1 row) DROP TABLE temporal_partitioned; +-- +-- test FK dependencies +-- +-- can't drop a range referenced by an FK, unless with CASCADE +CREATE TABLE temporal3 ( + id int4range, + valid_at tsrange, + CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +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 temporal3 (id, PERIOD valid_at) +); +ALTER TABLE temporal3 DROP COLUMN valid_at; +ERROR: cannot drop column valid_at of table temporal3 because other objects depend on it +DETAIL: constraint temporal_fk_rng2rng_fk on table temporal_fk_rng2rng depends on column valid_at of table temporal3 +HINT: Use DROP ... CASCADE to drop the dependent objects too. +ALTER TABLE temporal3 DROP COLUMN valid_at CASCADE; +NOTICE: drop cascades to constraint temporal_fk_rng2rng_fk on table temporal_fk_rng2rng +DROP TABLE temporal_fk_rng2rng; +DROP TABLE temporal3; +-- +-- test FOREIGN KEY, range references range +-- +-- test table setup +DROP TABLE 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. +-- works: PERIOD for both referenced and referencing +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 part 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, 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 part 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 part 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 +); +ERROR: foreign key uses PERIOD on the referenced table but not the referencing table +-- (parent_id, PERIOD valid_at) REFERENCES (id) +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) +); +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_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: +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 +); +DROP TABLE temporal_fk_rng2rng; +-- should fail because of duplicate referenced columns: +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 +-- Two scalar columns +DROP TABLE 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) +); +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) +); +\d temporal_fk2_rng2rng + Table "public.temporal_fk2_rng2rng" + Column | Type | Collation | Nullable | Default +------------+-----------+-----------+----------+--------- + id | int4range | | not null | + valid_at | tsrange | | not null | + 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) + +DROP TABLE temporal_fk2_rng2rng; +-- +-- test ALTER TABLE ADD CONSTRAINT +-- +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) +); +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng (id, PERIOD valid_at); +-- 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) +); +ALTER TABLE temporal_fk2_rng2rng + ADD CONSTRAINT temporal_fk2_rng2rng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_rng2 (id1, id2, PERIOD valid_at); +\d temporal_fk2_rng2rng + Table "public.temporal_fk2_rng2rng" + Column | Type | Collation | Nullable | Default +------------+-----------+-----------+----------+--------- + id | int4range | | not null | + valid_at | tsrange | | not null | + 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) + +-- with inferred PK on the referenced table, and wrong column type: +ALTER TABLE temporal_fk_rng2rng + DROP CONSTRAINT temporal_fk_rng2rng_fk, + ALTER COLUMN valid_at TYPE daterange USING daterange(lower(valid_at)::date, upper(valid_at)::date); +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng; +ERROR: foreign key constraint "temporal_fk_rng2rng_fk" cannot be implemented +DETAIL: Key columns "valid_at" and "valid_at" are of incompatible types: daterange and tsrange. +ALTER TABLE temporal_fk_rng2rng + ALTER COLUMN valid_at TYPE tsrange USING tsrange(lower(valid_at), upper(valid_at)); +-- with inferred PK on the referenced table: +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng; +-- should fail because of duplicate referenced columns: +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk2 + FOREIGN KEY (parent_id, PERIOD parent_id) + REFERENCES temporal_rng (id, PERIOD id); +ERROR: foreign key referenced-columns list must not contain duplicates +-- +-- test with rows already +-- +DELETE FROM temporal_fk_rng2rng; +DELETE FROM temporal_rng; +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]'); +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng; +ALTER TABLE temporal_fk_rng2rng + DROP CONSTRAINT temporal_fk_rng2rng_fk; +INSERT INTO temporal_fk_rng2rng VALUES ('[2,2]', tsrange('2018-01-02', '2018-04-01'), '[1,1]'); +-- should fail: +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","Sun Apr 01 00:00:00 2018")) is not present in table "temporal_rng". +-- okay again: +DELETE FROM temporal_fk_rng2rng; +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng; +-- +-- test pg_get_constraintdef +-- +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_rng2rng_fk'; + pg_get_constraintdef +--------------------------------------------------------------------------------------- + FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng(id, PERIOD valid_at) +(1 row) + +-- +-- test FK child inserts +-- +INSERT INTO temporal_fk_rng2rng VALUES ('[1,1]', tsrange('2018-01-02', '2018-02-01'), '[1,1]'); +-- 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" +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". +-- 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]'); +-- +-- 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 + ('[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]'); + ALTER TABLE temporal_fk_rng2rng + ALTER CONSTRAINT temporal_fk_rng2rng_fk + DEFERRABLE INITIALLY DEFERRED; + DELETE FROM temporal_rng WHERE id = '[5,5]'; --should not fail yet. +COMMIT; -- should fail here. +ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), ["Mon Jan 01 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is still referenced from table "temporal_fk_rng2rng". +-- +-- test FK parent updates NO ACTION +-- +-- 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')), + ('[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'); +-- a PK update that fails because both are referenced: +UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') +WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01'); +ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), ["Mon Jan 01 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is still referenced from table "temporal_fk_rng2rng". +-- changing the scalar part fails: +UPDATE temporal_rng SET id = '[7,7]' +WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01'); +ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), ["Mon Jan 01 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is still referenced from table "temporal_fk_rng2rng". +-- then delete the objecting FK record and the same PK update succeeds: +DELETE FROM temporal_fk_rng2rng WHERE id = '[3,3]'; +UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') +WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01'); +-- clean up: +DELETE FROM temporal_fk_rng2rng WHERE parent_id = '[5,5]'; +DELETE FROM temporal_rng WHERE id = '[5,5]'; +-- +-- test FK parent updates RESTRICT +-- +ALTER TABLE temporal_fk_rng2rng + DROP CONSTRAINT temporal_fk_rng2rng_fk; +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng + 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')), + ('[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'); +-- a PK update that fails because both are referenced: +UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') +WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01'); +ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), ["Mon Jan 01 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is still referenced from table "temporal_fk_rng2rng". +-- changing the scalar part fails: +UPDATE temporal_rng SET id = '[7,7]' +WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01'); +ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), ["Mon Jan 01 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is still referenced from table "temporal_fk_rng2rng". +-- then delete the objecting FK record and the same PK update succeeds: +DELETE FROM temporal_fk_rng2rng WHERE id = '[3,3]'; +UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') +WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01'); +-- clean up: +DELETE FROM temporal_fk_rng2rng WHERE parent_id = '[5,5]'; +DELETE FROM temporal_rng WHERE id = '[5,5]'; +-- +-- test FK parent deletes NO ACTION +-- +ALTER TABLE temporal_fk_rng2rng + DROP CONSTRAINT temporal_fk_rng2rng_fk; +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng; +-- a PK delete that succeeds because the numeric id isn't referenced: +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')), + ('[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: +DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01'); +ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), ["Mon Jan 01 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is still referenced from table "temporal_fk_rng2rng". +-- then delete the objecting FK record and the same PK delete succeeds: +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 FK parent deletes RESTRICT +-- +ALTER TABLE temporal_fk_rng2rng + DROP CONSTRAINT temporal_fk_rng2rng_fk; +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng + ON DELETE RESTRICT; +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')), + ('[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: +DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01'); +ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), ["Mon Jan 01 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is still referenced from table "temporal_fk_rng2rng". +-- then delete the objecting FK record and the same PK delete succeeds: +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 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 + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng + ON DELETE CASCADE ON UPDATE CASCADE; +ERROR: invalid ON DELETE action for foreign key constraint using PERIOD +-- 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 + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng + ON DELETE SET NULL ON UPDATE SET NULL; +ERROR: invalid ON DELETE action for foreign key constraint using PERIOD +-- 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, + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng + 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) +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: foreign key uses PERIOD on the referenced table but not the referencing table +-- (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: +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 +); +DROP TABLE temporal_fk_mltrng2mltrng; +-- 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) + +-- with inferred PK on the referenced table, and wrong column type: +ALTER TABLE temporal_fk_mltrng2mltrng + DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk, + ALTER COLUMN valid_at TYPE tsmultirange USING '{}'::tsmultirange; +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng; +ERROR: foreign key constraint "temporal_fk_mltrng2mltrng_fk" cannot be implemented +DETAIL: Key columns "valid_at" and "valid_at" are of incompatible types: tsmultirange and datemultirange. +ALTER TABLE temporal_fk_mltrng2mltrng + ALTER COLUMN valid_at TYPE datemultirange USING '{}'::datemultirange; +-- with inferred PK on the referenced table: +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng; +-- 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 VALUES ('[1,1]', datemultirange(daterange('2018-01-02', '2018-02-01')), '[1,1]'); +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng; +ALTER TABLE temporal_fk_mltrng2mltrng + DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; +INSERT INTO temporal_fk_mltrng2mltrng VALUES ('[2,2]', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,1]'); +-- should fail: +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng; +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), {[01-02-2018,04-01-2018)}) 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; +-- +-- 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 child inserts +-- +INSERT INTO temporal_fk_mltrng2mltrng VALUES ('[1,1]', datemultirange(daterange('2018-01-02', '2018-02-01')), '[1,1]'); +-- should fail: +INSERT INTO temporal_fk_mltrng2mltrng VALUES ('[2,2]', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,1]'); +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), {[01-02-2018,04-01-2018)}) is not present in table "temporal_mltrng". +-- now it should work: +INSERT INTO temporal_mltrng VALUES ('[1,1]', datemultirange(daterange('2018-02-03', '2018-03-03'))); +INSERT INTO temporal_fk_mltrng2mltrng VALUES ('[2,2]', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,1]'); +-- +-- test FK child updates +-- +UPDATE temporal_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2018-01-02', '2018-03-01')) WHERE id = '[1,1]'; +-- should fail: +UPDATE temporal_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2018-01-02', '2018-05-01')) WHERE id = '[1,1]'; +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), {[01-02-2018,05-01-2018)}) is not present in table "temporal_mltrng". +UPDATE temporal_fk_mltrng2mltrng SET parent_id = '[8,8]' WHERE id = '[1,1]'; +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), {[01-02-2018,03-01-2018)}) is not present in table "temporal_mltrng". +-- ALTER FK DEFERRABLE +BEGIN; + INSERT INTO temporal_mltrng VALUES + ('[5,5]', datemultirange(daterange('2018-01-01', '2018-02-01'))), + ('[5,5]', datemultirange(daterange('2018-02-01', '2018-03-01'))); + INSERT INTO temporal_fk_mltrng2mltrng VALUES + ('[3,3]', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,5]'); + ALTER TABLE temporal_fk_mltrng2mltrng + ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk + DEFERRABLE INITIALLY DEFERRED; + DELETE FROM temporal_mltrng WHERE id = '[5,5]'; --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), {[01-01-2018,02-01-2018)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- +-- test FK parent updates NO ACTION +-- +-- a PK update that succeeds because the numeric id isn't referenced: +INSERT INTO temporal_mltrng VALUES ('[5,5]', 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,5]'; +-- a PK update that succeeds even though the numeric id is referenced because the range isn't: +DELETE FROM temporal_mltrng WHERE id = '[5,5]'; +INSERT INTO temporal_mltrng VALUES + ('[5,5]', datemultirange(daterange('2018-01-01', '2018-02-01'))), + ('[5,5]', datemultirange(daterange('2018-02-01', '2018-03-01'))); +INSERT INTO temporal_fk_mltrng2mltrng VALUES ('[3,3]', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,5]'); +UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03-01')) +WHERE id = '[5,5]' 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,5]' 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), {[01-01-2018,02-01-2018)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- changing the scalar part fails: +UPDATE temporal_mltrng SET id = '[7,7]' +WHERE id = '[5,5]' 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), {[01-01-2018,02-01-2018)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- clean up: +DELETE FROM temporal_fk_mltrng2mltrng WHERE parent_id = '[5,5]'; +DELETE FROM temporal_mltrng WHERE id IN ('[5,5]', '[7,7]'); +-- +-- test FK parent updates RESTRICT +-- +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 + ON UPDATE RESTRICT; +-- a PK update that succeeds because the numeric id isn't referenced: +INSERT INTO temporal_mltrng VALUES ('[5,5]', 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,5]'; +-- a PK update that succeeds even though the numeric id is referenced because the range isn't: +DELETE FROM temporal_mltrng WHERE id = '[5,5]'; +INSERT INTO temporal_mltrng VALUES + ('[5,5]', datemultirange(daterange('2018-01-01', '2018-02-01'))), + ('[5,5]', datemultirange(daterange('2018-02-01', '2018-03-01'))); +INSERT INTO temporal_fk_mltrng2mltrng VALUES ('[3,3]', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,5]'); +UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03-01')) +WHERE id = '[5,5]' 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,5]' 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), {[01-01-2018,02-01-2018)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- changing the scalar part fails: +UPDATE temporal_mltrng SET id = '[7,7]' +WHERE id = '[5,5]' 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), {[01-01-2018,02-01-2018)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- clean up: +DELETE FROM temporal_fk_mltrng2mltrng WHERE parent_id = '[5,5]'; +DELETE FROM temporal_mltrng WHERE id IN ('[5,5]', '[7,7]'); +-- +-- test FK parent deletes NO ACTION +-- +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; +-- a PK delete that succeeds because the numeric id isn't referenced: +INSERT INTO temporal_mltrng VALUES ('[5,5]', datemultirange(daterange('2018-01-01', '2018-02-01'))); +DELETE FROM temporal_mltrng WHERE id = '[5,5]'; +-- a PK delete that succeeds even though the numeric id is referenced because the range isn't: +INSERT INTO temporal_mltrng VALUES + ('[5,5]', datemultirange(daterange('2018-01-01', '2018-02-01'))), + ('[5,5]', datemultirange(daterange('2018-02-01', '2018-03-01'))); +INSERT INTO temporal_fk_mltrng2mltrng VALUES ('[3,3]', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,5]'); +DELETE FROM temporal_mltrng WHERE id = '[5,5]' 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,5]' 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), {[01-01-2018,02-01-2018)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- clean up: +DELETE FROM temporal_fk_mltrng2mltrng WHERE parent_id = '[5,5]'; +DELETE FROM temporal_mltrng WHERE id IN ('[5,5]'); +-- +-- test FK parent deletes RESTRICT +-- +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 + ON DELETE RESTRICT; +INSERT INTO temporal_mltrng VALUES ('[5,5]', datemultirange(daterange('2018-01-01', '2018-02-01'))); +DELETE FROM temporal_mltrng WHERE id = '[5,5]'; +-- a PK delete that succeeds even though the numeric id is referenced because the range isn't: +INSERT INTO temporal_mltrng VALUES + ('[5,5]', datemultirange(daterange('2018-01-01', '2018-02-01'))), + ('[5,5]', datemultirange(daterange('2018-02-01', '2018-03-01'))); +INSERT INTO temporal_fk_mltrng2mltrng VALUES ('[3,3]', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,5]'); +DELETE FROM temporal_mltrng WHERE id = '[5,5]' 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,5]' 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), {[01-01-2018,02-01-2018)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- clean up: +DELETE FROM temporal_fk_mltrng2mltrng WHERE parent_id = '[5,5]'; +DELETE FROM temporal_mltrng WHERE id IN ('[5,5]'); +-- FK between partitioned tables +CREATE TABLE temporal_partitioned_rng ( + id int4range, + valid_at daterange, + name text, + CONSTRAINT temporal_paritioned_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +) PARTITION BY LIST (id); +CREATE TABLE tp1 partition OF temporal_partitioned_rng FOR VALUES IN ('[1,1]', '[3,3]', '[5,5]', '[7,7]', '[9,9]', '[11,11]'); +CREATE TABLE tp2 partition OF temporal_partitioned_rng FOR VALUES IN ('[2,2]', '[4,4]', '[6,6]', '[8,8]', '[10,10]', '[12,12]'); +INSERT INTO temporal_partitioned_rng VALUES + ('[1,1]', daterange('2000-01-01', '2000-02-01'), 'one'), + ('[1,1]', daterange('2000-02-01', '2000-03-01'), 'one'), + ('[2,2]', daterange('2000-01-01', '2010-01-01'), 'two'); +CREATE TABLE temporal_partitioned_fk_rng2rng ( + id int4range, + valid_at daterange, + parent_id int4range, + CONSTRAINT temporal_partitioned_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_partitioned_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_partitioned_rng (id, PERIOD valid_at) +) PARTITION BY LIST (id); +CREATE TABLE tfkp1 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[1,1]', '[3,3]', '[5,5]', '[7,7]', '[9,9]', '[11,11]'); +CREATE TABLE tfkp2 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[2,2]', '[4,4]', '[6,6]', '[8,8]', '[10,10]', '[12,12]'); +-- partitioned FK child inserts +INSERT INTO temporal_partitioned_fk_rng2rng VALUES + ('[1,1]', daterange('2000-01-01', '2000-02-15'), '[1,1]'), + ('[1,1]', daterange('2001-01-01', '2002-01-01'), '[2,2]'), + ('[2,2]', daterange('2000-01-01', '2000-02-15'), '[1,1]'); +-- should fail: +INSERT INTO temporal_partitioned_fk_rng2rng VALUES + ('[3,3]', daterange('2010-01-01', '2010-02-15'), '[1,1]'); +ERROR: insert or update on table "tfkp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=([1,2), [01-01-2010,02-15-2010)) is not present in table "temporal_partitioned_rng". +INSERT INTO temporal_partitioned_fk_rng2rng VALUES + ('[3,3]', daterange('2000-01-01', '2000-02-15'), '[3,3]'); +ERROR: insert or update on table "tfkp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=([3,4), [01-01-2000,02-15-2000)) is not present in table "temporal_partitioned_rng". +-- partitioned FK child updates +UPDATE temporal_partitioned_fk_rng2rng SET valid_at = daterange('2000-01-01', '2000-02-13') WHERE id = '[2,2]'; +-- move a row from the first partition to the second +UPDATE temporal_partitioned_fk_rng2rng SET id = '[4,4]' WHERE id = '[1,1]'; +-- move a row from the second partition to the first +UPDATE temporal_partitioned_fk_rng2rng SET id = '[1,1]' WHERE id = '[4,4]'; +-- should fail: +UPDATE temporal_partitioned_fk_rng2rng SET valid_at = daterange('2000-01-01', '2000-04-01') WHERE id = '[1,1]'; +ERROR: conflicting key value violates exclusion constraint "tfkp1_pkey" +DETAIL: Key (id, valid_at)=([1,2), [01-01-2000,04-01-2000)) conflicts with existing key (id, valid_at)=([1,2), [01-01-2000,04-01-2000)). +-- partitioned FK parent updates NO ACTION +INSERT INTO temporal_partitioned_rng VALUES ('[5,5]', daterange('2016-01-01', '2016-02-01')); +UPDATE temporal_partitioned_rng SET valid_at = daterange('2018-01-01', '2018-02-01') WHERE id = '[5,5]'; +INSERT INTO temporal_partitioned_rng VALUES ('[5,5]', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[3,3]', daterange('2018-01-05', '2018-01-10'), '[5,5]'); +UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-02-01', '2016-03-01') + WHERE id = '[5,5]' AND valid_at = daterange('2018-02-01', '2018-03-01'); +-- should fail: +UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-01-01', '2016-02-01') + WHERE id = '[5,5]' AND valid_at = daterange('2018-01-01', '2018-02-01'); +ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey" on table "temporal_partitioned_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), [01-01-2018,02-01-2018)) is still referenced from table "temporal_partitioned_fk_rng2rng". +-- clean up: +DELETE FROM temporal_partitioned_fk_rng2rng WHERE parent_id = '[5,5]'; +DELETE FROM temporal_partitioned_rng WHERE id = '[5,5]'; +-- partitioned FK parent deletes NO ACTION +INSERT INTO temporal_partitioned_rng VALUES ('[5,5]', daterange('2018-01-01', '2018-02-01')); +INSERT INTO temporal_partitioned_rng VALUES ('[5,5]', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[3,3]', daterange('2018-01-05', '2018-01-10'), '[5,5]'); +DELETE FROM temporal_partitioned_rng WHERE id = '[5,5]' AND valid_at = daterange('2018-02-01', '2018-03-01'); +-- should fail: +DELETE FROM temporal_partitioned_rng WHERE id = '[5,5]' AND valid_at = daterange('2018-01-01', '2018-02-01'); +ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey" on table "temporal_partitioned_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), [01-01-2018,02-01-2018)) is still referenced from table "temporal_partitioned_fk_rng2rng". +-- clean up: +DELETE FROM temporal_partitioned_fk_rng2rng WHERE parent_id = '[5,5]'; +DELETE FROM temporal_partitioned_rng WHERE id = '[5,5]'; +-- partitioned FK parent updates RESTRICT +ALTER TABLE temporal_partitioned_fk_rng2rng + DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk; +ALTER TABLE temporal_partitioned_fk_rng2rng + ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_partitioned_rng + ON DELETE RESTRICT; +INSERT INTO temporal_partitioned_rng VALUES ('[5,5]', daterange('2016-01-01', '2016-02-01')); +UPDATE temporal_partitioned_rng SET valid_at = daterange('2018-01-01', '2018-02-01') WHERE id = '[5,5]'; +INSERT INTO temporal_partitioned_rng VALUES ('[5,5]', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[3,3]', daterange('2018-01-05', '2018-01-10'), '[5,5]'); +UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-02-01', '2016-03-01') + WHERE id = '[5,5]' AND valid_at = daterange('2018-02-01', '2018-03-01'); +-- should fail: +UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-01-01', '2016-02-01') + WHERE id = '[5,5]' AND valid_at = daterange('2018-01-01', '2018-02-01'); +ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey" on table "temporal_partitioned_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), [01-01-2018,02-01-2018)) is still referenced from table "temporal_partitioned_fk_rng2rng". +-- clean up: +DELETE FROM temporal_partitioned_fk_rng2rng WHERE parent_id = '[5,5]'; +DELETE FROM temporal_partitioned_rng WHERE id = '[5,5]'; +-- partitioned FK parent deletes RESTRICT +INSERT INTO temporal_partitioned_rng VALUES ('[5,5]', daterange('2018-01-01', '2018-02-01')); +INSERT INTO temporal_partitioned_rng VALUES ('[5,5]', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[3,3]', daterange('2018-01-05', '2018-01-10'), '[5,5]'); +DELETE FROM temporal_partitioned_rng WHERE id = '[5,5]' AND valid_at = daterange('2018-02-01', '2018-03-01'); +-- should fail: +DELETE FROM temporal_partitioned_rng WHERE id = '[5,5]' AND valid_at = daterange('2018-01-01', '2018-02-01'); +ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey" on table "temporal_partitioned_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), [01-01-2018,02-01-2018)) is still referenced from table "temporal_partitioned_fk_rng2rng". +-- partitioned FK parent updates CASCADE +ALTER TABLE temporal_partitioned_fk_rng2rng + DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, + ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_partitioned_rng + ON DELETE CASCADE ON UPDATE CASCADE; +ERROR: invalid ON DELETE action for foreign key constraint using PERIOD +-- partitioned FK parent deletes CASCADE +-- partitioned FK parent updates SET NULL +ALTER TABLE temporal_partitioned_fk_rng2rng + DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, + ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_partitioned_rng + ON DELETE SET NULL ON UPDATE SET NULL; +ERROR: invalid ON DELETE action for foreign key constraint using PERIOD +-- 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, + ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_partitioned_rng + ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; +ERROR: invalid ON DELETE action for foreign key constraint using PERIOD +-- 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 c8e8ab994a0..540fc74eccd 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -56,7 +56,6 @@ CREATE TABLE temporal_rng2 ( SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng2_pk'; SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng2_pk'; - -- PK with a custom range type: CREATE TYPE textrange2 AS range (subtype=text, collation="C"); CREATE TABLE temporal_rng3 ( @@ -68,14 +67,26 @@ ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk; DROP TABLE temporal_rng3; DROP TYPE textrange2; --- PK with a multirange: +-- PK with one column plus a multirange: CREATE TABLE temporal_mltrng ( id int4range, - valid_at tsmultirange, + 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 ( @@ -214,6 +225,19 @@ INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-01-01', '2018-01-05')); INSERT INTO temporal_rng VALUES (NULL, tsrange('2018-01-01', '2018-01-05')); INSERT INTO temporal_rng VALUES ('[3,3]', NULL); +-- okay: +INSERT INTO temporal_mltrng VALUES ('[1,1]', datemultirange(daterange('2018-01-02', '2018-02-03'))); +INSERT INTO temporal_mltrng VALUES ('[1,1]', datemultirange(daterange('2018-03-03', '2018-04-04'))); +INSERT INTO temporal_mltrng VALUES ('[2,2]', datemultirange(daterange('2018-01-01', '2018-01-05'))); +INSERT INTO temporal_mltrng VALUES ('[3,3]', datemultirange(daterange('2018-01-01', NULL))); + +-- should fail: +INSERT INTO temporal_mltrng VALUES ('[1,1]', datemultirange(daterange('2018-01-01', '2018-01-05'))); +INSERT INTO temporal_mltrng VALUES (NULL, datemultirange(daterange('2018-01-01', '2018-01-05'))); +INSERT INTO temporal_mltrng VALUES ('[3,3]', NULL); + +SELECT * FROM temporal_mltrng ORDER BY id, valid_at; + -- -- test a range with both a PK and a UNIQUE constraint -- @@ -288,3 +312,897 @@ SELECT * FROM temporal_partitioned ORDER BY id, valid_at; SELECT * FROM tp1 ORDER BY id, valid_at; SELECT * FROM tp2 ORDER BY id, valid_at; DROP TABLE temporal_partitioned; + +-- +-- test FK dependencies +-- + +-- can't drop a range referenced by an FK, unless with CASCADE +CREATE TABLE temporal3 ( + id int4range, + valid_at tsrange, + CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +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 temporal3 (id, PERIOD valid_at) +); +ALTER TABLE temporal3 DROP COLUMN valid_at; +ALTER TABLE temporal3 DROP COLUMN valid_at CASCADE; +DROP TABLE temporal_fk_rng2rng; +DROP TABLE temporal3; + +-- +-- test FOREIGN KEY, range references range +-- + +-- test table setup +DROP TABLE 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) +); + +-- works: PERIOD for both referenced and referencing +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 part 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, PERIOD valid_at) + REFERENCES temporal_rng (id, valid_at) +); +-- (parent_id, valid_at) REFERENCES (id, PERIOD valid_at) +-- FOREIGN KEY part 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 part 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 +); +-- (parent_id, PERIOD valid_at) REFERENCES (id) +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) +); +-- (parent_id) REFERENCES (id, PERIOD valid_at) +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: +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 +); +DROP TABLE temporal_fk_rng2rng; + +-- should fail because of duplicate referenced columns: +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) +); + +-- Two scalar columns +DROP TABLE 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) +); + +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) +); +\d temporal_fk2_rng2rng +DROP TABLE temporal_fk2_rng2rng; + +-- +-- test ALTER TABLE ADD CONSTRAINT +-- + +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) +); +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng (id, PERIOD valid_at); +-- 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) +); +ALTER TABLE temporal_fk2_rng2rng + ADD CONSTRAINT temporal_fk2_rng2rng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_rng2 (id1, id2, PERIOD valid_at); +\d temporal_fk2_rng2rng + +-- with inferred PK on the referenced table, and wrong column type: +ALTER TABLE temporal_fk_rng2rng + DROP CONSTRAINT temporal_fk_rng2rng_fk, + ALTER COLUMN valid_at TYPE daterange USING daterange(lower(valid_at)::date, upper(valid_at)::date); +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng; +ALTER TABLE temporal_fk_rng2rng + ALTER COLUMN valid_at TYPE tsrange USING tsrange(lower(valid_at), upper(valid_at)); + +-- with inferred PK on the referenced table: +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng; + +-- should fail because of duplicate referenced columns: +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk2 + FOREIGN KEY (parent_id, PERIOD parent_id) + REFERENCES temporal_rng (id, PERIOD id); + +-- +-- test with rows already +-- + +DELETE FROM temporal_fk_rng2rng; +DELETE FROM temporal_rng; +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]'); +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng; +ALTER TABLE temporal_fk_rng2rng + DROP CONSTRAINT temporal_fk_rng2rng_fk; +INSERT INTO temporal_fk_rng2rng VALUES ('[2,2]', tsrange('2018-01-02', '2018-04-01'), '[1,1]'); +-- should fail: +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng; +-- okay again: +DELETE FROM temporal_fk_rng2rng; +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng; + +-- +-- test pg_get_constraintdef +-- + +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_rng2rng_fk'; + +-- +-- test FK child inserts +-- + +INSERT INTO temporal_fk_rng2rng VALUES ('[1,1]', tsrange('2018-01-02', '2018-02-01'), '[1,1]'); +-- should fail: +INSERT INTO temporal_fk_rng2rng VALUES ('[2,2]', tsrange('2018-01-02', '2018-04-01'), '[1,1]'); +-- 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]'); + +-- +-- 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]'; +UPDATE temporal_fk_rng2rng SET parent_id = '[8,8]' WHERE id = '[1,1]'; + +-- ALTER FK DEFERRABLE + +BEGIN; + 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]'); + ALTER TABLE temporal_fk_rng2rng + ALTER CONSTRAINT temporal_fk_rng2rng_fk + DEFERRABLE INITIALLY DEFERRED; + + DELETE FROM temporal_rng WHERE id = '[5,5]'; --should not fail yet. +COMMIT; -- should fail here. + +-- +-- test FK parent updates NO ACTION +-- + +-- 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')), + ('[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'); +-- a PK update that fails because both are referenced: +UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') +WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01'); +-- changing the scalar part fails: +UPDATE temporal_rng SET id = '[7,7]' +WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01'); +-- then delete the objecting FK record and the same PK update succeeds: +DELETE FROM temporal_fk_rng2rng WHERE id = '[3,3]'; +UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') +WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01'); +-- clean up: +DELETE FROM temporal_fk_rng2rng WHERE parent_id = '[5,5]'; +DELETE FROM temporal_rng WHERE id = '[5,5]'; + +-- +-- test FK parent updates RESTRICT +-- + +ALTER TABLE temporal_fk_rng2rng + DROP CONSTRAINT temporal_fk_rng2rng_fk; +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng + 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')), + ('[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'); +-- a PK update that fails because both are referenced: +UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') +WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01'); +-- changing the scalar part fails: +UPDATE temporal_rng SET id = '[7,7]' +WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01'); +-- then delete the objecting FK record and the same PK update succeeds: +DELETE FROM temporal_fk_rng2rng WHERE id = '[3,3]'; +UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') +WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01'); +-- clean up: +DELETE FROM temporal_fk_rng2rng WHERE parent_id = '[5,5]'; +DELETE FROM temporal_rng WHERE id = '[5,5]'; +-- +-- test FK parent deletes NO ACTION +-- +ALTER TABLE temporal_fk_rng2rng + DROP CONSTRAINT temporal_fk_rng2rng_fk; +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng; +-- a PK delete that succeeds because the numeric id isn't referenced: +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')), + ('[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: +DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01'); +-- then delete the objecting FK record and the same PK delete succeeds: +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 FK parent deletes RESTRICT +-- + +ALTER TABLE temporal_fk_rng2rng + DROP CONSTRAINT temporal_fk_rng2rng_fk; +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng + ON DELETE RESTRICT; +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')), + ('[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: +DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01'); +-- then delete the objecting FK record and the same PK delete succeeds: +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 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 + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng + 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 + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng + 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, + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng + 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) +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: +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 +); +DROP TABLE temporal_fk_mltrng2mltrng; + +-- 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 + +-- with inferred PK on the referenced table, and wrong column type: +ALTER TABLE temporal_fk_mltrng2mltrng + DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk, + ALTER COLUMN valid_at TYPE tsmultirange USING '{}'::tsmultirange; +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng; +ALTER TABLE temporal_fk_mltrng2mltrng + ALTER COLUMN valid_at TYPE datemultirange USING '{}'::datemultirange; + +-- with inferred PK on the referenced table: +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng; + +-- 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 VALUES ('[1,1]', datemultirange(daterange('2018-01-02', '2018-02-01')), '[1,1]'); +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng; +ALTER TABLE temporal_fk_mltrng2mltrng + DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; +INSERT INTO temporal_fk_mltrng2mltrng VALUES ('[2,2]', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,1]'); +-- should fail: +ALTER TABLE temporal_fk_mltrng2mltrng + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES 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; + +-- +-- test pg_get_constraintdef +-- + +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_mltrng2mltrng_fk'; + +-- +-- test FK child inserts +-- + +INSERT INTO temporal_fk_mltrng2mltrng VALUES ('[1,1]', datemultirange(daterange('2018-01-02', '2018-02-01')), '[1,1]'); +-- should fail: +INSERT INTO temporal_fk_mltrng2mltrng VALUES ('[2,2]', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,1]'); +-- now it should work: +INSERT INTO temporal_mltrng VALUES ('[1,1]', datemultirange(daterange('2018-02-03', '2018-03-03'))); +INSERT INTO temporal_fk_mltrng2mltrng VALUES ('[2,2]', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,1]'); + +-- +-- test FK child updates +-- + +UPDATE temporal_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2018-01-02', '2018-03-01')) WHERE id = '[1,1]'; +-- should fail: +UPDATE temporal_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2018-01-02', '2018-05-01')) WHERE id = '[1,1]'; +UPDATE temporal_fk_mltrng2mltrng SET parent_id = '[8,8]' WHERE id = '[1,1]'; + +-- ALTER FK DEFERRABLE + +BEGIN; + INSERT INTO temporal_mltrng VALUES + ('[5,5]', datemultirange(daterange('2018-01-01', '2018-02-01'))), + ('[5,5]', datemultirange(daterange('2018-02-01', '2018-03-01'))); + INSERT INTO temporal_fk_mltrng2mltrng VALUES + ('[3,3]', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,5]'); + ALTER TABLE temporal_fk_mltrng2mltrng + ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk + DEFERRABLE INITIALLY DEFERRED; + + DELETE FROM temporal_mltrng WHERE id = '[5,5]'; --should not fail yet. +COMMIT; -- should fail here. + +-- +-- test FK parent updates NO ACTION +-- + +-- a PK update that succeeds because the numeric id isn't referenced: +INSERT INTO temporal_mltrng VALUES ('[5,5]', 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,5]'; +-- a PK update that succeeds even though the numeric id is referenced because the range isn't: +DELETE FROM temporal_mltrng WHERE id = '[5,5]'; +INSERT INTO temporal_mltrng VALUES + ('[5,5]', datemultirange(daterange('2018-01-01', '2018-02-01'))), + ('[5,5]', datemultirange(daterange('2018-02-01', '2018-03-01'))); +INSERT INTO temporal_fk_mltrng2mltrng VALUES ('[3,3]', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,5]'); +UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03-01')) +WHERE id = '[5,5]' 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,5]' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +-- changing the scalar part fails: +UPDATE temporal_mltrng SET id = '[7,7]' +WHERE id = '[5,5]' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +-- clean up: +DELETE FROM temporal_fk_mltrng2mltrng WHERE parent_id = '[5,5]'; +DELETE FROM temporal_mltrng WHERE id IN ('[5,5]', '[7,7]'); + +-- +-- test FK parent updates RESTRICT +-- + +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 + ON UPDATE RESTRICT; +-- a PK update that succeeds because the numeric id isn't referenced: +INSERT INTO temporal_mltrng VALUES ('[5,5]', 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,5]'; +-- a PK update that succeeds even though the numeric id is referenced because the range isn't: +DELETE FROM temporal_mltrng WHERE id = '[5,5]'; +INSERT INTO temporal_mltrng VALUES + ('[5,5]', datemultirange(daterange('2018-01-01', '2018-02-01'))), + ('[5,5]', datemultirange(daterange('2018-02-01', '2018-03-01'))); +INSERT INTO temporal_fk_mltrng2mltrng VALUES ('[3,3]', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,5]'); +UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03-01')) +WHERE id = '[5,5]' 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,5]' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +-- changing the scalar part fails: +UPDATE temporal_mltrng SET id = '[7,7]' +WHERE id = '[5,5]' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +-- clean up: +DELETE FROM temporal_fk_mltrng2mltrng WHERE parent_id = '[5,5]'; +DELETE FROM temporal_mltrng WHERE id IN ('[5,5]', '[7,7]'); +-- +-- test FK parent deletes NO ACTION +-- +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; +-- a PK delete that succeeds because the numeric id isn't referenced: +INSERT INTO temporal_mltrng VALUES ('[5,5]', datemultirange(daterange('2018-01-01', '2018-02-01'))); +DELETE FROM temporal_mltrng WHERE id = '[5,5]'; +-- a PK delete that succeeds even though the numeric id is referenced because the range isn't: +INSERT INTO temporal_mltrng VALUES + ('[5,5]', datemultirange(daterange('2018-01-01', '2018-02-01'))), + ('[5,5]', datemultirange(daterange('2018-02-01', '2018-03-01'))); +INSERT INTO temporal_fk_mltrng2mltrng VALUES ('[3,3]', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,5]'); +DELETE FROM temporal_mltrng WHERE id = '[5,5]' 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,5]' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +-- clean up: +DELETE FROM temporal_fk_mltrng2mltrng WHERE parent_id = '[5,5]'; +DELETE FROM temporal_mltrng WHERE id IN ('[5,5]'); + +-- +-- test FK parent deletes RESTRICT +-- +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 + ON DELETE RESTRICT; +INSERT INTO temporal_mltrng VALUES ('[5,5]', datemultirange(daterange('2018-01-01', '2018-02-01'))); +DELETE FROM temporal_mltrng WHERE id = '[5,5]'; +-- a PK delete that succeeds even though the numeric id is referenced because the range isn't: +INSERT INTO temporal_mltrng VALUES + ('[5,5]', datemultirange(daterange('2018-01-01', '2018-02-01'))), + ('[5,5]', datemultirange(daterange('2018-02-01', '2018-03-01'))); +INSERT INTO temporal_fk_mltrng2mltrng VALUES ('[3,3]', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,5]'); +DELETE FROM temporal_mltrng WHERE id = '[5,5]' 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,5]' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +-- clean up: +DELETE FROM temporal_fk_mltrng2mltrng WHERE parent_id = '[5,5]'; +DELETE FROM temporal_mltrng WHERE id IN ('[5,5]'); + +-- FK between partitioned tables + +CREATE TABLE temporal_partitioned_rng ( + id int4range, + valid_at daterange, + name text, + CONSTRAINT temporal_paritioned_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +) PARTITION BY LIST (id); +CREATE TABLE tp1 partition OF temporal_partitioned_rng FOR VALUES IN ('[1,1]', '[3,3]', '[5,5]', '[7,7]', '[9,9]', '[11,11]'); +CREATE TABLE tp2 partition OF temporal_partitioned_rng FOR VALUES IN ('[2,2]', '[4,4]', '[6,6]', '[8,8]', '[10,10]', '[12,12]'); +INSERT INTO temporal_partitioned_rng VALUES + ('[1,1]', daterange('2000-01-01', '2000-02-01'), 'one'), + ('[1,1]', daterange('2000-02-01', '2000-03-01'), 'one'), + ('[2,2]', daterange('2000-01-01', '2010-01-01'), 'two'); + +CREATE TABLE temporal_partitioned_fk_rng2rng ( + id int4range, + valid_at daterange, + parent_id int4range, + CONSTRAINT temporal_partitioned_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_partitioned_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_partitioned_rng (id, PERIOD valid_at) +) PARTITION BY LIST (id); +CREATE TABLE tfkp1 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[1,1]', '[3,3]', '[5,5]', '[7,7]', '[9,9]', '[11,11]'); +CREATE TABLE tfkp2 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[2,2]', '[4,4]', '[6,6]', '[8,8]', '[10,10]', '[12,12]'); + +-- partitioned FK child inserts + +INSERT INTO temporal_partitioned_fk_rng2rng VALUES + ('[1,1]', daterange('2000-01-01', '2000-02-15'), '[1,1]'), + ('[1,1]', daterange('2001-01-01', '2002-01-01'), '[2,2]'), + ('[2,2]', daterange('2000-01-01', '2000-02-15'), '[1,1]'); +-- should fail: +INSERT INTO temporal_partitioned_fk_rng2rng VALUES + ('[3,3]', daterange('2010-01-01', '2010-02-15'), '[1,1]'); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES + ('[3,3]', daterange('2000-01-01', '2000-02-15'), '[3,3]'); + +-- partitioned FK child updates + +UPDATE temporal_partitioned_fk_rng2rng SET valid_at = daterange('2000-01-01', '2000-02-13') WHERE id = '[2,2]'; +-- move a row from the first partition to the second +UPDATE temporal_partitioned_fk_rng2rng SET id = '[4,4]' WHERE id = '[1,1]'; +-- move a row from the second partition to the first +UPDATE temporal_partitioned_fk_rng2rng SET id = '[1,1]' WHERE id = '[4,4]'; +-- should fail: +UPDATE temporal_partitioned_fk_rng2rng SET valid_at = daterange('2000-01-01', '2000-04-01') WHERE id = '[1,1]'; + +-- partitioned FK parent updates NO ACTION + +INSERT INTO temporal_partitioned_rng VALUES ('[5,5]', daterange('2016-01-01', '2016-02-01')); +UPDATE temporal_partitioned_rng SET valid_at = daterange('2018-01-01', '2018-02-01') WHERE id = '[5,5]'; +INSERT INTO temporal_partitioned_rng VALUES ('[5,5]', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[3,3]', daterange('2018-01-05', '2018-01-10'), '[5,5]'); +UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-02-01', '2016-03-01') + WHERE id = '[5,5]' AND valid_at = daterange('2018-02-01', '2018-03-01'); +-- should fail: +UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-01-01', '2016-02-01') + WHERE id = '[5,5]' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- clean up: +DELETE FROM temporal_partitioned_fk_rng2rng WHERE parent_id = '[5,5]'; +DELETE FROM temporal_partitioned_rng WHERE id = '[5,5]'; + +-- partitioned FK parent deletes NO ACTION + +INSERT INTO temporal_partitioned_rng VALUES ('[5,5]', daterange('2018-01-01', '2018-02-01')); +INSERT INTO temporal_partitioned_rng VALUES ('[5,5]', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[3,3]', daterange('2018-01-05', '2018-01-10'), '[5,5]'); +DELETE FROM temporal_partitioned_rng WHERE id = '[5,5]' AND valid_at = daterange('2018-02-01', '2018-03-01'); +-- should fail: +DELETE FROM temporal_partitioned_rng WHERE id = '[5,5]' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- clean up: +DELETE FROM temporal_partitioned_fk_rng2rng WHERE parent_id = '[5,5]'; +DELETE FROM temporal_partitioned_rng WHERE id = '[5,5]'; + +-- partitioned FK parent updates RESTRICT + +ALTER TABLE temporal_partitioned_fk_rng2rng + DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk; +ALTER TABLE temporal_partitioned_fk_rng2rng + ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_partitioned_rng + ON DELETE RESTRICT; +INSERT INTO temporal_partitioned_rng VALUES ('[5,5]', daterange('2016-01-01', '2016-02-01')); +UPDATE temporal_partitioned_rng SET valid_at = daterange('2018-01-01', '2018-02-01') WHERE id = '[5,5]'; +INSERT INTO temporal_partitioned_rng VALUES ('[5,5]', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[3,3]', daterange('2018-01-05', '2018-01-10'), '[5,5]'); +UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-02-01', '2016-03-01') + WHERE id = '[5,5]' AND valid_at = daterange('2018-02-01', '2018-03-01'); +-- should fail: +UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-01-01', '2016-02-01') + WHERE id = '[5,5]' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- clean up: +DELETE FROM temporal_partitioned_fk_rng2rng WHERE parent_id = '[5,5]'; +DELETE FROM temporal_partitioned_rng WHERE id = '[5,5]'; + +-- partitioned FK parent deletes RESTRICT + +INSERT INTO temporal_partitioned_rng VALUES ('[5,5]', daterange('2018-01-01', '2018-02-01')); +INSERT INTO temporal_partitioned_rng VALUES ('[5,5]', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[3,3]', daterange('2018-01-05', '2018-01-10'), '[5,5]'); +DELETE FROM temporal_partitioned_rng WHERE id = '[5,5]' AND valid_at = daterange('2018-02-01', '2018-03-01'); +-- should fail: +DELETE FROM temporal_partitioned_rng WHERE id = '[5,5]' AND valid_at = daterange('2018-01-01', '2018-02-01'); + +-- partitioned FK parent updates CASCADE + +ALTER TABLE temporal_partitioned_fk_rng2rng + DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, + ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + 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 + DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, + ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + 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, + ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + 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.42.0