From 61330e25a607e4e5a92eeee5662330df8067842c Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Sun, 27 Jun 2021 12:58:51 -0700 Subject: [PATCH v15 2/5] Add temporal FOREIGN KEYs - Added bison support for temporal FOREIGN KEYs. They should have a range 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. --- doc/src/sgml/ref/create_table.sgml | 42 +- src/backend/commands/tablecmds.c | 764 +++++++++++------- src/backend/parser/gram.y | 37 +- src/backend/utils/adt/ri_triggers.c | 341 +++++++- src/backend/utils/adt/ruleutils.c | 19 +- src/include/catalog/pg_constraint.h | 8 +- src/include/catalog/pg_proc.dat | 24 + src/include/nodes/parsenodes.h | 2 + src/include/parser/kwlist.h | 1 + .../regress/expected/without_overlaps.out | 461 +++++++++++ src/test/regress/sql/without_overlaps.sql | 462 +++++++++++ 11 files changed, 1817 insertions(+), 344 deletions(-) diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 87f0aab13b..fea8fc3c7a 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 [, ... ] [, temporal_interval WITHOUT OVERLAPS ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] [, temporal_interval 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 temporal_interval ] ) REFERENCES reftable [ ( refcolumn [, ... ] [, PERIOD temporal_interval ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] @@ -1149,8 +1149,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 temporal_interval ] ) + REFERENCES reftable [ ( refcolumn [, ... ] [, PERIOD temporal_interval ] ) ] [ MATCH matchtype ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] @@ -1161,11 +1161,29 @@ 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. The referenced columns must be the columns of a non-deferrable - unique or primary key constraint in the referenced table. The user + unique or primary key constraint in the referenced table. + + + + If the last column is marked with PERIOD, + it must be a period or range column, and the referenced table + must have a temporal primary key. + The non-PERIOD columns are treated normally + (and there must be at least one of them), + but the PERIOD column is not compared for equality. + Instead the constraint is considered satisfied + if the referenced table has matching records whose combined ranges completely cover + the referencing record. + In other words, the reference must have a referent for its entire duration. + + + + 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 @@ -1237,7 +1255,10 @@ WITH ( MODULUS numeric_literal, REM Delete any rows referencing the deleted row, or update the values of the referencing column(s) to the new values of the - referenced columns, respectively. + referenced columns, respectively. In a temporal foreign key, + the delete will use FOR PORTION OF semantics + to constrain the effect to the bounds of the referenced row + being deleted. @@ -1250,6 +1271,12 @@ 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, the change will use FOR + PORTION OF semantics to constrain the effect to the bounds + of the referenced row. + @@ -1262,6 +1289,9 @@ WITH ( MODULUS numeric_literal, REM can only be specified for ON DELETE actions. (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, the change will use FOR PORTION + OF semantics to constrain the effect to the bounds of the + referenced row. diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index ba2a8914e4..e01f64ae34 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -380,17 +380,20 @@ static int transformColumnNameList(Oid relId, List *colList, int16 *attnums, Oid *atttypids); static int transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid, List **attnamelist, + Node **pk_period, int16 *attnums, Oid *atttypids, + int16 *periodattnums, Oid *periodatttypids, Oid *opclasses); static Oid transformFkeyCheckAttrs(Relation pkrel, int numattrs, int16 *attnums, + bool is_temporal, int16 periodattnum, 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); @@ -500,7 +503,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); @@ -510,7 +514,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); @@ -542,6 +548,12 @@ static void GetForeignKeyCheckTriggers(Relation trigrel, Oid conoid, Oid confrelid, Oid conrelid, Oid *insertTriggerOid, Oid *updateTriggerOid); +static void FindFKComparisonOperators(Constraint *fkconstraint, + AlteredTableInfo *tab, int i, int16 *fkattnum, + 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, bool missing_ok, LOCKMODE lockmode); @@ -5822,7 +5834,8 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode, validateForeignKeyConstraint(fkconstraint->conname, rel, refrel, con->refindid, - con->conid); + con->conid, + con->contemporal); /* * No need to mark the constraint row as validated, we did @@ -9438,6 +9451,11 @@ 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 = (fkconstraint->fk_period != NULL); + int16 pkperiodattnums[1] = {0}; + int16 fkperiodattnums[1] = {0}; + Oid pkperiodtypoids[1] = {0}; + Oid fkperiodtypoids[1] = {0}; int i; int numfks, numpks, @@ -9532,6 +9550,10 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, numfks = transformColumnNameList(RelationGetRelid(rel), fkconstraint->fk_attrs, fkattnum, fktypoid); + if (is_temporal) + transformColumnNameList(RelationGetRelid(rel), + list_make1(fkconstraint->fk_period), + fkperiodattnums, fkperiodtypoids); numfkdelsetcols = transformColumnNameList(RelationGetRelid(rel), fkconstraint->fk_del_set_cols, @@ -9550,7 +9572,9 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, { numpks = transformFkeyGetPrimaryKey(pkrel, &indexOid, &fkconstraint->pk_attrs, + &fkconstraint->pk_period, pkattnum, pktypoid, + pkperiodattnums, pkperiodtypoids, opclasses); } else @@ -9558,8 +9582,14 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, numpks = transformColumnNameList(RelationGetRelid(pkrel), fkconstraint->pk_attrs, pkattnum, pktypoid); + if (is_temporal) + transformColumnNameList(RelationGetRelid(pkrel), + list_make1(fkconstraint->pk_period), + pkperiodattnums, pkperiodtypoids); + /* Look for an index matching the column list */ indexOid = transformFkeyCheckAttrs(pkrel, numpks, pkattnum, + is_temporal, pkperiodattnums[0], opclasses); } @@ -9618,187 +9648,27 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, for (i = 0; i < numpks; i++) { - Oid pktype = pktypoid[i]; - Oid fktype = fktypoid[i]; - Oid fktyped; - HeapTuple cla_ht; - Form_pg_opclass cla_tup; - Oid amid; - Oid opfamily; - Oid opcintype; - Oid pfeqop; - Oid ppeqop; - Oid ffeqop; - int16 eqstrategy; - Oid pfeqop_right; - - /* We need several fields out of the pg_opclass entry */ - cla_ht = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclasses[i])); - if (!HeapTupleIsValid(cla_ht)) - elog(ERROR, "cache lookup failed for opclass %u", opclasses[i]); - cla_tup = (Form_pg_opclass) GETSTRUCT(cla_ht); - amid = cla_tup->opcmethod; - opfamily = cla_tup->opcfamily; - 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; - - /* - * There had better be a primary equality operator for the index. - * We'll use it for PK = PK comparisons. - */ - ppeqop = get_opfamily_member(opfamily, opcintype, opcintype, - eqstrategy); - - if (!OidIsValid(ppeqop)) - elog(ERROR, "missing operator %d(%u,%u) in opfamily %u", - eqstrategy, opcintype, opcintype, opfamily); - - /* - * Are there equality operators that take exactly the FK type? Assume - * we should look through any domain here. - */ - fktyped = getBaseType(fktype); - - pfeqop = get_opfamily_member(opfamily, opcintype, fktyped, - eqstrategy); - if (OidIsValid(pfeqop)) - { - pfeqop_right = fktyped; - ffeqop = get_opfamily_member(opfamily, fktyped, fktyped, - eqstrategy); - } - else - { - /* keep compiler quiet */ - pfeqop_right = InvalidOid; - ffeqop = InvalidOid; - } - - if (!(OidIsValid(pfeqop) && OidIsValid(ffeqop))) - { - /* - * Otherwise, look for an implicit cast from the FK type to the - * opcintype, and if found, use the primary equality operator. - * This is a bit tricky because opcintype might be a polymorphic - * type such as ANYARRAY or ANYENUM; so what we have to test is - * whether the two actual column types can be concurrently cast to - * that type. (Otherwise, we'd fail to reject combinations such - * as int[] and point[].) - */ - Oid input_typeids[2]; - Oid target_typeids[2]; - - input_typeids[0] = pktype; - input_typeids[1] = fktype; - target_typeids[0] = opcintype; - target_typeids[1] = opcintype; - if (can_coerce_type(2, input_typeids, target_typeids, - COERCION_IMPLICIT)) - { - pfeqop = ffeqop = ppeqop; - pfeqop_right = opcintype; - } - } - - if (!(OidIsValid(pfeqop) && OidIsValid(ffeqop))) - ereport(ERROR, - (errcode(ERRCODE_DATATYPE_MISMATCH), - errmsg("foreign key constraint \"%s\" cannot be implemented", - fkconstraint->conname), - errdetail("Key columns \"%s\" and \"%s\" " - "are of incompatible types: %s and %s.", - strVal(list_nth(fkconstraint->fk_attrs, i)), - strVal(list_nth(fkconstraint->pk_attrs, i)), - format_type_be(fktype), - format_type_be(pktype)))); - - if (old_check_ok) - { - /* - * When a pfeqop changes, revalidate the constraint. We could - * permit intra-opfamily changes, but that adds subtle complexity - * without any concrete benefit for core types. We need not - * assess ppeqop or ffeqop, which RI_Initial_Check() does not use. - */ - old_check_ok = (pfeqop == lfirst_oid(old_pfeqop_item)); - old_pfeqop_item = lnext(fkconstraint->old_conpfeqop, - old_pfeqop_item); - } - if (old_check_ok) - { - Oid old_fktype; - Oid new_fktype; - CoercionPathType old_pathtype; - CoercionPathType new_pathtype; - Oid old_castfunc; - Oid new_castfunc; - Form_pg_attribute attr = TupleDescAttr(tab->oldDesc, - fkattnum[i] - 1); - - /* - * Identify coercion pathways from each of the old and new FK-side - * column types to the right (foreign) operand type of the pfeqop. - * We may assume that pg_constraint.conkey is not changing. - */ - old_fktype = attr->atttypid; - new_fktype = fktype; - old_pathtype = findFkeyCast(pfeqop_right, old_fktype, - &old_castfunc); - new_pathtype = findFkeyCast(pfeqop_right, new_fktype, - &new_castfunc); - - /* - * Upon a change to the cast from the FK column to its pfeqop - * operand, revalidate the constraint. For this evaluation, a - * binary coercion cast is equivalent to no cast at all. While - * type implementors should design implicit casts with an eye - * toward consistency of operations like equality, we cannot - * assume here that they have done so. - * - * A function with a polymorphic argument could change behavior - * arbitrarily in response to get_fn_expr_argtype(). Therefore, - * when the cast destination is polymorphic, we only avoid - * revalidation if the input type has not changed at all. Given - * just the core data types and operator classes, this requirement - * prevents no would-be optimizations. - * - * If the cast converts from a base type to a domain thereon, then - * that domain type must be the opcintype of the unique index. - * Necessarily, the primary key column must then be of the domain - * type. Since the constraint was previously valid, all values on - * the foreign side necessarily exist on the primary side and in - * turn conform to the domain. Consequently, we need not treat - * domains specially here. - * - * Since we require that all collations share the same notion of - * equality (which they do, because texteq reduces to bitwise - * equality), we don't compare collation here. - * - * We need not directly consider the PK type. It's necessarily - * binary coercible to the opcintype of the unique index column, - * and ri_triggers.c will only deal with PK datums in terms of - * that opcintype. Changing the opcintype also changes pfeqop. - */ - old_check_ok = (new_pathtype == old_pathtype && - new_castfunc == old_castfunc && - (!IsPolymorphicType(pfeqop_right) || - new_fktype == old_fktype)); - } + FindFKComparisonOperators( + fkconstraint, tab, i, fkattnum, + &old_check_ok, &old_pfeqop_item, + pktypoid[i], fktypoid[i], opclasses[i], + is_temporal, false, + &pfeqoperators[i], &ppeqoperators[i], &ffeqoperators[i]); + } + if (is_temporal) { + pkattnum[numpks] = pkperiodattnums[0]; + pktypoid[numpks] = pkperiodtypoids[0]; + fkattnum[numpks] = fkperiodattnums[0]; + fktypoid[numpks] = fkperiodtypoids[0]; - pfeqoperators[i] = pfeqop; - ppeqoperators[i] = ppeqop; - ffeqoperators[i] = ffeqop; + FindFKComparisonOperators( + fkconstraint, tab, numpks, fkattnum, + &old_check_ok, &old_pfeqop_item, + pkperiodtypoids[0], fkperiodtypoids[0], opclasses[numpks], + is_temporal, true, + &pfeqoperators[numpks], &ppeqoperators[numpks], &ffeqoperators[numpks]); + numfks += 1; + numpks += 1; } /* @@ -9817,7 +9687,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, @@ -9833,7 +9704,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. @@ -9918,7 +9790,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; @@ -10004,7 +9877,7 @@ addFkRecurseReferenced(List **wqueue, Constraint *fkconstraint, Relation rel, conislocal, /* islocal */ coninhcount, /* inhcount */ connoinherit, /* conNoInherit */ - false, /* conTemporal */ + is_temporal, /* conTemporal */ false); /* is_internal */ ObjectAddressSet(address, ConstraintRelationId, constrOid); @@ -10080,7 +9953,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); @@ -10138,7 +10012,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; @@ -10186,6 +10061,7 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, newcon->refrelid = RelationGetRelid(pkrel); newcon->refindid = indexOid; newcon->conid = parentConstr; + newcon->contemporal = fkconstraint->fk_period != NULL; newcon->qual = (Node *) fkconstraint; tab->constraints = lappend(tab->constraints, newcon); @@ -10303,7 +10179,7 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, false, 1, false, - false, /* conTemporal */ + is_temporal, /* conTemporal */ false); /* @@ -10334,7 +10210,8 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, old_check_ok, lockmode, insertTriggerOid, - updateTriggerOid); + updateTriggerOid, + is_temporal); table_close(partition, NoLock); } @@ -10570,7 +10447,8 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel) confdelsetcols, true, deleteTriggerOid, - updateTriggerOid); + updateTriggerOid, + constrForm->contemporal); table_close(fkRel, NoLock); ReleaseSysCache(tuple); @@ -10809,7 +10687,7 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) false, /* islocal */ 1, /* inhcount */ false, /* conNoInherit */ - false, /* conTemporal */ + constrForm->contemporal, /* conTemporal */ true); /* Set up partition dependencies for the new constraint */ @@ -10843,13 +10721,232 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) false, /* no old check exists */ AccessExclusiveLock, insertTriggerOid, - updateTriggerOid); + updateTriggerOid, + constrForm->contemporal); table_close(pkrel, NoLock); } table_close(trigrel, RowExclusiveLock); } +static void +FindFKComparisonOperators(Constraint *fkconstraint, + AlteredTableInfo *tab, + int i, + int16 *fkattnum, + 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; + HeapTuple cla_ht; + Form_pg_opclass cla_tup; + Oid amid; + Oid opfamily; + Oid opcintype; + Oid pfeqop; + Oid ppeqop; + Oid ffeqop; + int16 eqstrategy; + Oid pfeqop_right; + + /* We need several fields out of the pg_opclass entry */ + cla_ht = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclass)); + if (!HeapTupleIsValid(cla_ht)) + elog(ERROR, "cache lookup failed for opclass %u", opclass); + cla_tup = (Form_pg_opclass) GETSTRUCT(cla_ht); + amid = cla_tup->opcmethod; + opfamily = cla_tup->opcfamily; + opcintype = cla_tup->opcintype; + ReleaseSysCache(cla_ht); + + if (is_temporal) + { + if (amid != GIST_AM_OID) + elog(ERROR, "only GiST indexes are supported for temporal foreign keys"); + eqstrategy = for_overlaps ? RTOverlapStrategyNumber : RTEqualStrategyNumber; + } + 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. + * We'll use it for PK = PK comparisons. + */ + ppeqop = get_opfamily_member(opfamily, opcintype, opcintype, + eqstrategy); + + if (!OidIsValid(ppeqop)) + elog(ERROR, "missing operator %d(%u,%u) in opfamily %u", + eqstrategy, opcintype, opcintype, opfamily); + + /* + * Are there equality operators that take exactly the FK type? Assume + * we should look through any domain here. + */ + fktyped = getBaseType(fktype); + + pfeqop = get_opfamily_member(opfamily, opcintype, fktyped, + eqstrategy); + if (OidIsValid(pfeqop)) + { + pfeqop_right = fktyped; + ffeqop = get_opfamily_member(opfamily, fktyped, fktyped, + eqstrategy); + } + else + { + /* keep compiler quiet */ + pfeqop_right = InvalidOid; + ffeqop = InvalidOid; + } + + if (!(OidIsValid(pfeqop) && OidIsValid(ffeqop))) + { + /* + * Otherwise, look for an implicit cast from the FK type to the + * opcintype, and if found, use the primary equality operator. + * This is a bit tricky because opcintype might be a polymorphic + * type such as ANYARRAY or ANYENUM; so what we have to test is + * whether the two actual column types can be concurrently cast to + * that type. (Otherwise, we'd fail to reject combinations such + * as int[] and point[].) + */ + Oid input_typeids[2]; + Oid target_typeids[2]; + + input_typeids[0] = pktype; + input_typeids[1] = fktype; + target_typeids[0] = opcintype; + target_typeids[1] = opcintype; + if (can_coerce_type(2, input_typeids, target_typeids, + COERCION_IMPLICIT)) + { + pfeqop = ffeqop = ppeqop; + pfeqop_right = opcintype; + } + } + + if (!(OidIsValid(pfeqop) && OidIsValid(ffeqop))) + { + char *fkattr_name; + char *pkattr_name; + + if (for_overlaps) + { + fkattr_name = strVal(fkconstraint->fk_period); + pkattr_name = strVal(fkconstraint->pk_period); + } + else + { + fkattr_name = strVal(list_nth(fkconstraint->fk_attrs, i)); + pkattr_name = strVal(list_nth(fkconstraint->pk_attrs, i)); + } + + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("foreign key constraint \"%s\" cannot be implemented", + fkconstraint->conname), + errdetail("Key columns \"%s\" and \"%s\" " + "are of incompatible types: %s and %s.", + fkattr_name, + pkattr_name, + format_type_be(fktype), + format_type_be(pktype)))); + } + + if (*old_check_ok) + { + /* + * When a pfeqop changes, revalidate the constraint. We could + * permit intra-opfamily changes, but that adds subtle complexity + * without any concrete benefit for core types. We need not + * assess ppeqop or ffeqop, which RI_Initial_Check() does not use. + */ + *old_check_ok = (pfeqop == lfirst_oid(*old_pfeqop_item)); + *old_pfeqop_item = lnext(fkconstraint->old_conpfeqop, + *old_pfeqop_item); + } + if (*old_check_ok) + { + Oid old_fktype; + Oid new_fktype; + CoercionPathType old_pathtype; + CoercionPathType new_pathtype; + Oid old_castfunc; + Oid new_castfunc; + Form_pg_attribute attr = TupleDescAttr(tab->oldDesc, + fkattnum[i] - 1); + + /* + * Identify coercion pathways from each of the old and new FK-side + * column types to the right (foreign) operand type of the pfeqop. + * We may assume that pg_constraint.conkey is not changing. + */ + old_fktype = attr->atttypid; + new_fktype = fktype; + old_pathtype = findFkeyCast(pfeqop_right, old_fktype, + &old_castfunc); + new_pathtype = findFkeyCast(pfeqop_right, new_fktype, + &new_castfunc); + + /* + * Upon a change to the cast from the FK column to its pfeqop + * operand, revalidate the constraint. For this evaluation, a + * binary coercion cast is equivalent to no cast at all. While + * type implementors should design implicit casts with an eye + * toward consistency of operations like equality, we cannot + * assume here that they have done so. + * + * A function with a polymorphic argument could change behavior + * arbitrarily in response to get_fn_expr_argtype(). Therefore, + * when the cast destination is polymorphic, we only avoid + * revalidation if the input type has not changed at all. Given + * just the core data types and operator classes, this requirement + * prevents no would-be optimizations. + * + * If the cast converts from a base type to a domain thereon, then + * that domain type must be the opcintype of the unique index. + * Necessarily, the primary key column must then be of the domain + * type. Since the constraint was previously valid, all values on + * the foreign side necessarily exist on the primary side and in + * turn conform to the domain. Consequently, we need not treat + * domains specially here. + * + * Since we require that all collations share the same notion of + * equality (which they do, because texteq reduces to bitwise + * equality), we don't compare collation here. + * + * We need not directly consider the PK type. It's necessarily + * binary coercible to the opcintype of the unique index column, + * and ri_triggers.c will only deal with PK datums in terms of + * that opcintype. Changing the opcintype also changes pfeqop. + */ + *old_check_ok = (new_pathtype == old_pathtype && + new_castfunc == old_castfunc && + (!IsPolymorphicType(pfeqop_right) || + new_fktype == old_fktype)); + + } + + *pfeqopOut = pfeqop; + *ppeqopOut = ppeqop; + *ffeqopOut = ffeqop; +} + /* * When the parent of a partition receives [the referencing side of] a foreign * key, we must propagate that foreign key to the partition. However, the @@ -11347,7 +11444,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_TRI_FKEY_NOACTION_DEL && + tgform->tgfoid != F_TRI_FKEY_NOACTION_UPD && + tgform->tgfoid != F_TRI_FKEY_CHECK_INS && + tgform->tgfoid != F_TRI_FKEY_CHECK_UPD) continue; tgCopyTuple = heap_copytuple(tgtuple); @@ -11595,7 +11696,6 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName, return address; } - /* * transformColumnNameList - transform list of column names * @@ -11663,7 +11763,9 @@ transformColumnNameList(Oid relId, List *colList, static int transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid, List **attnamelist, + Node **pk_period, int16 *attnums, Oid *atttypids, + int16 *periodattnums, Oid *periodatttypids, Oid *opclasses) { List *indexoidlist; @@ -11728,36 +11830,55 @@ 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++) { int pkattno = indexStruct->indkey.values[i]; - attnums[i] = pkattno; - atttypids[i] = attnumTypeId(pkrel, pkattno); - opclasses[i] = indclass->values[i]; - *attnamelist = lappend(*attnamelist, - makeString(pstrdup(NameStr(*attnumAttName(pkrel, pkattno))))); + if (i == indexStruct->indnkeyatts - 1 && indexStruct->indisexclusion) + { + /* we have a range */ + /* The caller will set attnums[i] */ + periodattnums[0] = pkattno; + periodatttypids[0] = attnumTypeId(pkrel, pkattno); + opclasses[i] = indclass->values[i]; + + Assert(*pk_period == NULL); + *pk_period = (Node *) makeString(pstrdup(NameStr(*attnumAttName(pkrel, pkattno)))); + } + else + { + attnums[i] = pkattno; + atttypids[i] = attnumTypeId(pkrel, pkattno); + opclasses[i] = indclass->values[i]; + *attnamelist = lappend(*attnamelist, + makeString(pstrdup(NameStr(*attnumAttName(pkrel, pkattno))))); + } } ReleaseSysCache(indexTuple); - return i; + if (indexStruct->indisexclusion) return i - 1; + else return i; } /* * transformFkeyCheckAttrs - * * Make sure that the attributes of a referenced table belong to a unique - * (or primary key) constraint. Return the OID of the index supporting - * the constraint, as well as the opclasses associated with the index + * (or primary key) constraint. Or if this is a temporal foreign key + * the primary key should be an exclusion constraint instead. + * Return the OID of the index supporting the constraint, + * as well as the opclasses associated with the index * columns. */ static Oid transformFkeyCheckAttrs(Relation pkrel, int numattrs, int16 *attnums, + bool is_temporal, int16 periodattnum, Oid *opclasses) /* output parameter */ { Oid indexoid = InvalidOid; @@ -11784,6 +11905,10 @@ transformFkeyCheckAttrs(Relation pkrel, (errcode(ERRCODE_INVALID_FOREIGN_KEY), errmsg("foreign key referenced-columns list must not contain duplicates"))); } + if (is_temporal && attnums[i] == periodattnum) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("foreign key referenced-columns list must not contain duplicates"))); } /* @@ -11805,12 +11930,16 @@ transformFkeyCheckAttrs(Relation pkrel, indexStruct = (Form_pg_index) GETSTRUCT(indexTuple); /* - * Must have the right number of columns; must be unique and not a - * partial index; forget it if there are any expressions, too. Invalid - * indexes are out as well. + * 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 && + if ((is_temporal + ? (indexStruct->indnkeyatts == numattrs + 1 && + indexStruct->indisexclusion) + : (indexStruct->indnkeyatts == numattrs && + indexStruct->indisunique)) && indexStruct->indisvalid && heap_attisnull(indexTuple, Anum_pg_index_indpred, NULL) && heap_attisnull(indexTuple, Anum_pg_index_indexprs, NULL)) @@ -11848,6 +11977,19 @@ transformFkeyCheckAttrs(Relation pkrel, if (!found) break; } + if (found && is_temporal) + { + found = false; + for (j = 0; j < numattrs + 1; j++) + { + if (periodattnum == indexStruct->indkey.values[j]) + { + opclasses[numattrs] = indclass->values[j]; + found = true; + break; + } + } + } /* * Refuse to use a deferrable unique/primary key. This is per SQL @@ -11957,7 +12099,8 @@ validateForeignKeyConstraint(char *conname, Relation rel, Relation pkrel, Oid pkindOid, - Oid constraintOid) + Oid constraintOid, + bool temporal) { TupleTableSlot *slot; TableScanDesc scan; @@ -11986,8 +12129,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; /* @@ -12056,6 +12201,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint, { ObjectAddress trigAddress; CreateTrigStmt *fk_trigger; + bool is_temporal = fkconstraint->fk_period; /* * Note: for a self-referential FK (referencing and referenced tables are @@ -12075,12 +12221,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("TRI_FKey_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("TRI_FKey_check_upd"); + else + fk_trigger->funcname = SystemFuncName("RI_FKey_check_upd"); fk_trigger->events = TRIGGER_TYPE_UPDATE; } @@ -12138,37 +12290,68 @@ 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_period != NULL) { - 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("TRI_FKey_noaction_del"); + break; + case FKCONSTR_ACTION_RESTRICT: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("TRI_FKey_restrict_del"); + break; + case FKCONSTR_ACTION_CASCADE: + case FKCONSTR_ACTION_SETNULL: + case FKCONSTR_ACTION_SETDEFAULT: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("action not supported for temporal foreign keys"))); + 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, @@ -12198,37 +12381,68 @@ 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_period != NULL) { - 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("TRI_FKey_noaction_upd"); + break; + case FKCONSTR_ACTION_RESTRICT: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("TRI_FKey_restrict_upd"); + break; + case FKCONSTR_ACTION_CASCADE: + case FKCONSTR_ACTION_SETNULL: + case FKCONSTR_ACTION_SETDEFAULT: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("action not supported for temporal foreign keys"))); + 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 3ea4acb74d..7d22319d40 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -523,11 +523,12 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type TableElement TypedTableElement ConstraintElem TableFuncElement %type columnDef columnOptions %type def_elem reloption_elem old_aggr_elem operator_def_elem -%type def_arg columnElem without_overlaps_clause +%type def_arg columnElem without_overlaps_clause optionalPeriodName 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 %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 @@ -4186,21 +4187,23 @@ 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", + n->fk_period = $5; + n->pk_attrs = linitial($9); + n->pk_period = lsecond($9); + 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); @@ -4228,6 +4231,16 @@ without_overlaps_clause: | /*EMPTY*/ { $$ = NULL; } ; +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); @@ -17515,6 +17528,7 @@ reserved_keyword: | ONLY | OR | ORDER + | PERIOD | PLACING | PRIMARY | REFERENCES @@ -17824,6 +17838,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 6945d99b3d..365b844bf3 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_range.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,6 +120,7 @@ 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 */ @@ -200,8 +203,9 @@ static int ri_NullCheck(TupleDesc tupDesc, TupleTableSlot *slot, static void ri_BuildQueryKey(RI_QueryKey *key, const RI_ConstraintInfo *riinfo, int32 constr_queryno); -static bool ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot, +static bool ri_KeysStable(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot, const RI_ConstraintInfo *riinfo, bool rel_is_pk); +static bool ri_RangeAttributeNeedsCheck(bool rel_is_pk, Datum oldvalue, Datum newvalue); static bool ri_AttributesEqual(Oid eq_opr, Oid typeid, Datum oldvalue, Datum newvalue); @@ -361,26 +365,57 @@ RI_FKey_check(TriggerData *trigdata) /* ---------- * The query string built is - * SELECT 1 FROM [ONLY] x WHERE pkatt1 = $1 [AND ...] - * FOR KEY SHARE OF x + * SELECT 1 + * FROM [ONLY] x WHERE pkatt1 = $1 [AND ...] + * 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++) { - Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]); - Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]); + Oid pk_type; + Oid fk_type; + pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]); quoteOneName(attname, RIAttName(pk_rel, riinfo->pk_attnums[i])); + + fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]); + sprintf(paramname, "$%d", i + 1); ri_GenerateQual(&querybuf, querysep, attname, pk_type, @@ -390,6 +425,8 @@ RI_FKey_check(TriggerData *trigdata) queryoids[i] = fk_type; } appendStringInfoString(&querybuf, " FOR KEY SHARE OF x"); + if (riinfo->temporal) + appendStringInfo(&querybuf, ") x1 HAVING $%d <@ pg_catalog.range_agg(x1.r)", riinfo->nkeys); /* Prepare and save the plan */ qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids, @@ -497,21 +534,49 @@ 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++) { - Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]); + Oid pk_type; + pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]); quoteOneName(attname, RIAttName(pk_rel, riinfo->pk_attnums[i])); + sprintf(paramname, "$%d", i + 1); ri_GenerateQual(&querybuf, querysep, attname, pk_type, @@ -521,6 +586,8 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel, queryoids[i] = pk_type; } appendStringInfoString(&querybuf, " FOR KEY SHARE OF x"); + if (riinfo->temporal) + appendStringInfo(&querybuf, ") x1 HAVING $%d <@ pg_catalog.range_agg(x1.r)", riinfo->nkeys); /* Prepare and save the plan */ qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids, @@ -695,10 +762,16 @@ ri_restrict(TriggerData *trigdata, bool is_no_action) querysep = "WHERE"; for (int i = 0; i < riinfo->nkeys; i++) { - Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]); - Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]); - Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]); - Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]); + Oid pk_type; + Oid fk_type; + Oid pk_coll; + Oid fk_coll; + + pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]); + pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]); + + fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]); + fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]); quoteOneName(attname, RIAttName(fk_rel, riinfo->fk_attnums[i])); @@ -1213,6 +1286,126 @@ ri_set(TriggerData *trigdata, bool is_set_null, int tgkind) } } +/* ---------- + * TRI_FKey_check_ins - + * + * Check temporal foreign key existence at insert event on FK table. + * ---------- + */ +Datum +TRI_FKey_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); +} + + +/* ---------- + * TRI_FKey_check_upd - + * + * Check temporal foreign key existence at update event on FK table. + * ---------- + */ +Datum +TRI_FKey_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); +} + + +/* ---------- + * TRI_FKey_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 +TRI_FKey_noaction_del(PG_FUNCTION_ARGS) +{ + /* + * Check that this is a valid trigger call on the right time and event. + */ + ri_CheckTrigger(fcinfo, "TRI_FKey_noaction_del", RI_TRIGTYPE_DELETE); + + /* + * Share code with RESTRICT/UPDATE cases. + */ + return ri_restrict((TriggerData *) fcinfo->context, true); +} + +/* + * TRI_FKey_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 +TRI_FKey_restrict_del(PG_FUNCTION_ARGS) +{ + /* Check that this is a valid trigger call on the right time and event. */ + ri_CheckTrigger(fcinfo, "TRI_FKey_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 +TRI_FKey_noaction_upd(PG_FUNCTION_ARGS) +{ + /* Check that this is a valid trigger call on the right time and event. */ + ri_CheckTrigger(fcinfo, "TRI_FKey_noaction_upd", RI_TRIGTYPE_UPDATE); + + /* Share code with RESTRICT/DELETE cases. */ + return ri_restrict((TriggerData *) fcinfo->context, true); +} + +/* + * TRI_FKey_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 +TRI_FKey_restrict_upd(PG_FUNCTION_ARGS) +{ + /* Check that this is a valid trigger call on the right time and event. */ + ri_CheckTrigger(fcinfo, "TRI_FKey_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 - @@ -1241,7 +1434,7 @@ RI_FKey_pk_upd_check_required(Trigger *trigger, Relation pk_rel, return false; /* If all old and new key values are equal, no check is needed */ - if (newslot && ri_KeysEqual(pk_rel, oldslot, newslot, riinfo, true)) + if (newslot && ri_KeysStable(pk_rel, oldslot, newslot, riinfo, true)) return false; /* Else we need to fire the trigger. */ @@ -1340,7 +1533,7 @@ RI_FKey_fk_upd_check_required(Trigger *trigger, Relation fk_rel, return true; /* If all old and new key values are equal, no check is needed */ - if (ri_KeysEqual(fk_rel, oldslot, newslot, riinfo, false)) + if (ri_KeysStable(fk_rel, oldslot, newslot, riinfo, false)) return false; /* Else we need to fire the trigger. */ @@ -1488,15 +1681,17 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel) sep = "("; for (int i = 0; i < riinfo->nkeys; i++) { - Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]); - Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]); - Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]); - Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]); + Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]); + Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]); + Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]); + Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]); quoteOneName(pkattname + 3, RIAttName(pk_rel, riinfo->pk_attnums[i])); + quoteOneName(fkattname + 3, RIAttName(fk_rel, riinfo->fk_attnums[i])); + ri_GenerateQual(&querybuf, sep, pkattname, pk_type, riinfo->pf_eq_oprs[i], @@ -2137,6 +2332,7 @@ ri_LoadConstraintInfo(Oid constraintOid) riinfo = (RI_ConstraintInfo *) hash_search(ri_constraint_cache, &constraintOid, HASH_ENTER, &found); + if (!found) riinfo->valid = false; else if (riinfo->valid) @@ -2171,6 +2367,7 @@ ri_LoadConstraintInfo(Oid constraintOid) riinfo->confupdtype = conForm->confupdtype; riinfo->confdeltype = conForm->confdeltype; riinfo->confmatchtype = conForm->confmatchtype; + riinfo->temporal = conForm->contemporal; DeconstructFkConstraintRow(tup, &riinfo->nkeys, @@ -2791,9 +2988,12 @@ ri_HashPreparedPlan(RI_QueryKey *key, SPIPlanPtr plan) /* - * ri_KeysEqual - + * ri_KeysStable - * - * 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. * * 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 @@ -2801,7 +3001,7 @@ ri_HashPreparedPlan(RI_QueryKey *key, SPIPlanPtr plan) * previously found at least one of the rows to contain no nulls. */ static bool -ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot, +ri_KeysStable(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot, const RI_ConstraintInfo *riinfo, bool rel_is_pk) { const int16 *attnums; @@ -2834,35 +3034,86 @@ ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot, if (rel_is_pk) { - /* - * If we are looking at the PK table, then do a bytewise - * comparison. We must propagate PK changes if the value is - * changed to one that "looks" different but would compare as - * equal using the equality operator. This only makes a - * difference for ON UPDATE CASCADE, but for consistency we treat - * all changes to the PK the same. - */ - Form_pg_attribute att = TupleDescAttr(oldslot->tts_tupleDescriptor, attnums[i] - 1); + if (riinfo->temporal && i == riinfo->nkeys - 1) + { + if (ri_RangeAttributeNeedsCheck(true, oldvalue, newvalue)) + return false; + } + else + { + /* + * If we are looking at the PK table, then do a bytewise + * comparison. We must propagate PK changes if the value is + * changed to one that "looks" different but would compare as + * equal using the equality operator. This only makes a + * difference for ON UPDATE CASCADE, but for consistency we treat + * all changes to the PK the same. + */ + Form_pg_attribute att = TupleDescAttr(oldslot->tts_tupleDescriptor, attnums[i] - 1); - if (!datum_image_eq(oldvalue, newvalue, att->attbyval, att->attlen)) - return false; + if (!datum_image_eq(oldvalue, newvalue, att->attbyval, att->attlen)) + return false; + } } else { - /* - * 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)) - return false; + if (riinfo->temporal && i == riinfo->nkeys - 1) + { + if (ri_RangeAttributeNeedsCheck(false, oldvalue, newvalue)) + return false; + } + else + { + /* + * 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)) + return false; + } } } return true; } +/* + * ri_RangeAttributeNeedsCheck - + * + * Compare old and new values, and return true if we need to check the FK. + * + * NB: we have already checked that neither value is null. + */ +static bool +ri_RangeAttributeNeedsCheck(bool rel_is_pk, Datum oldvalue, Datum newvalue) +{ + RangeType *oldrange, *newrange; + Oid oldrngtype, newrngtype; + TypeCacheEntry *typcache; + + oldrange = DatumGetRangeTypeP(oldvalue); + newrange = DatumGetRangeTypeP(newvalue); + oldrngtype = RangeTypeGetOid(oldrange); + newrngtype = RangeTypeGetOid(newrange); + + if (oldrngtype != newrngtype) + elog(ERROR, "range types are inconsistent"); + + typcache = lookup_type_cache(oldrngtype, TYPECACHE_RANGE_INFO); + if (typcache->rngelemtype == NULL) + elog(ERROR, "type %u is not a range type", oldrngtype); + + if (rel_is_pk) + /* If the PK's range shrunk, a conflict is possible. */ + return !range_eq_internal(typcache, oldrange, newrange) && + !range_contains_internal(typcache, newrange, oldrange); + else + /* If the FK's range grew, a conflict is possible. */ + return !range_eq_internal(typcache, oldrange, newrange) && + range_contains_internal(typcache, newrange, oldrange); +} /* * ri_AttributesEqual - @@ -3021,10 +3272,16 @@ 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_TRI_FKEY_RESTRICT_DEL: + case F_TRI_FKEY_RESTRICT_UPD: + case F_TRI_FKEY_NOACTION_DEL: + case F_TRI_FKEY_NOACTION_UPD: return RI_TRIGGER_PK; case F_RI_FKEY_CHECK_INS: case F_RI_FKEY_CHECK_UPD: + case F_TRI_FKEY_CHECK_INS: + case F_TRI_FKEY_CHECK_UPD: return RI_TRIGGER_FK; } diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 7aa5b62ab8..99bbe6d97e 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, Oid indexId, - bool withoutOverlaps, StringInfo buf); + bool withoutOverlaps, 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, @@ -2247,7 +2247,10 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, val = SysCacheGetAttrNotNull(CONSTROID, tup, Anum_pg_constraint_conkey); - decompile_column_index_array(val, conForm->conrelid, conForm->conindid, false, &buf); + /* If it is a temporal foreign key then it uses PERIOD. */ + decompile_column_index_array(val, conForm->conrelid, + conForm->conindid, false, + conForm->contemporal, &buf); /* add foreign relation name */ appendStringInfo(&buf, ") REFERENCES %s(", @@ -2258,7 +2261,9 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, val = SysCacheGetAttrNotNull(CONSTROID, tup, Anum_pg_constraint_confkey); - decompile_column_index_array(val, conForm->confrelid, conForm->conindid, false, &buf); + decompile_column_index_array(val, conForm->confrelid, + conForm->conindid, false, + conForm->contemporal, &buf); appendStringInfoChar(&buf, ')'); @@ -2344,7 +2349,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, if (!isnull) { appendStringInfoString(&buf, " ("); - decompile_column_index_array(val, conForm->conrelid, InvalidOid, false, &buf); + decompile_column_index_array(val, conForm->conrelid, InvalidOid, false, false, &buf); appendStringInfoChar(&buf, ')'); } @@ -2387,7 +2392,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, indexId = conForm->conindid; SysCacheGetAttr(CONSTROID, tup, Anum_pg_constraint_conexclop, &isnull); - keyatts = decompile_column_index_array(val, conForm->conrelid, indexId, !isnull, &buf); + keyatts = decompile_column_index_array(val, conForm->conrelid, indexId, !isnull, false, &buf); appendStringInfoChar(&buf, ')'); @@ -2583,7 +2588,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, */ static int decompile_column_index_array(Datum column_index_array, Oid relId, Oid indexId, - bool withoutOverlaps, StringInfo buf) + bool withoutOverlaps, bool withPeriod, StringInfo buf) { Datum *keys; int nKeys; @@ -2604,6 +2609,8 @@ decompile_column_index_array(Datum column_index_array, Oid relId, Oid indexId, appendStringInfoString(buf, quote_identifier(colName)); else if (withoutOverlaps && j == nKeys - 1) appendStringInfo(buf, ", %s WITHOUT OVERLAPS", quote_identifier(colName)); + else if (withPeriod && j == nKeys - 1) + appendStringInfo(buf, ", PERIOD %s", quote_identifier(colName)); else appendStringInfo(buf, ", %s", quote_identifier(colName)); } diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h index 786a9ca904..7ca0534b48 100644 --- a/src/include/catalog/pg_constraint.h +++ b/src/include/catalog/pg_constraint.h @@ -127,19 +127,19 @@ CATALOG(pg_constraint,2606,ConstraintRelationId) int16 confkey[1]; /* - * If a foreign key, the OIDs of the PK = FK equality operators for each + * If a foreign key, the OIDs of the PK = FK comparison 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 + * If a foreign key, the OIDs of the PK = PK comparison 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 + * If a foreign key, the OIDs of the FK = FK comparison operators for each * column of the constraint (i.e., equality for the referencing columns) */ Oid conffeqop[1] BKI_LOOKUP(pg_operator); @@ -180,7 +180,7 @@ DECLARE_INDEX(pg_constraint_conparentid_index, 2579, ConstraintParentIndexId, pg /* conkey can contain zero (InvalidAttrNumber) if a whole-row Var is used */ DECLARE_ARRAY_FOREIGN_KEY_OPT((conrelid, conkey), pg_attribute, (attrelid, attnum)); -DECLARE_ARRAY_FOREIGN_KEY((confrelid, confkey), pg_attribute, (attrelid, attnum)); +DECLARE_ARRAY_FOREIGN_KEY_OPT((confrelid, confkey), pg_attribute, (attrelid, attnum)); #ifdef EXPOSE_TO_CLIENT_CODE diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 9805bc6118..3132468a9c 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -3942,6 +3942,30 @@ prorettype => 'trigger', proargtypes => '', prosrc => 'RI_FKey_noaction_upd' }, +# Temporal referential integrity constraint triggers +{ oid => '6122', descr => 'temporal referential integrity FOREIGN KEY ... REFERENCES', + proname => 'TRI_FKey_check_ins', provolatile => 'v', prorettype => 'trigger', + proargtypes => '', prosrc => 'TRI_FKey_check_ins' }, +{ oid => '6123', descr => 'temporal referential integrity FOREIGN KEY ... REFERENCES', + proname => 'TRI_FKey_check_upd', provolatile => 'v', prorettype => 'trigger', + proargtypes => '', prosrc => 'TRI_FKey_check_upd' }, +{ oid => '6126', descr => 'temporal referential integrity ON DELETE RESTRICT', + proname => 'TRI_FKey_restrict_del', provolatile => 'v', + prorettype => 'trigger', proargtypes => '', + prosrc => 'TRI_FKey_restrict_del' }, +{ oid => '6127', descr => 'temporal referential integrity ON UPDATE RESTRICT', + proname => 'TRI_FKey_restrict_upd', provolatile => 'v', + prorettype => 'trigger', proargtypes => '', + prosrc => 'TRI_FKey_restrict_upd' }, +{ oid => '6132', descr => 'temporal referential integrity ON DELETE NO ACTION', + proname => 'TRI_FKey_noaction_del', provolatile => 'v', + prorettype => 'trigger', proargtypes => '', + prosrc => 'TRI_FKey_noaction_del' }, +{ oid => '6133', descr => 'temporal referential integrity ON UPDATE NO ACTION', + proname => 'TRI_FKey_noaction_upd', provolatile => 'v', + prorettype => 'trigger', proargtypes => '', + prosrc => 'TRI_FKey_noaction_upd' }, + { oid => '1666', proname => 'varbiteq', proleakproof => 't', prorettype => 'bool', proargtypes => 'varbit varbit', prosrc => 'biteq' }, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 0747b54b26..479b5ffa35 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2632,7 +2632,9 @@ typedef struct Constraint /* Fields used for FOREIGN KEY constraints: */ RangeVar *pktable; /* Primary key table */ List *fk_attrs; /* Attributes of foreign key */ + Node *fk_period; /* String node naming Period or range column */ List *pk_attrs; /* Corresponding attrs in PK table */ + Node *pk_period; /* String node naming Period or range column */ 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 5984dcfa4b..ccf5bf8aa6 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/without_overlaps.out b/src/test/regress/expected/without_overlaps.out index 5e126df071..1749945e71 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -357,3 +357,464 @@ 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 +-- +-- 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. +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 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 +-- +-- 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); +-- 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; +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')); +INSERT INTO temporal_rng VALUES ('[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 DELETE RESTRICT; +-- a PK update that succeeds because the numeric id isn't referenced: +INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01')); +UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') WHERE id = '[5,5]'; +-- a PK update that succeeds even though the numeric id is referenced because the range isn't: +DELETE FROM temporal_rng WHERE id = '[5,5]'; +INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01')); +INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_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')); +INSERT INTO temporal_rng VALUES ('[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')); +INSERT INTO temporal_rng VALUES ('[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: action not supported for temporal foreign keys +-- test FK parent updates SET NULL +INSERT INTO temporal_rng VALUES ('[9,9]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01'), '[9,9]'); +ALTER TABLE temporal_fk_rng2rng + DROP CONSTRAINT temporal_fk_rng2rng_fk, + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng + ON DELETE SET NULL ON UPDATE SET NULL; +ERROR: action not supported for temporal foreign keys +-- test FK parent updates SET DEFAULT +INSERT INTO temporal_rng VALUES ('[-1,-1]', tsrange(null, null)); +INSERT INTO temporal_rng VALUES ('[12,12]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[8,8]', tsrange('2018-01-01', '2021-01-01'), '[12,12]'); +ALTER TABLE temporal_fk_rng2rng + ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', + DROP CONSTRAINT temporal_fk_rng2rng_fk, + 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: action not supported for temporal foreign keys +-- 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_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_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_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_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: action not supported for temporal foreign keys +-- partitioned FK parent deletes CASCADE +-- partitioned FK parent updates SET NULL +ALTER TABLE temporal_partitioned_fk_rng2rng + DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, + 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: action not supported for temporal foreign keys +-- partitioned FK parent deletes SET NULL +-- partitioned FK parent updates SET DEFAULT +ALTER TABLE temporal_partitioned_fk_rng2rng + ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', + 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: action not supported for temporal foreign keys +-- partitioned FK parent deletes SET DEFAULT +DROP TABLE temporal_partitioned_fk_rng2rng; +DROP TABLE temporal_partitioned_rng; diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql index 3c3236618c..13ce7313c5 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -260,3 +260,465 @@ 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 +-- + +-- 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) +); + +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 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) +); + +-- +-- 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); + +-- 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; +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')); +INSERT INTO temporal_rng VALUES ('[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 DELETE RESTRICT; +-- a PK update that succeeds because the numeric id isn't referenced: +INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01')); +UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') WHERE id = '[5,5]'; +-- a PK update that succeeds even though the numeric id is referenced because the range isn't: +DELETE FROM temporal_rng WHERE id = '[5,5]'; +INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01')); +INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_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')); +INSERT INTO temporal_rng VALUES ('[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')); +INSERT INTO temporal_rng VALUES ('[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; + +-- 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.25.1