From c8b31830f6a8716fd46c2ab5130c925d1048761c Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Sun, 27 Jun 2021 12:58:51 -0700 Subject: [PATCH v20 4/8] 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. --- .../btree_gist/expected/without_overlaps.out | 27 + contrib/btree_gist/sql/without_overlaps.sql | 12 + doc/src/sgml/ref/create_table.sgml | 48 +- src/backend/commands/indexcmds.c | 26 +- src/backend/commands/tablecmds.c | 864 ++++++++++++------ src/backend/nodes/outfuncs.c | 2 + src/backend/nodes/readfuncs.c | 2 + src/backend/parser/gram.y | 37 +- src/backend/utils/adt/ri_triggers.c | 404 +++++++- src/backend/utils/adt/ruleutils.c | 19 +- src/backend/utils/cache/lsyscache.c | 31 + src/include/catalog/pg_constraint.h | 8 +- src/include/catalog/pg_proc.dat | 24 + src/include/commands/defrem.h | 6 +- src/include/commands/tablecmds.h | 3 + src/include/nodes/parsenodes.h | 6 + src/include/parser/kwlist.h | 1 + src/include/utils/lsyscache.h | 1 + .../regress/expected/without_overlaps.out | 551 +++++++++++ src/test/regress/sql/without_overlaps.sql | 526 +++++++++++ 20 files changed, 2245 insertions(+), 353 deletions(-) diff --git a/contrib/btree_gist/expected/without_overlaps.out b/contrib/btree_gist/expected/without_overlaps.out index 069272688f..d11601e570 100644 --- a/contrib/btree_gist/expected/without_overlaps.out +++ b/contrib/btree_gist/expected/without_overlaps.out @@ -29,3 +29,30 @@ SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'te CREATE UNIQUE INDEX temporal_rng_pk ON temporal_rng USING gist (id, valid_at) (1 row) +-- Foreign key +CREATE TABLE temporal_fk_rng2rng ( + id integer, + valid_at tsrange, + parent_id integer, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng (id, PERIOD valid_at) +); +\d temporal_fk_rng2rng + Table "public.temporal_fk_rng2rng" + Column | Type | Collation | Nullable | Default +-----------+---------+-----------+----------+--------- + id | integer | | not null | + valid_at | tsrange | | not null | + parent_id | integer | | | +Indexes: + "temporal_fk_rng2rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +Foreign-key constraints: + "temporal_fk_rng2rng_fk" FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng(id, PERIOD valid_at) + +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_rng2rng_fk'; + pg_get_constraintdef +--------------------------------------------------------------------------------------- + FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng(id, PERIOD valid_at) +(1 row) + diff --git a/contrib/btree_gist/sql/without_overlaps.sql b/contrib/btree_gist/sql/without_overlaps.sql index 98665dbb9e..036ee0a760 100644 --- a/contrib/btree_gist/sql/without_overlaps.sql +++ b/contrib/btree_gist/sql/without_overlaps.sql @@ -11,3 +11,15 @@ CREATE TABLE temporal_rng ( \d temporal_rng SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng_pk'; SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng_pk'; + +-- Foreign key +CREATE TABLE temporal_fk_rng2rng ( + id integer, + valid_at tsrange, + parent_id integer, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng (id, PERIOD valid_at) +); +\d temporal_fk_rng2rng +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_rng2rng_fk'; diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index a0bd2b8adc..b5f48525de 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -81,7 +81,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | - FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] + FOREIGN KEY ( column_name [, ... ] [, PERIOD 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 ] @@ -1146,8 +1146,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 ] @@ -1158,11 +1158,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 @@ -1236,6 +1254,12 @@ WITH ( MODULUS numeric_literal, REM values of the referencing column(s) to the new values of the referenced columns, respectively. + + + In a temporal foreign key, the delete/update will use + FOR PORTION OF semantics to constrain the + effect to the bounds being deleted/updated in the referenced row. + @@ -1247,6 +1271,13 @@ 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 being + deleted/updated in the referenced row. The column maked with + PERIOD will not be set to null. + @@ -1260,6 +1291,13 @@ WITH ( MODULUS numeric_literal, REM (There must be a row in the referenced table matching the default values, if they are not null, or the operation will fail.) + + + In a temporal foreign key, the change will use FOR PORTION + OF semantics to constrain the effect to the bounds being + deleted/updated in the referenced row. The column marked with + PERIOD with not be set to a default value. + diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 8c06aa0956..8d48b79cea 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -2189,8 +2189,9 @@ ComputeIndexAttrs(IndexInfo *indexInfo, opname = "equals"; } GetOperatorFromCanonicalStrategy(opclassOids[attn], - atttype, + InvalidOid, opname, + "WITHOUT OVERLAPS constraint", &opid, &strat); indexInfo->ii_ExclusionOps[attn] = opid; @@ -2431,8 +2432,9 @@ GetDefaultOpClass(Oid type_id, Oid am_id) * GetOperatorFromCanonicalStrategy * * opclass - the opclass to use - * atttype - the type to ask about + * rhstype - the type for the right-hand side * opname - used to build error messages + * context - used to build error messages * opid - holds the operator we found * strat - holds the input and output strategy number * @@ -2445,11 +2447,14 @@ GetDefaultOpClass(Oid type_id, Oid am_id) * function to translate from the well-known number * to the internal value. If the function isn't defined * or it gives no result, we retrun InvalidStrategy. + * + * rhstype can be InvalidOid to use the opcintype instead. */ void GetOperatorFromCanonicalStrategy(Oid opclass, - Oid atttype, + Oid rhstype, const char *opname, + const char *context, Oid *opid, StrategyNumber *strat) { @@ -2473,12 +2478,19 @@ GetOperatorFromCanonicalStrategy(Oid opclass, if (!StrategyIsValid(*strat)) ereport(ERROR, (errcode(ERRCODE_UNDEFINED_OBJECT), - errmsg("no %s operator found for WITHOUT OVERLAPS constraint", opname), + errmsg("no %s operator found for %s", opname, context), errdetail("Could not translate strategy number %u for opclass %d.", opstrat, opclass), errhint("Define a stratnum support function for your GiST opclass."))); - *opid = get_opfamily_member(opfamily, opcintype, opcintype, *strat); + /* + * We parameterize rhstype so foreign keys can ask for a <@ operator + * whose rhs matches the aggregate function. For example range_agg + * returns anymultirange. + */ + if (!OidIsValid(rhstype)) + rhstype = opcintype; + *opid = get_opfamily_member(opfamily, opcintype, rhstype, *strat); } if (!OidIsValid(*opid)) @@ -2500,11 +2512,11 @@ GetOperatorFromCanonicalStrategy(Oid opclass, ereport(ERROR, (errcode(ERRCODE_UNDEFINED_OBJECT), - errmsg("no %s operator found for WITHOUT OVERLAPS constraint", opname), + errmsg("no %s operator found for %s", opname, context), errdetail("There must be an %s operator within opfamily \"%s\" for type \"%s\".", opname, NameStr(opfform->opfname), - format_type_be(atttype)))); + format_type_be(opcintype)))); } } diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index d1189d74a8..4320c6f15e 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -16,6 +16,7 @@ #include "access/attmap.h" #include "access/genam.h" +#include "access/gist_private.h" #include "access/heapam.h" #include "access/heapam_xlog.h" #include "access/multixact.h" @@ -207,8 +208,11 @@ typedef struct NewConstraint ConstrType contype; /* CHECK or FOREIGN */ Oid refrelid; /* PK rel, if FOREIGN */ Oid refindid; /* OID of PK's index, if FOREIGN */ + bool conwithperiod; /* Whether the new FOREIGN KEY uses PERIOD */ Oid conid; /* OID of pg_constraint entry, if FOREIGN */ Node *qual; /* Check expr or CONSTR_FOREIGN Constraint */ + Oid *operoids; /* oper oids for FOREIGN KEY with PERIOD */ + Oid *procoids; /* proc oids for FOREIGN KEY with PERIOD */ ExprState *qualstate; /* Execution state for CHECK expr */ } NewConstraint; @@ -379,17 +383,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); @@ -499,7 +506,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); @@ -509,7 +517,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); @@ -541,6 +551,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); @@ -5895,7 +5911,8 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode, validateForeignKeyConstraint(fkconstraint->conname, rel, refrel, con->refindid, - con->conid); + con->conid, + con->conwithperiod); /* * No need to mark the constraint row as validated, we did @@ -9524,6 +9541,13 @@ 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}; + Oid periodoperoid; + Oid periodprocoid; int i; int numfks, numpks, @@ -9618,6 +9642,15 @@ 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); + else + if (fkconstraint->pk_period) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("foreign key uses PERIOD on the referenced table but not the referencing table"))); numfkdelsetcols = transformColumnNameList(RelationGetRelid(rel), fkconstraint->fk_del_set_cols, @@ -9636,7 +9669,9 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, { numpks = transformFkeyGetPrimaryKey(pkrel, &indexOid, &fkconstraint->pk_attrs, + &fkconstraint->pk_period, pkattnum, pktypoid, + pkperiodattnums, pkperiodtypoids, opclasses); } else @@ -9644,8 +9679,22 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, numpks = transformColumnNameList(RelationGetRelid(pkrel), fkconstraint->pk_attrs, pkattnum, pktypoid); + if (is_temporal) + { + /* Since we got pk_attrs, we should have pk_period too. */ + if (!fkconstraint->pk_period) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("foreign key uses PERIOD on the referencing table but not the referenced table"))); + + 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); } @@ -9704,189 +9753,40 @@ 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; } + /* + * For FKs with PERIOD we need an operator and aggregate function + * to check whether the referencing row's range is contained + * by the aggregated ranges of the referenced row(s). + * For rangetypes this is fk.periodatt <@ range_agg(pk.periodatt). + * FKs will look these up at "runtime", but we should make sure + * the lookup works here. + */ + if (is_temporal) + FindFKPeriodOpersAndProcs(opclasses[numpks - 1], &periodoperoid, &periodprocoid); + /* * Create all the constraint and trigger objects, recursing to partitions * as necessary. First handle the referenced side. @@ -9903,7 +9803,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, @@ -9919,7 +9820,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. @@ -10004,7 +9906,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; @@ -10090,7 +9993,7 @@ addFkRecurseReferenced(List **wqueue, Constraint *fkconstraint, Relation rel, conislocal, /* islocal */ coninhcount, /* inhcount */ connoinherit, /* conNoInherit */ - false, /* conWithoutOverlaps */ + is_temporal, /* conWithoutOverlaps */ false); /* is_internal */ ObjectAddressSet(address, ConstraintRelationId, constrOid); @@ -10166,7 +10069,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); @@ -10224,7 +10128,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; @@ -10272,6 +10177,7 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, newcon->refrelid = RelationGetRelid(pkrel); newcon->refindid = indexOid; newcon->conid = parentConstr; + newcon->conwithperiod = fkconstraint->fk_period != NULL; newcon->qual = (Node *) fkconstraint; tab->constraints = lappend(tab->constraints, newcon); @@ -10389,7 +10295,7 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, false, 1, false, - false, /* conWithoutOverlaps */ + is_temporal, /* conWithoutOverlaps */ false); /* @@ -10420,7 +10326,8 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, old_check_ok, lockmode, insertTriggerOid, - updateTriggerOid); + updateTriggerOid, + is_temporal); table_close(partition, NoLock); } @@ -10656,7 +10563,8 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel) confdelsetcols, true, deleteTriggerOid, - updateTriggerOid); + updateTriggerOid, + constrForm->conwithoutoverlaps); table_close(fkRel, NoLock); ReleaseSysCache(tuple); @@ -10749,6 +10657,7 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) ListCell *lc; Oid insertTriggerOid, updateTriggerOid; + bool is_temporal; tuple = SearchSysCache1(CONSTROID, ObjectIdGetDatum(parentConstrOid)); if (!HeapTupleIsValid(tuple)) @@ -10864,6 +10773,7 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) fkconstraint->conname = pstrdup(NameStr(constrForm->conname)); indexOid = constrForm->conindid; + is_temporal = constrForm->conwithoutoverlaps; constrOid = CreateConstraintEntry(fkconstraint->conname, constrForm->connamespace, @@ -10895,7 +10805,7 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) false, /* islocal */ 1, /* inhcount */ false, /* conNoInherit */ - false, /* conWithoutOverlaps */ + is_temporal, /* conWithoutOverlaps */ true); /* Set up partition dependencies for the new constraint */ @@ -10929,13 +10839,300 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) false, /* no old check exists */ AccessExclusiveLock, insertTriggerOid, - updateTriggerOid); + updateTriggerOid, + is_temporal); 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; + StrategyNumber rtstrategy; + int16 eqstrategy; + Oid pfeqop_right; + char *stratname; + + /* We need several fields out of the pg_opclass entry */ + cla_ht = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclass)); + 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"); + /* + * For the non-overlaps parts, we want either RTEqualStrategyNumber (without btree_gist) + * or BTEqualStrategyNumber (with btree_gist). We'll try the latter first. + */ + if (for_overlaps) + { + stratname = "overlaps"; + rtstrategy = RTOverlapStrategyNumber; + } + else + { + stratname = "equality"; + rtstrategy = RTEqualStrategyNumber; + } + eqstrategy = gistTranslateStratnum(opclass, rtstrategy); + if (!StrategyIsValid(eqstrategy)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("no %s operator found for foreign key", stratname), + errdetail("Could not translate strategy number %d for opclass %d.", + rtstrategy, opclass), + errhint("Define a stratnum support function for your GiST opclass."))); + } + else + { + /* + * Check it's a btree; currently this can never fail since no other + * index AMs support unique indexes. If we ever did have other types + * of unique indexes, we'd need a way to determine which operator + * strategy number is equality. (Is it reasonable to insist that + * every such index AM use btree's number for equality?) + */ + if (amid != BTREE_AM_OID) + elog(ERROR, "only b-tree indexes are supported for foreign keys"); + eqstrategy = BTEqualStrategyNumber; + } + + /* + * There had better be a primary equality operator for the index. + * 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; +} + +/* + * FindFkPeriodOpersAndProcs - + * + * Looks up the oper and proc oids for confkperiodoperoids and confkperiodprocoids. + * These are used by foreign keys with a PERIOD element. + */ +void +FindFKPeriodOpersAndProcs(Oid opclass, + Oid *periodoperoid, + Oid *periodprocoid) +{ + Oid opfamily; + Oid opcintype; + Oid aggrettype; + Oid funcid = InvalidOid; + StrategyNumber strat = RTContainedByStrategyNumber; + + /* First look up the support proc for aggregation. */ + if (get_opclass_opfamily_and_input_type(opclass, &opfamily, &opcintype)) + funcid = get_opfamily_proc(opfamily, opcintype, opcintype, GIST_REFERENCED_AGG_PROC); + + if (!OidIsValid(funcid)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("no support func %u found for FOREIGN KEY constraint", GIST_REFERENCED_AGG_PROC), + errhint("Define a referencedagg support function for your GiST opclass."))); + + *periodprocoid = funcid; + + /* Look up the function's rettype. */ + aggrettype = get_func_rettype(funcid); + + /* + * Now look up the ContainedBy operator. + * Its left arg must be the type of the column (or rather of the opclass). + * Its right arg must match the return type of the support proc. + */ + GetOperatorFromCanonicalStrategy(opclass, + aggrettype, + "contained by", + "FOREIGN KEY constraint", + periodoperoid, + &strat); +} + /* * 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 @@ -11433,7 +11630,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); @@ -11680,7 +11881,6 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName, return address; } - /* * transformColumnNameList - transform list of column names * @@ -11746,7 +11946,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; @@ -11811,36 +12013,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; @@ -11867,6 +12088,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"))); } /* @@ -11888,12 +12113,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)) @@ -11931,6 +12160,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 @@ -12040,7 +12282,8 @@ validateForeignKeyConstraint(char *conname, Relation rel, Relation pkrel, Oid pkindOid, - Oid constraintOid) + Oid constraintOid, + bool temporal) { TupleTableSlot *slot; TableScanDesc scan; @@ -12069,8 +12312,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; /* @@ -12139,6 +12384,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 @@ -12158,12 +12404,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; } @@ -12221,37 +12473,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, @@ -12281,37 +12564,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/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index f52ae3fc97..3bdd46a413 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -794,7 +794,9 @@ _outConstraint(StringInfo str, const Constraint *node) appendStringInfoString(str, "FOREIGN_KEY"); WRITE_NODE_FIELD(pktable); WRITE_NODE_FIELD(fk_attrs); + WRITE_NODE_FIELD(fk_period); WRITE_NODE_FIELD(pk_attrs); + WRITE_NODE_FIELD(pk_period); WRITE_CHAR_FIELD(fk_matchtype); WRITE_CHAR_FIELD(fk_upd_action); WRITE_CHAR_FIELD(fk_del_action); diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index b6ef30ce0d..190ec14ef1 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -462,7 +462,9 @@ _readConstraint(void) case CONSTR_FOREIGN: READ_NODE_FIELD(pktable); READ_NODE_FIELD(fk_attrs); + READ_NODE_FIELD(fk_period); READ_NODE_FIELD(pk_attrs); + READ_NODE_FIELD(pk_period); READ_CHAR_FIELD(fk_matchtype); READ_CHAR_FIELD(fk_upd_action); READ_CHAR_FIELD(fk_del_action); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 02ada43a90..c563a5b79b 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -521,12 +521,13 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); SetResetClause FunctionSetResetClause %type TableElement TypedTableElement ConstraintElem TableFuncElement -%type columnDef columnOptions +%type columnDef columnOptions optionalPeriodName %type def_elem reloption_elem old_aggr_elem operator_def_elem %type def_arg columnElem where_clause where_or_current_clause a_expr b_expr c_expr AexprConst indirection_el opt_slice_bound columnref in_expr having_clause func_table xmltable array_expr OptWhereClause operator_def_arg +%type opt_column_and_period_list %type rowsfrom_item rowsfrom_list opt_col_def_list %type opt_ordinality opt_without_overlaps %type ExclusionConstraintList ExclusionConstraintElem @@ -744,7 +745,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 @@ -4206,21 +4207,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); @@ -4248,6 +4251,16 @@ columnList: | columnList ',' columnElem { $$ = lappend($1, $3); } ; +optionalPeriodName: + ',' PERIOD columnElem { $$ = $3; } + | /*EMPTY*/ { $$ = NULL; } + ; + +opt_column_and_period_list: + '(' columnList optionalPeriodName ')' { $$ = list_make2($2, $3); } + | /*EMPTY*/ { $$ = list_make2(NIL, NULL); } + ; + columnElem: ColId { $$ = (Node *) makeString($1); @@ -17602,6 +17615,7 @@ reserved_keyword: | ONLY | OR | ORDER + | PERIOD | PLACING | PRIMARY | REFERENCES @@ -17911,6 +17925,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..dec7b08644 100644 --- a/src/backend/utils/adt/ri_triggers.c +++ b/src/backend/utils/adt/ri_triggers.c @@ -31,7 +31,9 @@ #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/tablecmds.h" #include "commands/trigger.h" #include "executor/executor.h" #include "executor/spi.h" @@ -48,6 +50,7 @@ #include "utils/inval.h" #include "utils/lsyscache.h" #include "utils/memutils.h" +#include "utils/rangetypes.h" #include "utils/rel.h" #include "utils/rls.h" #include "utils/ruleutils.h" @@ -118,12 +121,15 @@ typedef struct RI_ConstraintInfo int16 confdelsetcols[RI_MAX_NUMKEYS]; /* attnums of cols to set on * delete */ char confmatchtype; /* foreign key's match type */ + bool temporal; /* if the foreign key is temporal */ int nkeys; /* number of key columns */ int16 pk_attnums[RI_MAX_NUMKEYS]; /* attnums of referenced cols */ int16 fk_attnums[RI_MAX_NUMKEYS]; /* attnums of referencing cols */ Oid pf_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (PK = FK) */ Oid pp_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (PK = PK) */ Oid ff_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (FK = FK) */ + Oid period_contained_by_oper; /* operator for PEROID SQL */ + Oid period_referenced_agg_proc; /* proc for PERIOD SQL */ dlist_node valid_link; /* Link in list of valid entries */ } RI_ConstraintInfo; @@ -200,8 +206,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); @@ -231,6 +238,8 @@ static void ri_ReportViolation(const RI_ConstraintInfo *riinfo, Relation pk_rel, Relation fk_rel, TupleTableSlot *violatorslot, TupleDesc tupdesc, int queryno, bool partgone) pg_attribute_noreturn(); +static void lookupTRIOperAndProc(const RI_ConstraintInfo *riinfo, + char **opname, char **aggname); /* @@ -361,26 +370,58 @@ 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 +431,15 @@ RI_FKey_check(TriggerData *trigdata) queryoids[i] = fk_type; } appendStringInfoString(&querybuf, " FOR KEY SHARE OF x"); + if (riinfo->temporal) + { + char *opname; + char *aggname; + lookupTRIOperAndProc(riinfo, &opname, &aggname); + appendStringInfo(&querybuf, ") x1 HAVING $%d %s %s(x1.r)", riinfo->nkeys, opname, aggname); + pfree(opname); + pfree(aggname); + } /* Prepare and save the plan */ qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids, @@ -497,21 +547,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 +599,15 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel, queryoids[i] = pk_type; } appendStringInfoString(&querybuf, " FOR KEY SHARE OF x"); + if (riinfo->temporal) + { + char *opname; + char *aggname; + lookupTRIOperAndProc(riinfo, &opname, &aggname); + appendStringInfo(&querybuf, ") x1 HAVING $%d %s %s(x1.r)", riinfo->nkeys, opname, aggname); + pfree(opname); + pfree(aggname); + } /* Prepare and save the plan */ qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids, @@ -695,10 +782,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 +1306,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 +1454,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 +1553,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 +1701,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 +2352,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 +2387,7 @@ ri_LoadConstraintInfo(Oid constraintOid) riinfo->confupdtype = conForm->confupdtype; riinfo->confdeltype = conForm->confdeltype; riinfo->confmatchtype = conForm->confmatchtype; + riinfo->temporal = conForm->conwithoutoverlaps; DeconstructFkConstraintRow(tup, &riinfo->nkeys, @@ -2182,6 +2399,18 @@ ri_LoadConstraintInfo(Oid constraintOid) &riinfo->ndelsetcols, riinfo->confdelsetcols); + /* + * For temporal FKs, get the operator and aggregate function we need. + * We ask the opclass of the PK element for this. + * This all gets cached (as does the generated plan), + * so there's no performance issue. + */ + if (riinfo->temporal) + { + Oid opclass = get_index_column_opclass(conForm->conindid, riinfo->nkeys); + FindFKPeriodOpersAndProcs(opclass, &riinfo->period_contained_by_oper, &riinfo->period_referenced_agg_proc); + } + ReleaseSysCache(tup); /* @@ -2791,9 +3020,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 +3033,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 +3066,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,12 +3304,49 @@ 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; } return RI_TRIGGER_NONE; } + +/* + * lookupTRIOperAndProc - + * + * Gets the names of the operator and aggregate function + * used to build the SQL for TRI constraints. + * Raises an error if either is not found. + */ +static void +lookupTRIOperAndProc(const RI_ConstraintInfo *riinfo, char **opname, char **aggname) +{ + Oid oid; + + oid = riinfo->period_contained_by_oper; + if (!OidIsValid(oid)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("no ContainedBy operator for foreign key constraint \"%s\"", + NameStr(riinfo->conname)), + errhint("You must use an operator class with a matching ContainedBy operator."))); + *opname = get_opname(oid); + + oid = riinfo->period_referenced_agg_proc; + if (!OidIsValid(oid)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("no referencedagg support function for foreign key constraint \"%s\"", + NameStr(riinfo->conname)), + errhint("You must use an operator class with a referencedagg support function."))); + *aggname = get_func_name_and_namespace(oid); +} diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 25ac0ad1bc..1bf3aeb342 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, - 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,9 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, val = SysCacheGetAttrNotNull(CONSTROID, tup, Anum_pg_constraint_conkey); - decompile_column_index_array(val, conForm->conrelid, false, &buf); + /* If it is a temporal foreign key then it uses PERIOD. */ + decompile_column_index_array(val, conForm->conrelid, false, + conForm->conwithoutoverlaps, &buf); /* add foreign relation name */ appendStringInfo(&buf, ") REFERENCES %s(", @@ -2258,7 +2260,8 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, val = SysCacheGetAttrNotNull(CONSTROID, tup, Anum_pg_constraint_confkey); - decompile_column_index_array(val, conForm->confrelid, false, &buf); + decompile_column_index_array(val, conForm->confrelid, false, + conForm->conwithoutoverlaps, &buf); appendStringInfoChar(&buf, ')'); @@ -2344,7 +2347,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, if (!isnull) { appendStringInfoString(&buf, " ("); - decompile_column_index_array(val, conForm->conrelid, false, &buf); + decompile_column_index_array(val, conForm->conrelid, false, false, &buf); appendStringInfoChar(&buf, ')'); } @@ -2379,7 +2382,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, val = SysCacheGetAttrNotNull(CONSTROID, tup, Anum_pg_constraint_conkey); - keyatts = decompile_column_index_array(val, conForm->conrelid, conForm->conwithoutoverlaps, &buf); + keyatts = decompile_column_index_array(val, conForm->conrelid, conForm->conwithoutoverlaps, false, &buf); appendStringInfoChar(&buf, ')'); @@ -2575,7 +2578,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, */ static int decompile_column_index_array(Datum column_index_array, Oid relId, - bool withoutOverlaps, StringInfo buf) + bool withoutOverlaps, bool withPeriod, StringInfo buf) { Datum *keys; int nKeys; @@ -2594,7 +2597,9 @@ decompile_column_index_array(Datum column_index_array, Oid relId, if (j == 0) appendStringInfoString(buf, quote_identifier(colName)); else - appendStringInfo(buf, ", %s", quote_identifier(colName)); + appendStringInfo(buf, ", %s%s", + (withPeriod && j == nKeys - 1) ? "PERIOD " : "", + quote_identifier(colName)); } if (withoutOverlaps) appendStringInfoString(buf, " WITHOUT OVERLAPS"); diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index fc6d267e44..12aede5da9 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -1627,6 +1627,37 @@ get_func_name(Oid funcid) return NULL; } +/* + * get_func_name_and_namespace + * returns the schema-qualified name of the function with the given funcid + * + * Note: returns a palloc'd copy of the string, or NULL if no such function. + */ +char * +get_func_name_and_namespace(Oid funcid) +{ + HeapTuple tp; + + tp = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcid)); + if (HeapTupleIsValid(tp)) + { + Form_pg_proc functup = (Form_pg_proc) GETSTRUCT(tp); + char *namesp = get_namespace_name(functup->pronamespace); + char *func = NameStr(functup->proname); + char *result; + int len = strlen(namesp) + strlen(func) + 2; + + result = palloc(len * sizeof(char)); + snprintf(result, len, "%s.%s", namesp, func); + + pfree(namesp); + ReleaseSysCache(tp); + return result; + } + else + return NULL; +} + /* * get_func_namespace * diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h index c17b1e9d31..0cc15a50d7 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); @@ -181,7 +181,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 1787afba74..874ca98ed6 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -3945,6 +3945,30 @@ prorettype => 'trigger', proargtypes => '', prosrc => 'RI_FKey_noaction_upd' }, +# Temporal referential integrity constraint triggers +{ oid => '6122', descr => 'temporal referential integrity FOREIGN KEY ... REFERENCES', + proname => '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/commands/defrem.h b/src/include/commands/defrem.h index 1f738b0129..e2b9dca1e0 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -50,8 +50,10 @@ extern bool CheckIndexCompatible(Oid oldId, extern Oid GetDefaultOpClass(Oid type_id, Oid am_id); extern Oid ResolveOpClass(const List *opclass, Oid attrType, const char *accessMethodName, Oid accessMethodId); -extern void GetOperatorFromCanonicalStrategy(Oid opclass, Oid atttype, - const char *opname, Oid *opid, +extern void GetOperatorFromCanonicalStrategy(Oid opclass, Oid rhstype, + const char *opname, + const char *context, + Oid *opid, StrategyNumber *strat); /* commands/functioncmds.c */ diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h index a9c6825601..82a1444d34 100644 --- a/src/include/commands/tablecmds.h +++ b/src/include/commands/tablecmds.h @@ -105,5 +105,8 @@ extern void RangeVarCallbackOwnsRelation(const RangeVar *relation, Oid relId, Oid oldRelId, void *arg); extern bool PartConstraintImpliedByRelConstraint(Relation scanrel, List *partConstraint); +extern void FindFKPeriodOpersAndProcs(Oid opclass, + Oid *periodoperoid, + Oid *periodprocoid); #endif /* TABLECMDS_H */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 098990dd16..03c5e707b9 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2563,6 +2563,10 @@ typedef enum ConstrType /* types of constraints */ #define FKCONSTR_MATCH_PARTIAL 'p' #define FKCONSTR_MATCH_SIMPLE 's' +/* Foreign key with PERIOD positions */ +#define FKCONSTR_PERIOD_OP_CONTAINED_BY 0 +#define FKCONSTR_PERIOD_PROC_REFERENCED_AGG 0 + typedef struct Constraint { pg_node_attr(custom_read_write) @@ -2609,7 +2613,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/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index c22cabdf42..e82fb6f425 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -120,6 +120,7 @@ extern Oid get_negator(Oid opno); extern RegProcedure get_oprrest(Oid opno); extern RegProcedure get_oprjoin(Oid opno); extern char *get_func_name(Oid funcid); +extern char *get_func_name_and_namespace(Oid funcid); extern Oid get_func_namespace(Oid funcid); extern Oid get_func_rettype(Oid funcid); extern int get_func_nargs(Oid funcid); diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out index cd0446fa51..7e5e209237 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -393,3 +393,554 @@ 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 mismatched PERIOD columns: +-- (parent_id, PERIOD valid_at) REFERENCES (id, 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, valid_at) +); +ERROR: foreign key uses PERIOD on the referencing table but not the referenced table +-- (parent_id, valid_at) REFERENCES (id, PERIOD valid_at) +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at tsrange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at) + REFERENCES temporal_rng (id, PERIOD valid_at) +); +ERROR: foreign key uses PERIOD on the referenced table but not the referencing table +-- (parent_id, PERIOD valid_at) REFERENCES (id) +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at tsrange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng (id) +); +ERROR: foreign key uses PERIOD on the referencing table but not the referenced table +-- (parent_id) REFERENCES (id, PERIOD valid_at) +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at tsrange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id) + REFERENCES temporal_rng (id, PERIOD valid_at) +); +ERROR: foreign key uses PERIOD on the referenced table but not the referencing table +-- with inferred PK on the referenced table: +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at tsrange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng +); +DROP TABLE temporal_fk_rng2rng; +-- should fail because of duplicate referenced columns: +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at tsrange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD parent_id) + REFERENCES temporal_rng (id, PERIOD id) +); +ERROR: foreign key referenced-columns list must not contain duplicates +-- Two scalar columns +CREATE TABLE temporal_fk2_rng2rng ( + id int4range, + valid_at tsrange, + parent_id1 int4range, + parent_id2 int4range, + CONSTRAINT temporal_fk2_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk2_rng2rng_fk FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_rng2 (id1, id2, PERIOD valid_at) +); +\d temporal_fk2_rng2rng + Table "public.temporal_fk2_rng2rng" + Column | Type | Collation | Nullable | Default +------------+-----------+-----------+----------+--------- + id | int4range | | not null | + valid_at | tsrange | | not null | + parent_id1 | int4range | | | + parent_id2 | int4range | | | +Indexes: + "temporal_fk2_rng2rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +Foreign-key constraints: + "temporal_fk2_rng2rng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_rng2(id1, id2, PERIOD valid_at) + +DROP TABLE temporal_fk2_rng2rng; +-- +-- test ALTER TABLE ADD CONSTRAINT +-- +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at tsrange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng (id, PERIOD valid_at); +-- Two scalar columns: +CREATE TABLE temporal_fk2_rng2rng ( + id int4range, + valid_at tsrange, + parent_id1 int4range, + parent_id2 int4range, + CONSTRAINT temporal_fk2_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +ALTER TABLE temporal_fk2_rng2rng + ADD CONSTRAINT temporal_fk2_rng2rng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_rng2 (id1, id2, PERIOD valid_at); +\d temporal_fk2_rng2rng + Table "public.temporal_fk2_rng2rng" + Column | Type | Collation | Nullable | Default +------------+-----------+-----------+----------+--------- + id | int4range | | not null | + valid_at | tsrange | | not null | + parent_id1 | int4range | | | + parent_id2 | int4range | | | +Indexes: + "temporal_fk2_rng2rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +Foreign-key constraints: + "temporal_fk2_rng2rng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_rng2(id1, id2, PERIOD valid_at) + +-- with inferred PK on the referenced table, and wrong column type: +ALTER TABLE temporal_fk_rng2rng + DROP CONSTRAINT temporal_fk_rng2rng_fk, + ALTER COLUMN valid_at TYPE daterange USING daterange(lower(valid_at)::date, upper(valid_at)::date); +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng; +ERROR: foreign key constraint "temporal_fk_rng2rng_fk" cannot be implemented +DETAIL: Key columns "valid_at" and "valid_at" are of incompatible types: daterange and tsrange. +ALTER TABLE temporal_fk_rng2rng + ALTER COLUMN valid_at TYPE tsrange USING tsrange(lower(valid_at), upper(valid_at)); +-- with inferred PK on the referenced table: +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng; +-- should fail because of duplicate referenced columns: +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk2 + FOREIGN KEY (parent_id, PERIOD parent_id) + REFERENCES temporal_rng (id, PERIOD id); +ERROR: foreign key referenced-columns list must not contain duplicates +-- +-- test with rows already +-- +DELETE FROM temporal_fk_rng2rng; +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 39ea24f643..9b79bb6345 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -288,3 +288,529 @@ 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 mismatched PERIOD columns: +-- (parent_id, PERIOD valid_at) REFERENCES (id, 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, valid_at) +); +-- (parent_id, valid_at) REFERENCES (id, PERIOD valid_at) +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at tsrange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at) + REFERENCES temporal_rng (id, PERIOD valid_at) +); +-- (parent_id, PERIOD valid_at) REFERENCES (id) +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at tsrange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng (id) +); +-- (parent_id) REFERENCES (id, PERIOD valid_at) +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at tsrange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id) + REFERENCES temporal_rng (id, PERIOD valid_at) +); + +-- with inferred PK on the referenced table: +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at tsrange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng +); +DROP TABLE temporal_fk_rng2rng; + +-- should fail because of duplicate referenced columns: +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at tsrange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD parent_id) + REFERENCES temporal_rng (id, PERIOD id) +); + +-- Two scalar columns +CREATE TABLE temporal_fk2_rng2rng ( + id int4range, + valid_at tsrange, + parent_id1 int4range, + parent_id2 int4range, + CONSTRAINT temporal_fk2_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk2_rng2rng_fk FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_rng2 (id1, id2, PERIOD valid_at) +); +\d temporal_fk2_rng2rng +DROP TABLE temporal_fk2_rng2rng; + +-- +-- test ALTER TABLE ADD CONSTRAINT +-- + +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at tsrange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng (id, PERIOD valid_at); +-- Two scalar columns: +CREATE TABLE temporal_fk2_rng2rng ( + id int4range, + valid_at tsrange, + parent_id1 int4range, + parent_id2 int4range, + CONSTRAINT temporal_fk2_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +ALTER TABLE temporal_fk2_rng2rng + ADD CONSTRAINT temporal_fk2_rng2rng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_rng2 (id1, id2, PERIOD valid_at); +\d temporal_fk2_rng2rng + +-- with inferred PK on the referenced table, and wrong column type: +ALTER TABLE temporal_fk_rng2rng + DROP CONSTRAINT temporal_fk_rng2rng_fk, + ALTER COLUMN valid_at TYPE daterange USING daterange(lower(valid_at)::date, upper(valid_at)::date); +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng; +ALTER TABLE temporal_fk_rng2rng + ALTER COLUMN valid_at TYPE tsrange USING tsrange(lower(valid_at), upper(valid_at)); + +-- with inferred PK on the referenced table: +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng; + +-- should fail because of duplicate referenced columns: +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk2 + FOREIGN KEY (parent_id, PERIOD parent_id) + REFERENCES temporal_rng (id, PERIOD id); + +-- +-- test with rows already +-- + +DELETE FROM temporal_fk_rng2rng; +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.42.0