From 6fc6c78a3692577ade840627d04ff4aee6302f50 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Wed, 28 Feb 2024 21:45:37 -0800 Subject: [PATCH v30 1/6] Add temporal FOREIGN KEYs - Added bison support for temporal FOREIGN KEYs. They should have a range column for the temporal component. (Multirange and other types are not yet supported.) - Added temporal trigger functions akin to existing RI trigger functions. - Don't yet support ON {UPDATE,DELETE} {CASCADE,SET NULL,SET DEFAULT}. - Added pg_dump support. - Show the correct syntax in psql \d output for foreign keys. - Added tests and documentation. --- .../btree_gist/expected/without_overlaps.out | 48 ++ contrib/btree_gist/sql/without_overlaps.sql | 28 + doc/src/sgml/catalogs.sgml | 3 +- doc/src/sgml/ref/create_table.sgml | 45 +- src/backend/catalog/pg_constraint.c | 43 ++ src/backend/commands/indexcmds.c | 49 +- src/backend/commands/tablecmds.c | 211 +++++- src/backend/parser/gram.y | 45 +- src/backend/utils/adt/ri_triggers.c | 131 +++- src/backend/utils/adt/ruleutils.c | 19 +- src/include/catalog/pg_constraint.h | 19 +- src/include/commands/defrem.h | 2 +- src/include/nodes/parsenodes.h | 2 + src/include/parser/kwlist.h | 1 + .../regress/expected/without_overlaps.out | 644 +++++++++++++++++- src/test/regress/sql/without_overlaps.sql | 615 ++++++++++++++++- 16 files changed, 1788 insertions(+), 117 deletions(-) diff --git a/contrib/btree_gist/expected/without_overlaps.out b/contrib/btree_gist/expected/without_overlaps.out index be52c522e89..18856900ded 100644 --- a/contrib/btree_gist/expected/without_overlaps.out +++ b/contrib/btree_gist/expected/without_overlaps.out @@ -42,3 +42,51 @@ INSERT INTO temporal_rng VALUES (1, '[2000-06-01,2001-01-01)'); ERROR: conflicting key value violates exclusion constraint "temporal_rng_pk" DETAIL: Key (id, valid_at)=(1, [06-01-2000,01-01-2001)) conflicts with existing key (id, valid_at)=(1, [01-01-2000,01-01-2001)). +-- Foreign key +CREATE TABLE temporal_fk_rng2rng ( + id integer, + valid_at daterange, + parent_id integer, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng (id, PERIOD valid_at) +); +\d temporal_fk_rng2rng + Table "public.temporal_fk_rng2rng" + Column | Type | Collation | Nullable | Default +-----------+-----------+-----------+----------+--------- + id | integer | | not null | + valid_at | daterange | | not null | + parent_id | integer | | | +Indexes: + "temporal_fk_rng2rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +Foreign-key constraints: + "temporal_fk_rng2rng_fk" FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng(id, PERIOD valid_at) + +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_rng2rng_fk'; + pg_get_constraintdef +--------------------------------------------------------------------------------------- + FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng(id, PERIOD valid_at) +(1 row) + +-- okay +INSERT INTO temporal_fk_rng2rng VALUES + (1, '[2000-01-01,2001-01-01)', 1); +-- okay spanning two parent records: +INSERT INTO temporal_fk_rng2rng VALUES + (2, '[2000-01-01,2002-01-01)', 1); +-- key is missing +INSERT INTO temporal_fk_rng2rng VALUES + (3, '[2000-01-01,2001-01-01)', 3); +ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=(3, [01-01-2000,01-01-2001)) is not present in table "temporal_rng". +-- key exist but is outside range +INSERT INTO temporal_fk_rng2rng VALUES + (4, '[2001-01-01,2002-01-01)', 2); +ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=(2, [01-01-2001,01-01-2002)) is not present in table "temporal_rng". +-- key exist but is partly outside range +INSERT INTO temporal_fk_rng2rng VALUES + (5, '[2000-01-01,2002-01-01)', 2); +ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=(2, [01-01-2000,01-01-2002)) is not present in table "temporal_rng". diff --git a/contrib/btree_gist/sql/without_overlaps.sql b/contrib/btree_gist/sql/without_overlaps.sql index 433c609ab22..b1b581fcabc 100644 --- a/contrib/btree_gist/sql/without_overlaps.sql +++ b/contrib/btree_gist/sql/without_overlaps.sql @@ -23,3 +23,31 @@ INSERT INTO temporal_rng VALUES -- should fail: INSERT INTO temporal_rng VALUES (1, '[2000-06-01,2001-01-01)'); + +-- Foreign key +CREATE TABLE temporal_fk_rng2rng ( + id integer, + valid_at daterange, + parent_id integer, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng (id, PERIOD valid_at) +); +\d temporal_fk_rng2rng +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_rng2rng_fk'; + +-- okay +INSERT INTO temporal_fk_rng2rng VALUES + (1, '[2000-01-01,2001-01-01)', 1); +-- okay spanning two parent records: +INSERT INTO temporal_fk_rng2rng VALUES + (2, '[2000-01-01,2002-01-01)', 1); +-- key is missing +INSERT INTO temporal_fk_rng2rng VALUES + (3, '[2000-01-01,2001-01-01)', 3); +-- key exist but is outside range +INSERT INTO temporal_fk_rng2rng VALUES + (4, '[2001-01-01,2002-01-01)', 2); +-- key exist but is partly outside range +INSERT INTO temporal_fk_rng2rng VALUES + (5, '[2000-01-01,2002-01-01)', 2); diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 2f091ad09d1..860d1f1991e 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -2725,7 +2725,8 @@ SCRAM-SHA-256$<iteration count>:&l This constraint is defined with WITHOUT OVERLAPS - (for primary keys and unique constraints). + (for primary keys and unique constraints) or PERIOD + (for foreign keys). diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 4cbaaccaf7c..512b1db3878 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -81,7 +81,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | - FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] + FOREIGN KEY ( column_name [, ... ] [, PERIOD column_name ] ) REFERENCES reftable [ ( refcolumn [, ... ] [, PERIOD column_name ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] @@ -1152,8 +1152,8 @@ WITH ( MODULUS numeric_literal, REM REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] (column constraint) - FOREIGN KEY ( column_name [, ... ] ) - REFERENCES reftable [ ( refcolumn [, ... ] ) ] + FOREIGN KEY ( column_name [, ... ] [, PERIOD column_name ] ) + REFERENCES reftable [ ( refcolumn [, ... ] [, PERIOD column_name ] ) ] [ MATCH matchtype ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] @@ -1169,7 +1169,32 @@ WITH ( MODULUS numeric_literal, REM primary key of the reftable is used. Otherwise, the refcolumn list must refer to the columns of a non-deferrable unique or primary key - constraint or be the columns of a non-partial unique index. The user + constraint or be the columns of a non-partial unique index. + + + + If the last column is marked with PERIOD, + it is treated in a special way. + While the non-PERIOD columns are compared for equality + (and there must be at least one of them), + the PERIOD column is not. + Instead the constraint is considered satisfied + if the referenced table has matching records + (based on the non-PERIOD parts of the key) + whose combined PERIOD values completely cover + the referencing record's. + In other words, the reference must have a referent for its entire duration. + This column must be a column with a range type. + In addition the referenced table must have a primary key + or unique constraint declared with WITHOUT OVERLAPS. + Finally, if one side of the foreign key uses PERIOD, + the other side must too. If the refcolumn + list is omitted, we assume the WITHOUT OVERLAPS + part of the primary key should be treated as if marked with PERIOD. + + + + The user must have REFERENCES permission on the referenced table (either the whole table, or the specific referenced columns). The addition of a foreign key constraint requires a @@ -1243,6 +1268,10 @@ WITH ( MODULUS numeric_literal, REM values of the referencing column(s) to the new values of the referenced columns, respectively. + + + In a temporal foreign key, this option is not supported. + @@ -1254,6 +1283,10 @@ WITH ( MODULUS numeric_literal, REM referencing columns, to null. A subset of columns can only be specified for ON DELETE actions. + + + In a temporal foreign key, this option is not supported. + @@ -1267,6 +1300,10 @@ WITH ( MODULUS numeric_literal, REM (There must be a row in the referenced table matching the default values, if they are not null, or the operation will fail.) + + + In a temporal foreign key, this option is not supported. + diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index 5ea9df219c1..eb15b7e7935 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -15,6 +15,7 @@ #include "postgres.h" #include "access/genam.h" +#include "access/gist.h" #include "access/htup_details.h" #include "access/sysattr.h" #include "access/table.h" @@ -1605,6 +1606,48 @@ DeconstructFkConstraintRow(HeapTuple tuple, int *numfks, *numfks = numkeys; } +/* + * FindFkPeriodOpers - + * + * Looks up the operator oids used for the PERIOD part of a temporal foreign key. + * The opclass should be the opclass of that PERIOD element. + * Everything else is an output: containedbyoperoid is the ContainedBy operator for + * types matching the PERIOD element. + * And aggedcontainedbyoperoid is also a ContainedBy operator, + * but one whose rhs is anymultirange. + * That way foreign keys can compare fkattr <@ range_agg(pkattr). + */ +void +FindFKPeriodOpers(Oid opclass, + Oid *containedbyoperoid, + Oid *aggedcontainedbyoperoid) +{ + StrategyNumber strat; + + /* + * Look up the ContainedBy operator whose lhs and rhs are the opclass's type. + * We use this to optimize RI checks: if the new value includes all + * of the old value, then we can treat the attribute as if it didn't change, + * and skip the RI check. + */ + strat = RTContainedByStrategyNumber; + GetOperatorFromWellKnownStrategy(opclass, + InvalidOid, + containedbyoperoid, + &strat); + + /* + * Now look up the ContainedBy operator. + * Its left arg must be the type of the column (or rather of the opclass). + * Its right arg must match the return type of the support proc. + */ + strat = RTContainedByStrategyNumber; + GetOperatorFromWellKnownStrategy(opclass, + ANYMULTIRANGEOID, + aggedcontainedbyoperoid, + &strat); +} + /* * Determine whether a relation can be proven functionally dependent on * a set of grouping columns. If so, return true and add the pg_constraint diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 7b20d103c86..d540a60910a 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -2185,8 +2185,10 @@ ComputeIndexAttrs(IndexInfo *indexInfo, strat = RTOverlapStrategyNumber; else strat = RTEqualStrategyNumber; - GetOperatorFromWellKnownStrategy(opclassOids[attn], atttype, - &opid, &strat); + GetOperatorFromWellKnownStrategy(opclassOids[attn], + InvalidOid, + &opid, + &strat); indexInfo->ii_ExclusionOps[attn] = opid; indexInfo->ii_ExclusionProcs[attn] = get_opcode(opid); indexInfo->ii_ExclusionStrats[attn] = strat; @@ -2425,7 +2427,7 @@ GetDefaultOpClass(Oid type_id, Oid am_id) * GetOperatorFromWellKnownStrategy * * opclass - the opclass to use - * atttype - the type to ask about + * rhstype - the type for the right-hand side, or InvalidOid to use the type of the given opclass. * opid - holds the operator we found * strat - holds the input and output strategy number * @@ -2438,14 +2440,30 @@ GetDefaultOpClass(Oid type_id, Oid am_id) * InvalidStrategy. */ void -GetOperatorFromWellKnownStrategy(Oid opclass, Oid atttype, - Oid *opid, StrategyNumber *strat) +GetOperatorFromWellKnownStrategy(Oid opclass, + Oid rhstype, + Oid *opid, + StrategyNumber *strat) { Oid opfamily; Oid opcintype; StrategyNumber instrat = *strat; + char *errstr; - Assert(instrat == RTEqualStrategyNumber || instrat == RTOverlapStrategyNumber); + switch (instrat) + { + case RTEqualStrategyNumber: + errstr = "could not identify an equality operator for type %s"; + break; + case RTOverlapStrategyNumber: + errstr = "could not identify an overlaps operator for type %s"; + break; + case RTContainedByStrategyNumber: + errstr = "could not identify a contained by operator for type %s"; + break; + default: + elog(ERROR, "unsupported strategy number %u", instrat); + } *opid = InvalidOid; @@ -2468,16 +2486,19 @@ GetOperatorFromWellKnownStrategy(Oid opclass, Oid atttype, ereport(ERROR, errcode(ERRCODE_UNDEFINED_OBJECT), - instrat == RTEqualStrategyNumber ? - errmsg("could not identify an equality operator for type %s", format_type_be(atttype)) : - errmsg("could not identify an overlaps operator for type %s", format_type_be(atttype)), + errmsg(errstr, format_type_be(opcintype)), errdetail("Could not translate strategy number %d for operator class \"%s\" for access method \"%s\".", instrat, NameStr(((Form_pg_opclass) GETSTRUCT(tuple))->opcname), "gist")); - - ReleaseSysCache(tuple); } - *opid = get_opfamily_member(opfamily, opcintype, opcintype, *strat); + /* + * We parameterize rhstype so foreign keys can ask for a <@ operator + * whose rhs matches the aggregate function. For example range_agg + * returns anymultirange. + */ + if (!OidIsValid(rhstype)) + rhstype = opcintype; + *opid = get_opfamily_member(opfamily, opcintype, rhstype, *strat); } if (!OidIsValid(*opid)) @@ -2490,9 +2511,7 @@ GetOperatorFromWellKnownStrategy(Oid opclass, Oid atttype, ereport(ERROR, errcode(ERRCODE_UNDEFINED_OBJECT), - instrat == RTEqualStrategyNumber ? - errmsg("could not identify an equality operator for type %s", format_type_be(atttype)) : - errmsg("could not identify an overlaps operator for type %s", format_type_be(atttype)), + errmsg(errstr, format_type_be(opcintype)), errdetail("There is no suitable operator in operator family \"%s\" for access method \"%s\".", NameStr(((Form_pg_opfamily) GETSTRUCT(tuple))->opfname), "gist")); } diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 3ed0618b4e6..f1886c1c29d 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -16,6 +16,7 @@ #include "access/attmap.h" #include "access/genam.h" +#include "access/gist.h" #include "access/heapam.h" #include "access/heapam_xlog.h" #include "access/multixact.h" @@ -209,6 +210,7 @@ typedef struct NewConstraint ConstrType contype; /* CHECK or FOREIGN */ Oid refrelid; /* PK rel, if FOREIGN */ Oid refindid; /* OID of PK's index, if FOREIGN */ + bool conwithperiod; /* Whether the new FOREIGN KEY uses PERIOD */ Oid conid; /* OID of pg_constraint entry, if FOREIGN */ Node *qual; /* Check expr or CONSTR_FOREIGN Constraint */ ExprState *qualstate; /* Execution state for CHECK expr */ @@ -384,16 +386,16 @@ static int transformColumnNameList(Oid relId, List *colList, static int transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid, List **attnamelist, int16 *attnums, Oid *atttypids, - Oid *opclasses); + Oid *opclasses, bool *pk_period); static Oid transformFkeyCheckAttrs(Relation pkrel, int numattrs, int16 *attnums, - Oid *opclasses); + bool with_period, 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 hasperiod); static void ATController(AlterTableStmt *parsetree, Relation rel, List *cmds, bool recurse, LOCKMODE lockmode, AlterTableUtilityContext *context); @@ -506,7 +508,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 with_period); static void validateFkOnDeleteSetColumns(int numfks, const int16 *fkattnums, int numfksetcols, const int16 *fksetcolsattnums, List *fksetcols); @@ -516,7 +519,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 with_period); + static void CloneForeignKeyConstraints(List **wqueue, Relation parentRel, Relation partitionRel); static void CloneFkReferenced(Relation parentRel, Relation partitionRel); @@ -5962,7 +5967,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 @@ -9813,6 +9819,8 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, Oid ppeqoperators[INDEX_MAX_KEYS] = {0}; Oid ffeqoperators[INDEX_MAX_KEYS] = {0}; int16 fkdelsetcols[INDEX_MAX_KEYS] = {0}; + bool with_period; + bool pk_with_period; int i; int numfks, numpks, @@ -9907,6 +9915,14 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, numfks = transformColumnNameList(RelationGetRelid(rel), fkconstraint->fk_attrs, fkattnum, fktypoid); + with_period = fkconstraint->fk_with_period || fkconstraint->pk_with_period; + if (with_period) + { + if (!fkconstraint->fk_with_period) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("foreign key uses PERIOD on the referenced table but not the referencing table"))); + } numfkdelsetcols = transformColumnNameList(RelationGetRelid(rel), fkconstraint->fk_del_set_cols, @@ -9926,16 +9942,29 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, numpks = transformFkeyGetPrimaryKey(pkrel, &indexOid, &fkconstraint->pk_attrs, pkattnum, pktypoid, - opclasses); + opclasses, &pk_with_period); + + /* If the primary key uses WITHOUT OVERLAPS, the fk must use PERIOD */ + if (pk_with_period && !fkconstraint->fk_with_period) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("foreign key uses PERIOD on the referenced table but not the referencing table"))); } else { numpks = transformColumnNameList(RelationGetRelid(pkrel), fkconstraint->pk_attrs, pkattnum, pktypoid); + + /* Since we got pk_attrs, one should be a period. */ + if (with_period && !fkconstraint->pk_with_period) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("foreign key uses PERIOD on the referencing table but not the referenced table"))); + /* Look for an index matching the column list */ indexOid = transformFkeyCheckAttrs(pkrel, numpks, pkattnum, - opclasses); + with_period, opclasses); } /* @@ -10004,8 +10033,11 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, Oid pfeqop; Oid ppeqop; Oid ffeqop; + StrategyNumber rtstrategy; int16 eqstrategy; Oid pfeqop_right; + char *stratname; + bool for_overlaps = with_period && i == numpks - 1; /* We need several fields out of the pg_opclass entry */ cla_ht = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclasses[i])); @@ -10017,16 +10049,53 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, opcintype = cla_tup->opcintype; ReleaseSysCache(cla_ht); - /* - * Check it's a btree; currently this can never fail since no other - * index AMs support unique indexes. If we ever did have other types - * of unique indexes, we'd need a way to determine which operator - * strategy number is equality. (Is it reasonable to insist that - * every such index AM use btree's number for equality?) - */ - if (amid != BTREE_AM_OID) - elog(ERROR, "only b-tree indexes are supported for foreign keys"); - eqstrategy = BTEqualStrategyNumber; + if (with_period) + { + /* + * GiST indexes are required to support temporal foreign keys + * because they combine equals and overlaps. + */ + if (amid != GIST_AM_OID) + elog(ERROR, "only GiST indexes are supported for temporal foreign keys"); + if (for_overlaps) + { + stratname = "overlaps"; + rtstrategy = RTOverlapStrategyNumber; + } + else + { + stratname = "equality"; + rtstrategy = RTEqualStrategyNumber; + } + /* + * An opclass can use whatever strategy numbers it wants, so we ask + * the opclass what number it actually uses instead of our + * RT* constants. + */ + eqstrategy = GistTranslateStratnum(opclasses[i], rtstrategy); + if (eqstrategy == InvalidStrategy) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("no %s operator found for foreign key", stratname), + errdetail("Could not translate strategy number %d for opclass %d.", + rtstrategy, opclasses[i]), + errhint("Define a stratnum support function for your GiST opclass."))); + } + else + { + /* + * Check it's a btree. This can only fail if the primary key or + * unique constraint uses WITHOUT OVERLAPS. But then we should + * forbid a non-PERIOD foreign key. + * + * If we ever allowed non-temporal unique indexes with other index AMs, + * we could use GistTranslateStratnum (or something similar for non-GiST) + * to determine which operator strategy number is equality. + */ + if (amid != BTREE_AM_OID) + elog(ERROR, "only b-tree indexes are supported for non-PERIOD foreign keys"); + eqstrategy = BTEqualStrategyNumber; + } /* * There had better be a primary equality operator for the index. @@ -10087,16 +10156,21 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, } if (!(OidIsValid(pfeqop) && OidIsValid(ffeqop))) + { + char *fkattr_name = strVal(list_nth(fkconstraint->fk_attrs, i)); + char *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.", - strVal(list_nth(fkconstraint->fk_attrs, i)), - strVal(list_nth(fkconstraint->pk_attrs, i)), + fkattr_name, + pkattr_name, format_type_be(fktype), format_type_be(pktype)))); + } if (old_check_ok) { @@ -10169,6 +10243,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, new_castfunc == old_castfunc && (!IsPolymorphicType(pfeqop_right) || new_fktype == old_fktype)); + } pfeqoperators[i] = pfeqop; @@ -10176,6 +10251,22 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, ffeqoperators[i] = ffeqop; } + /* + * For FKs with PERIOD we need an operator and aggregate function + * to check whether the referencing row's range is contained + * by the aggregated ranges of the referenced row(s). + * For rangetypes this is fk.periodatt <@ range_agg(pk.periodatt). + * FKs will look these up at "runtime", but we should make sure + * the lookup works here, even if we don't use the values. + */ + if (with_period) + { + Oid periodoperoid; + Oid aggedperiodoperoid; + + FindFKPeriodOpers(opclasses[numpks - 1], &periodoperoid, &aggedperiodoperoid); + } + /* * Create all the constraint and trigger objects, recursing to partitions * as necessary. First handle the referenced side. @@ -10192,7 +10283,8 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, numfkdelsetcols, fkdelsetcols, old_check_ok, - InvalidOid, InvalidOid); + InvalidOid, InvalidOid, + with_period); /* Now handle the referencing side. */ addFkRecurseReferencing(wqueue, fkconstraint, rel, pkrel, @@ -10208,7 +10300,8 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, fkdelsetcols, old_check_ok, lockmode, - InvalidOid, InvalidOid); + InvalidOid, InvalidOid, + with_period); /* * Done. Close pk table, but keep lock until we've committed. @@ -10293,7 +10386,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 with_period) { ObjectAddress address; Oid constrOid; @@ -10379,7 +10473,7 @@ addFkRecurseReferenced(List **wqueue, Constraint *fkconstraint, Relation rel, conislocal, /* islocal */ coninhcount, /* inhcount */ connoinherit, /* conNoInherit */ - false, /* conPeriod */ + with_period, /* conPeriod */ false); /* is_internal */ ObjectAddressSet(address, ConstraintRelationId, constrOid); @@ -10455,7 +10549,8 @@ addFkRecurseReferenced(List **wqueue, Constraint *fkconstraint, Relation rel, pfeqoperators, ppeqoperators, ffeqoperators, numfkdelsetcols, fkdelsetcols, old_check_ok, - deleteTriggerOid, updateTriggerOid); + deleteTriggerOid, updateTriggerOid, + with_period); /* Done -- clean up (but keep the lock) */ table_close(partRel, NoLock); @@ -10513,7 +10608,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 with_period) { Oid insertTriggerOid, updateTriggerOid; @@ -10561,6 +10657,7 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, newcon->refrelid = RelationGetRelid(pkrel); newcon->refindid = indexOid; newcon->conid = parentConstr; + newcon->conwithperiod = fkconstraint->fk_with_period; newcon->qual = (Node *) fkconstraint; tab->constraints = lappend(tab->constraints, newcon); @@ -10678,7 +10775,7 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, false, 1, false, - false, /* conPeriod */ + with_period, /* conPeriod */ false); /* @@ -10709,7 +10806,8 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, old_check_ok, lockmode, insertTriggerOid, - updateTriggerOid); + updateTriggerOid, + with_period); table_close(partition, NoLock); } @@ -10945,7 +11043,8 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel) confdelsetcols, true, deleteTriggerOid, - updateTriggerOid); + updateTriggerOid, + constrForm->conperiod); table_close(fkRel, NoLock); ReleaseSysCache(tuple); @@ -11038,6 +11137,7 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) ListCell *lc; Oid insertTriggerOid, updateTriggerOid; + bool with_period; tuple = SearchSysCache1(CONSTROID, ObjectIdGetDatum(parentConstrOid)); if (!HeapTupleIsValid(tuple)) @@ -11153,6 +11253,7 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) fkconstraint->conname = pstrdup(NameStr(constrForm->conname)); indexOid = constrForm->conindid; + with_period = constrForm->conperiod; constrOid = CreateConstraintEntry(fkconstraint->conname, constrForm->connamespace, @@ -11184,7 +11285,7 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) false, /* islocal */ 1, /* inhcount */ false, /* conNoInherit */ - false, /* conPeriod */ + with_period, /* conPeriod */ true); /* Set up partition dependencies for the new constraint */ @@ -11218,7 +11319,8 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) false, /* no old check exists */ AccessExclusiveLock, insertTriggerOid, - updateTriggerOid); + updateTriggerOid, + with_period); table_close(pkrel, NoLock); } @@ -12039,7 +12141,7 @@ static int transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid, List **attnamelist, int16 *attnums, Oid *atttypids, - Oid *opclasses) + Oid *opclasses, bool *pk_period) { List *indexoidlist; ListCell *indexoidscan; @@ -12117,6 +12219,8 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid, makeString(pstrdup(NameStr(*attnumAttName(pkrel, pkattno))))); } + *pk_period = (indexStruct->indisexclusion); + ReleaseSysCache(indexTuple); return i; @@ -12137,7 +12241,7 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid, static Oid transformFkeyCheckAttrs(Relation pkrel, int numattrs, int16 *attnums, - Oid *opclasses) + bool with_period, Oid *opclasses) { Oid indexoid = InvalidOid; bool found = false; @@ -12184,12 +12288,13 @@ transformFkeyCheckAttrs(Relation pkrel, indexStruct = (Form_pg_index) GETSTRUCT(indexTuple); /* - * Must have the right number of columns; must be unique and not a + * Must have the right number of columns; must be unique + * (or if temporal then exclusion instead) and not a * partial index; forget it if there are any expressions, too. Invalid * indexes are out as well. */ if (indexStruct->indnkeyatts == numattrs && - indexStruct->indisunique && + (with_period ? indexStruct->indisexclusion : indexStruct->indisunique) && indexStruct->indisvalid && heap_attisnull(indexTuple, Anum_pg_index_indpred, NULL) && heap_attisnull(indexTuple, Anum_pg_index_indexprs, NULL)) @@ -12227,6 +12332,13 @@ transformFkeyCheckAttrs(Relation pkrel, if (!found) break; } + /* The last attribute in the index must be the PERIOD FK part */ + if (found && with_period) + { + int16 periodattnum = attnums[numattrs - 1]; + + found = (periodattnum == indexStruct->indkey.values[numattrs - 1]); + } /* * Refuse to use a deferrable unique/primary key. This is per SQL @@ -12336,7 +12448,8 @@ validateForeignKeyConstraint(char *conname, Relation rel, Relation pkrel, Oid pkindOid, - Oid constraintOid) + Oid constraintOid, + bool hasperiod) { TupleTableSlot *slot; TableScanDesc scan; @@ -12365,8 +12478,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 (!hasperiod && RI_Initial_Check(&trig, rel, pkrel)) return; /* @@ -12517,6 +12632,17 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr fk_trigger->whenClause = NULL; fk_trigger->transitionRels = NIL; fk_trigger->constrrel = NULL; + + /* Forbid some actions with PERIOD elements for now: */ + if (fkconstraint->fk_with_period && ( + fkconstraint->fk_del_action == FKCONSTR_ACTION_CASCADE || + fkconstraint->fk_del_action == FKCONSTR_ACTION_SETNULL || + fkconstraint->fk_del_action == FKCONSTR_ACTION_SETDEFAULT)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("invalid %s action for foreign key constraint using PERIOD", + "ON DELETE"))); + switch (fkconstraint->fk_del_action) { case FKCONSTR_ACTION_NOACTION: @@ -12577,6 +12703,17 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr fk_trigger->whenClause = NULL; fk_trigger->transitionRels = NIL; fk_trigger->constrrel = NULL; + + /* Forbid some actions with PERIOD elements for now: */ + if (fkconstraint->fk_with_period && ( + fkconstraint->fk_upd_action == FKCONSTR_ACTION_CASCADE || + fkconstraint->fk_upd_action == FKCONSTR_ACTION_SETNULL || + fkconstraint->fk_upd_action == FKCONSTR_ACTION_SETDEFAULT)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("invalid %s action for foreign key constraint using PERIOD", + "ON UPDATE"))); + switch (fkconstraint->fk_upd_action) { case FKCONSTR_ACTION_NOACTION: diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 39a801a1c38..47ec71a9ab2 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -523,12 +523,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 @@ -746,7 +747,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 @@ -4228,21 +4229,31 @@ ConstraintElem: NULL, yyscanner); $$ = (Node *) n; } - | FOREIGN KEY '(' columnList ')' REFERENCES qualified_name - opt_column_list key_match key_actions ConstraintAttributeSpec + | FOREIGN KEY '(' columnList optionalPeriodName ')' REFERENCES qualified_name + opt_column_and_period_list key_match key_actions ConstraintAttributeSpec { Constraint *n = makeNode(Constraint); n->contype = CONSTR_FOREIGN; n->location = @1; - n->pktable = $7; + n->pktable = $8; n->fk_attrs = $4; - n->pk_attrs = $8; - n->fk_matchtype = $9; - n->fk_upd_action = ($10)->updateAction->action; - n->fk_del_action = ($10)->deleteAction->action; - n->fk_del_set_cols = ($10)->deleteAction->cols; - processCASbits($11, @11, "FOREIGN KEY", + if ($5) + { + n->fk_attrs = lappend(n->fk_attrs, $5); + n->fk_with_period = true; + } + n->pk_attrs = linitial($9); + if (lsecond($9)) + { + n->pk_attrs = lappend(n->pk_attrs, lsecond($9)); + n->pk_with_period = true; + } + n->fk_matchtype = $10; + n->fk_upd_action = ($11)->updateAction->action; + n->fk_del_action = ($11)->deleteAction->action; + n->fk_del_set_cols = ($11)->deleteAction->cols; + processCASbits($12, @12, "FOREIGN KEY", &n->deferrable, &n->initdeferred, &n->skip_validation, NULL, yyscanner); @@ -4270,6 +4281,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); @@ -17342,6 +17363,7 @@ unreserved_keyword: | PARTITION | PASSING | PASSWORD + | PERIOD | PLANS | POLICY | PRECEDING @@ -17945,6 +17967,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 2fe93775003..824a41f3bf3 100644 --- a/src/backend/utils/adt/ri_triggers.c +++ b/src/backend/utils/adt/ri_triggers.c @@ -30,6 +30,7 @@ #include "access/xact.h" #include "catalog/pg_collation.h" #include "catalog/pg_constraint.h" +#include "catalog/pg_proc.h" #include "commands/trigger.h" #include "executor/executor.h" #include "executor/spi.h" @@ -45,6 +46,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" @@ -96,6 +98,9 @@ * * Information extracted from an FK pg_constraint entry. This is cached in * ri_constraint_cache. + * + * Note that pf/pp/ff_eq_oprs may hold the overlaps operator instead of equals + * for the PERIOD part of a temporal foreign key. */ typedef struct RI_ConstraintInfo { @@ -115,12 +120,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 hasperiod; /* if the foreign key uses PERIOD */ 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; /* anyrange <@ anyrange */ + Oid agged_period_contained_by_oper; /* anyrange <@ anymultirange */ dlist_node valid_link; /* Link in list of valid entries */ } RI_ConstraintInfo; @@ -362,14 +370,41 @@ RI_FKey_check(TriggerData *trigdata) * FOR KEY SHARE OF x * The type id's for the $ parameters are those of the * corresponding FK attributes. + * + * But for temporal FKs we need to make sure + * the FK's range is completely covered. + * So we use this query instead: + * SELECT 1 + * FROM ( + * SELECT pkperiodatt AS r + * FROM [ONLY] pktable x + * WHERE pkatt1 = $1 [AND ...] + * AND pkperiodatt && $n + * FOR KEY SHARE OF x + * ) x1 + * HAVING $n <@ range_agg(x1.r) + * Note if FOR KEY SHARE ever allows GROUP BY and HAVING + * we can make this a bit simpler. * ---------- */ initStringInfo(&querybuf); pk_only = pk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ? "" : "ONLY "; quoteRelationName(pkrelname, pk_rel); - appendStringInfo(&querybuf, "SELECT 1 FROM %s%s x", - pk_only, pkrelname); + if (riinfo->hasperiod) + { + 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++) { @@ -387,6 +422,19 @@ RI_FKey_check(TriggerData *trigdata) queryoids[i] = fk_type; } appendStringInfoString(&querybuf, " FOR KEY SHARE OF x"); + if (riinfo->hasperiod) + { + Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1]); + Oid agg_rettype = ANYMULTIRANGEOID; + + appendStringInfo(&querybuf, ") x1 HAVING "); + sprintf(paramname, "$%d", riinfo->nkeys); + ri_GenerateQual(&querybuf, "", + paramname, fk_type, + riinfo->agged_period_contained_by_oper, + "pg_catalog.range_agg", agg_rettype); + appendStringInfo(&querybuf, "(x1.r)"); + } /* Prepare and save the plan */ qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids, @@ -494,14 +542,40 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel, * FOR KEY SHARE OF x * The type id's for the $ parameters are those of the * PK attributes themselves. + * But for temporal FKs we need to make sure + * the FK's range is completely covered. + * So we use this query instead: + * SELECT 1 + * FROM ( + * SELECT pkperiodatt AS r + * FROM [ONLY] pktable x + * WHERE pkatt1 = $1 [AND ...] + * AND pkperiodatt && $n + * FOR KEY SHARE OF x + * ) x1 + * HAVING $n <@ range_agg(x1.r) + * Note if FOR KEY SHARE ever allows GROUP BY and HAVING + * we can make this a bit simpler. * ---------- */ initStringInfo(&querybuf); pk_only = pk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ? "" : "ONLY "; quoteRelationName(pkrelname, pk_rel); - appendStringInfo(&querybuf, "SELECT 1 FROM %s%s x", - pk_only, pkrelname); + if (riinfo->hasperiod) + { + 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++) { @@ -518,6 +592,19 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel, queryoids[i] = pk_type; } appendStringInfoString(&querybuf, " FOR KEY SHARE OF x"); + if (riinfo->hasperiod) + { + Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1]); + Oid agg_rettype = ANYMULTIRANGEOID; + + appendStringInfo(&querybuf, ") x1 HAVING "); + sprintf(paramname, "$%d", riinfo->nkeys); + ri_GenerateQual(&querybuf, "", + paramname, fk_type, + riinfo->agged_period_contained_by_oper, + "pg_catalog.range_agg", agg_rettype); + appendStringInfo(&querybuf, "(x1.r)"); + } /* Prepare and save the plan */ qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids, @@ -1210,7 +1297,6 @@ ri_set(TriggerData *trigdata, bool is_set_null, int tgkind) } } - /* * RI_FKey_pk_upd_check_required - * @@ -2168,6 +2254,7 @@ ri_LoadConstraintInfo(Oid constraintOid) riinfo->confupdtype = conForm->confupdtype; riinfo->confdeltype = conForm->confdeltype; riinfo->confmatchtype = conForm->confmatchtype; + riinfo->hasperiod = conForm->conperiod; DeconstructFkConstraintRow(tup, &riinfo->nkeys, @@ -2179,6 +2266,20 @@ ri_LoadConstraintInfo(Oid constraintOid) &riinfo->ndelsetcols, riinfo->confdelsetcols); + /* + * For temporal FKs, get the operators and functions we need. + * We ask the opclass of the PK element for these. + * This all gets cached (as does the generated plan), + * so there's no performance issue. + */ + if (riinfo->hasperiod) + { + Oid opclass = get_index_column_opclass(conForm->conindid, riinfo->nkeys); + FindFKPeriodOpers(opclass, + &riinfo->period_contained_by_oper, + &riinfo->agged_period_contained_by_oper); + } + ReleaseSysCache(tup); /* @@ -2790,7 +2891,10 @@ ri_HashPreparedPlan(RI_QueryKey *key, SPIPlanPtr plan) /* * ri_KeysEqual - * - * Check if all key values in OLD and NEW are equal. + * Check if all key values in OLD and NEW are "equivalent": + * For normal FKs we check for equality. + * For temporal FKs we check that the PK side is a superset of its old value, + * or the FK side is a subset of its old value. * * Note: at some point we might wish to redefine this as checking for * "IS NOT DISTINCT" rather than "=", that is, allow two nulls to be @@ -2846,13 +2950,23 @@ ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot, } else { + Oid eq_opr = riinfo->ff_eq_oprs[i]; + + /* + * When comparing the PERIOD columns we can skip the check + * whenever the referencing column stayed equal or shrank, + * so test with the contained-by operator instead. + */ + if (riinfo->hasperiod && i == riinfo->nkeys - 1) + eq_opr = riinfo->period_contained_by_oper; + /* * For the FK table, compare with the appropriate equality * operator. Changes that compare equal will still satisfy the * constraint after the update. */ - if (!ri_AttributesEqual(riinfo->ff_eq_oprs[i], RIAttType(rel, attnums[i]), - oldvalue, newvalue)) + if (!ri_AttributesEqual(eq_opr, RIAttType(rel, attnums[i]), + newvalue, oldvalue)) return false; } } @@ -2860,7 +2974,6 @@ ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot, return true; } - /* * ri_AttributesEqual - * diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index f2893d40861..449958f148a 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -338,7 +338,7 @@ static char *pg_get_viewdef_worker(Oid viewoid, int prettyFlags, int wrapColumn); static char *pg_get_triggerdef_worker(Oid trigid, bool pretty); static int decompile_column_index_array(Datum column_index_array, Oid relId, - StringInfo buf); + bool withPeriod, StringInfo buf); static char *pg_get_ruledef_worker(Oid ruleoid, int prettyFlags); static char *pg_get_indexdef_worker(Oid indexrelid, int colno, const Oid *excludeOps, @@ -2245,7 +2245,9 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, val = SysCacheGetAttrNotNull(CONSTROID, tup, Anum_pg_constraint_conkey); - decompile_column_index_array(val, conForm->conrelid, &buf); + /* If it is a temporal foreign key then it uses PERIOD. */ + decompile_column_index_array(val, conForm->conrelid, + conForm->conperiod, &buf); /* add foreign relation name */ appendStringInfo(&buf, ") REFERENCES %s(", @@ -2256,7 +2258,8 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, val = SysCacheGetAttrNotNull(CONSTROID, tup, Anum_pg_constraint_confkey); - decompile_column_index_array(val, conForm->confrelid, &buf); + decompile_column_index_array(val, conForm->confrelid, + conForm->conperiod, &buf); appendStringInfoChar(&buf, ')'); @@ -2342,7 +2345,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, if (!isnull) { appendStringInfoString(&buf, " ("); - decompile_column_index_array(val, conForm->conrelid, &buf); + decompile_column_index_array(val, conForm->conrelid, false, &buf); appendStringInfoChar(&buf, ')'); } @@ -2377,7 +2380,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, val = SysCacheGetAttrNotNull(CONSTROID, tup, Anum_pg_constraint_conkey); - keyatts = decompile_column_index_array(val, conForm->conrelid, &buf); + keyatts = decompile_column_index_array(val, conForm->conrelid, false, &buf); if (conForm->conperiod) appendStringInfoString(&buf, " WITHOUT OVERLAPS"); @@ -2575,7 +2578,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, */ static int decompile_column_index_array(Datum column_index_array, Oid relId, - StringInfo buf) + bool withPeriod, StringInfo buf) { Datum *keys; int nKeys; @@ -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)); } return nKeys; diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h index a33b4f17ea8..334ace5e600 100644 --- a/src/include/catalog/pg_constraint.h +++ b/src/include/catalog/pg_constraint.h @@ -108,8 +108,8 @@ CATALOG(pg_constraint,2606,ConstraintRelationId) bool connoinherit; /* - * For primary keys and unique constraints, signifies the last column uses - * overlaps instead of equals. + * For primary keys, unique constraints, and foreign keys, + * signifies the last column uses overlaps instead of equals. */ bool conperiod; @@ -127,20 +127,20 @@ CATALOG(pg_constraint,2606,ConstraintRelationId) int16 confkey[1]; /* - * If a foreign key, the OIDs of the PK = FK equality operators for each - * column of the constraint + * If a foreign key, the OIDs of the PK = FK equality/overlap operators for + * each column of the constraint */ Oid conpfeqop[1] BKI_LOOKUP(pg_operator); /* - * If a foreign key, the OIDs of the PK = PK equality operators for each - * column of the constraint (i.e., equality for the referenced columns) + * If a foreign key, the OIDs of the PK = PK equality/overlap operators for + * each column of the constraint (i.e., equality for the referenced columns) */ Oid conppeqop[1] BKI_LOOKUP(pg_operator); /* - * If a foreign key, the OIDs of the FK = FK equality operators for each - * column of the constraint (i.e., equality for the referencing columns) + * If a foreign key, the OIDs of the FK = FK equality/overlap operators for + * each column of the constraint (i.e., equality for the referencing columns) */ Oid conffeqop[1] BKI_LOOKUP(pg_operator); @@ -283,6 +283,9 @@ extern void DeconstructFkConstraintRow(HeapTuple tuple, int *numfks, AttrNumber *conkey, AttrNumber *confkey, Oid *pf_eq_oprs, Oid *pp_eq_oprs, Oid *ff_eq_oprs, int *num_fk_del_set_cols, AttrNumber *fk_del_set_cols); +extern void FindFKPeriodOpers(Oid opclass, + Oid *containedbyoperoid, + Oid *aggedcontainedbyoperoid); extern bool check_functional_grouping(Oid relid, Index varno, Index varlevelsup, diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h index 0c53d67d3ee..5fd095ea177 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -50,7 +50,7 @@ extern bool CheckIndexCompatible(Oid oldId, extern Oid GetDefaultOpClass(Oid type_id, Oid am_id); extern Oid ResolveOpClass(const List *opclass, Oid attrType, const char *accessMethodName, Oid accessMethodId); -extern void GetOperatorFromWellKnownStrategy(Oid opclass, Oid atttype, +extern void GetOperatorFromWellKnownStrategy(Oid opclass, Oid rhstype, Oid *opid, StrategyNumber *strat); /* commands/functioncmds.c */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 1e2e898851f..d43c0817fbd 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2614,6 +2614,8 @@ typedef struct Constraint RangeVar *pktable; /* Primary key table */ List *fk_attrs; /* Attributes of foreign key */ List *pk_attrs; /* Corresponding attrs in PK table */ + bool fk_with_period; /* Last attribute of FK uses PERIOD */ + bool pk_with_period; /* Last attribute of PK uses PERIOD */ char fk_matchtype; /* FULL, PARTIAL, SIMPLE */ char fk_upd_action; /* ON UPDATE action */ char fk_del_action; /* ON DELETE action */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 099353469b5..d45b0b38842 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -327,6 +327,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, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD, BARE_LABEL) diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out index 9fb20395a6a..05f0cd5a595 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -99,21 +99,6 @@ CREATE TABLE temporal_rng3 ( ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk; DROP TABLE temporal_rng3; DROP TYPE textrange2; --- PK with a multirange: -CREATE TABLE temporal_mltrng ( - id int4range, - valid_at tsmultirange, - CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -\d temporal_mltrng - Table "public.temporal_mltrng" - Column | Type | Collation | Nullable | Default -----------+--------------+-----------+----------+--------- - id | int4range | | not null | - valid_at | tsmultirange | | not null | -Indexes: - "temporal_mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) - -- UNIQUE with no columns just WITHOUT OVERLAPS: CREATE TABLE temporal_rng3 ( valid_at daterange, @@ -394,4 +379,633 @@ 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 daterange, + CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at daterange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal3 (id, PERIOD valid_at) +); +ALTER TABLE temporal3 DROP COLUMN valid_at; +ERROR: cannot drop column valid_at of table temporal3 because other objects depend on it +DETAIL: constraint temporal_fk_rng2rng_fk on table temporal_fk_rng2rng depends on column valid_at of table temporal3 +HINT: Use DROP ... CASCADE to drop the dependent objects too. +ALTER TABLE temporal3 DROP COLUMN valid_at CASCADE; +NOTICE: drop cascades to constraint temporal_fk_rng2rng_fk on table temporal_fk_rng2rng +DROP TABLE temporal_fk_rng2rng; +DROP TABLE temporal3; +-- +-- test FOREIGN KEY, range references range +-- +-- test table setup +DROP TABLE temporal_rng; +CREATE TABLE temporal_rng (id int4range, valid_at daterange); +ALTER TABLE temporal_rng + ADD CONSTRAINT temporal_rng_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +-- Can't create a FK with a mismatched range type +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at int4range, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk2 PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk2 FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng (id, PERIOD valid_at) +); +ERROR: foreign key constraint "temporal_fk_rng2rng_fk2" cannot be implemented +DETAIL: Key columns "valid_at" and "valid_at" are of incompatible types: int4range and daterange. +-- works: PERIOD for both referenced and referencing +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at daterange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng (id, PERIOD valid_at) +); +DROP TABLE temporal_fk_rng2rng; +-- with mismatched PERIOD columns: +-- (parent_id, PERIOD valid_at) REFERENCES (id, valid_at) +-- REFERENCES part should specify PERIOD +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at daterange, + 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, valid_at) +-- both should specify PERIOD: +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at daterange, + 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, valid_at) +); +ERROR: only b-tree indexes are supported for non-PERIOD foreign keys +-- (parent_id, valid_at) REFERENCES (id, PERIOD valid_at) +-- FOREIGN KEY part should specify PERIOD +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at daterange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at) + REFERENCES temporal_rng (id, PERIOD valid_at) +); +ERROR: foreign key uses PERIOD on the referenced table but not the referencing table +-- (parent_id, valid_at) REFERENCES [implicit] +-- FOREIGN KEY part should specify PERIOD +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at daterange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at) + REFERENCES temporal_rng +); +ERROR: foreign key uses PERIOD on the referenced table but not the referencing table +-- (parent_id, PERIOD valid_at) REFERENCES (id) +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at daterange, + 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 daterange, + 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: +-- (parent_id, PERIOD valid_at) REFERENCES [implicit] +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at daterange, + 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; +-- (parent_id) REFERENCES [implicit] +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at daterange, + 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 +); +ERROR: foreign key uses PERIOD on the referenced table but not the referencing table +-- should fail because of duplicate referenced columns: +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at daterange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD parent_id) + REFERENCES temporal_rng (id, PERIOD id) +); +ERROR: foreign key referenced-columns list must not contain duplicates +-- Two scalar columns +DROP TABLE temporal_rng2; +CREATE TABLE temporal_rng2 ( + id1 int4range, + id2 int4range, + valid_at daterange, + CONSTRAINT temporal_rng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) +); +CREATE TABLE temporal_fk2_rng2rng ( + id int4range, + valid_at daterange, + 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 | daterange | | 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 daterange, + 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 daterange, + 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 | daterange | | 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 tsrange USING tsrange(lower(valid_at), upper(valid_at)); +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: tsrange and daterange. +ALTER TABLE temporal_fk_rng2rng + ALTER COLUMN valid_at TYPE daterange USING daterange(lower(valid_at)::date, upper(valid_at)::date); +-- with inferred PK on the referenced table: +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng; +-- should fail because of duplicate referenced columns: +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk2 + FOREIGN KEY (parent_id, PERIOD parent_id) + REFERENCES temporal_rng (id, PERIOD id); +ERROR: foreign key referenced-columns list must not contain duplicates +-- +-- test with rows already +-- +DELETE FROM temporal_fk_rng2rng; +DELETE FROM temporal_rng; +INSERT INTO temporal_rng (id, valid_at) VALUES + ('[1,2)', daterange('2018-01-02', '2018-02-03')), + ('[1,2)', daterange('2018-03-03', '2018-04-04')), + ('[2,3)', daterange('2018-01-01', '2018-01-05')), + ('[3,4)', daterange('2018-01-01', NULL)); +ALTER TABLE temporal_fk_rng2rng + DROP CONSTRAINT temporal_fk_rng2rng_fk; +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-01'), '[1,2)'); +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 (id, valid_at, parent_id) VALUES ('[2,3)', daterange('2018-01-02', '2018-04-01'), '[1,2)'); +-- 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), [2018-01-02,2018-04-01)) 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 referencing inserts +-- +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-01'), '[1,2)'); +-- should fail: +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[2,3)', daterange('2018-01-02', '2018-04-01'), '[1,2)'); +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), [2018-01-02,2018-04-01)) is not present in table "temporal_rng". +-- now it should work: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-02-03', '2018-03-03')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[2,3)', daterange('2018-01-02', '2018-04-01'), '[1,2)'); +-- +-- test FK referencing updates +-- +UPDATE temporal_fk_rng2rng SET valid_at = daterange('2018-01-02', '2018-03-01') WHERE id = '[1,2)'; +-- should fail: +UPDATE temporal_fk_rng2rng SET valid_at = daterange('2018-01-02', '2018-05-01') WHERE id = '[1,2)'; +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), [2018-01-02,2018-05-01)) is not present in table "temporal_rng". +UPDATE temporal_fk_rng2rng SET parent_id = '[8,9)' WHERE id = '[1,2)'; +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), [2018-01-02,2018-03-01)) is not present in table "temporal_rng". +-- ALTER FK DEFERRABLE +BEGIN; + INSERT INTO temporal_rng (id, valid_at) VALUES + ('[5,6)', daterange('2018-01-01', '2018-02-01')), + ('[5,6)', daterange('2018-02-01', '2018-03-01')); + INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES + ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); + ALTER TABLE temporal_fk_rng2rng + ALTER CONSTRAINT temporal_fk_rng2rng_fk + DEFERRABLE INITIALLY DEFERRED; + DELETE FROM temporal_rng WHERE id = '[5,6)'; --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), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". +-- +-- test FK referenced updates NO ACTION +-- +TRUNCATE temporal_rng, temporal_fk_rng2rng; +-- a PK update that succeeds because the numeric id isn't referenced: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); +UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') WHERE id = '[5,6)'; +-- a PK update that succeeds even though the numeric id is referenced because the range isn't: +DELETE FROM temporal_rng WHERE id = '[5,6)'; +INSERT INTO temporal_rng (id, valid_at) VALUES + ('[5,6)', daterange('2018-01-01', '2018-02-01')), + ('[5,6)', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); +UPDATE temporal_rng SET valid_at = daterange('2016-02-01', '2016-03-01') +WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); +-- a PK update that fails because both are referenced: +UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') +WHERE id = '[5,6)' AND valid_at = daterange('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), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". +-- changing the scalar part fails: +UPDATE temporal_rng SET id = '[7,8)' +WHERE id = '[5,6)' AND valid_at = daterange('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), [2018-01-01,2018-02-01)) 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,4)'; +UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') +WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- +-- test FK referenced updates RESTRICT +-- +TRUNCATE temporal_rng, temporal_fk_rng2rng; +ALTER TABLE temporal_fk_rng2rng + DROP CONSTRAINT temporal_fk_rng2rng_fk; +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng + ON UPDATE RESTRICT; +-- a PK update that succeeds because the numeric id isn't referenced: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); +UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') WHERE id = '[5,6)'; +-- a PK update that succeeds even though the numeric id is referenced because the range isn't: +DELETE FROM temporal_rng WHERE id = '[5,6)'; +INSERT INTO temporal_rng (id, valid_at) VALUES + ('[5,6)', daterange('2018-01-01', '2018-02-01')), + ('[5,6)', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); +UPDATE temporal_rng SET valid_at = daterange('2016-02-01', '2016-03-01') +WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); +-- a PK update that fails because both are referenced: +UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') +WHERE id = '[5,6)' AND valid_at = daterange('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), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". +-- changing the scalar part fails: +UPDATE temporal_rng SET id = '[7,8)' +WHERE id = '[5,6)' AND valid_at = daterange('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), [2018-01-01,2018-02-01)) 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,4)'; +UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') +WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- +-- test FK referenced deletes NO ACTION +-- +TRUNCATE temporal_rng, temporal_fk_rng2rng; +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 (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); +DELETE FROM temporal_rng WHERE id = '[5,6)'; +-- a PK delete that succeeds even though the numeric id is referenced because the range isn't: +INSERT INTO temporal_rng (id, valid_at) VALUES + ('[5,6)', daterange('2018-01-01', '2018-02-01')), + ('[5,6)', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); +DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); +-- a PK delete that fails because both are referenced: +DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('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), [2018-01-01,2018-02-01)) 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,4)'; +DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- +-- test FK referenced deletes RESTRICT +-- +TRUNCATE temporal_rng, temporal_fk_rng2rng; +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 (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); +DELETE FROM temporal_rng WHERE id = '[5,6)'; +-- a PK delete that succeeds even though the numeric id is referenced because the range isn't: +INSERT INTO temporal_rng (id, valid_at) VALUES + ('[5,6)', daterange('2018-01-01', '2018-02-01')), + ('[5,6)', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); +DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); +-- a PK delete that fails because both are referenced: +DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('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), [2018-01-01,2018-02-01)) 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,4)'; +DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- +-- test ON UPDATE/DELETE options +-- +-- test FK referenced updates CASCADE +INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[4,5)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); +ALTER TABLE temporal_fk_rng2rng + DROP CONSTRAINT temporal_fk_rng2rng_fk, + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng + ON DELETE CASCADE ON UPDATE CASCADE; +ERROR: invalid ON DELETE action for foreign key constraint using PERIOD +-- test FK referenced updates SET NULL +INSERT INTO temporal_rng (id, valid_at) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'), '[9,10)'); +ALTER TABLE temporal_fk_rng2rng + DROP CONSTRAINT temporal_fk_rng2rng_fk, + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng + ON DELETE SET NULL ON UPDATE SET NULL; +ERROR: invalid ON DELETE action for foreign key constraint using PERIOD +-- test FK referenced updates SET DEFAULT +INSERT INTO temporal_rng (id, valid_at) VALUES ('[-1,-1]', daterange(null, null)); +INSERT INTO temporal_rng (id, valid_at) VALUES ('[12,13)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01'), '[12,13)'); +ALTER TABLE temporal_fk_rng2rng + ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', + DROP CONSTRAINT temporal_fk_rng2rng_fk, + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng + ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; +ERROR: invalid ON DELETE action for foreign key constraint using PERIOD +-- +-- 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,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)'); +CREATE TABLE tp2 partition OF temporal_partitioned_rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)'); +INSERT INTO temporal_partitioned_rng (id, valid_at, name) VALUES + ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), + ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), + ('[2,3)', 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,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)'); +CREATE TABLE tfkp2 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)'); +-- +-- partitioned FK referencing inserts +-- +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES + ('[1,2)', daterange('2000-01-01', '2000-02-15'), '[1,2)'), + ('[1,2)', daterange('2001-01-01', '2002-01-01'), '[2,3)'), + ('[2,3)', daterange('2000-01-01', '2000-02-15'), '[1,2)'); +-- should fail: +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES + ('[3,4)', daterange('2010-01-01', '2010-02-15'), '[1,2)'); +ERROR: insert or update on table "tfkp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=([1,2), [2010-01-01,2010-02-15)) is not present in table "temporal_partitioned_rng". +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES + ('[3,4)', daterange('2000-01-01', '2000-02-15'), '[3,4)'); +ERROR: insert or update on table "tfkp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=([3,4), [2000-01-01,2000-02-15)) is not present in table "temporal_partitioned_rng". +-- +-- partitioned FK referencing updates +-- +UPDATE temporal_partitioned_fk_rng2rng SET valid_at = daterange('2000-01-01', '2000-02-13') WHERE id = '[2,3)'; +-- move a row from the first partition to the second +UPDATE temporal_partitioned_fk_rng2rng SET id = '[4,5)' WHERE id = '[1,2)'; +-- move a row from the second partition to the first +UPDATE temporal_partitioned_fk_rng2rng SET id = '[1,2)' WHERE id = '[4,5)'; +-- should fail: +UPDATE temporal_partitioned_fk_rng2rng SET valid_at = daterange('2000-01-01', '2000-04-01') WHERE id = '[1,2)'; +ERROR: conflicting key value violates exclusion constraint "tfkp1_pkey" +DETAIL: Key (id, valid_at)=([1,2), [2000-01-01,2000-04-01)) conflicts with existing key (id, valid_at)=([1,2), [2000-01-01,2000-04-01)). +-- +-- partitioned FK referenced updates NO ACTION +-- +TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2016-01-01', '2016-02-01')); +UPDATE temporal_partitioned_rng SET valid_at = daterange('2018-01-01', '2018-02-01') WHERE id = '[5,6)'; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); +UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-02-01', '2016-03-01') + WHERE id = '[5,6)' 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,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey" on table "temporal_partitioned_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_partitioned_fk_rng2rng". +-- +-- partitioned FK referenced deletes NO ACTION +-- +TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); +DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); +-- should fail: +DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey" on table "temporal_partitioned_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_partitioned_fk_rng2rng". +-- +-- partitioned FK referenced updates RESTRICT +-- +TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; +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 (id, valid_at) VALUES ('[5,6)', daterange('2016-01-01', '2016-02-01')); +UPDATE temporal_partitioned_rng SET valid_at = daterange('2018-01-01', '2018-02-01') WHERE id = '[5,6)'; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); +UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-02-01', '2016-03-01') + WHERE id = '[5,6)' 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,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey" on table "temporal_partitioned_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_partitioned_fk_rng2rng". +-- +-- partitioned FK referenced deletes RESTRICT +-- +TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); +DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); +-- should fail: +DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey" on table "temporal_partitioned_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_partitioned_fk_rng2rng". +-- +-- partitioned FK referenced updates CASCADE +-- +ALTER TABLE temporal_partitioned_fk_rng2rng + DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, + ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_partitioned_rng + ON DELETE CASCADE ON UPDATE CASCADE; +ERROR: invalid ON DELETE action for foreign key constraint using PERIOD +-- +-- partitioned FK referenced deletes CASCADE +-- +-- +-- partitioned FK referenced updates SET NULL +-- +ALTER TABLE temporal_partitioned_fk_rng2rng + DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, + ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_partitioned_rng + ON DELETE SET NULL ON UPDATE SET NULL; +ERROR: invalid ON DELETE action for foreign key constraint using PERIOD +-- +-- partitioned FK referenced deletes SET NULL +-- +-- +-- partitioned FK referenced updates SET DEFAULT +-- +ALTER TABLE temporal_partitioned_fk_rng2rng + ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', + DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, + ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_partitioned_rng + ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; +ERROR: invalid ON DELETE action for foreign key constraint using PERIOD +-- +-- partitioned FK referenced deletes SET DEFAULT +-- +DROP TABLE temporal_partitioned_fk_rng2rng; +DROP TABLE temporal_partitioned_rng; RESET datestyle; diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql index b5e77168f54..d904a286853 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -58,7 +58,6 @@ CREATE TABLE temporal_rng2 ( SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng2_pk'; SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng2_pk'; - -- PK with a custom range type: CREATE TYPE textrange2 AS range (subtype=text, collation="C"); CREATE TABLE temporal_rng3 ( @@ -70,14 +69,6 @@ ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk; DROP TABLE temporal_rng3; DROP TYPE textrange2; --- PK with a multirange: -CREATE TABLE temporal_mltrng ( - id int4range, - valid_at tsmultirange, - CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) -); -\d temporal_mltrng - -- UNIQUE with no columns just WITHOUT OVERLAPS: CREATE TABLE temporal_rng3 ( @@ -291,4 +282,610 @@ 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 daterange, + CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at daterange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal3 (id, PERIOD valid_at) +); +ALTER TABLE temporal3 DROP COLUMN valid_at; +ALTER TABLE temporal3 DROP COLUMN valid_at CASCADE; +DROP TABLE temporal_fk_rng2rng; +DROP TABLE temporal3; + +-- +-- test FOREIGN KEY, range references range +-- + +-- test table setup +DROP TABLE temporal_rng; +CREATE TABLE temporal_rng (id int4range, valid_at daterange); +ALTER TABLE temporal_rng + ADD CONSTRAINT temporal_rng_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); + +-- Can't create a FK with a mismatched range type +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at int4range, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk2 PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk2 FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng (id, PERIOD valid_at) +); + +-- works: PERIOD for both referenced and referencing +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at daterange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng (id, PERIOD valid_at) +); +DROP TABLE temporal_fk_rng2rng; + +-- with mismatched PERIOD columns: + +-- (parent_id, PERIOD valid_at) REFERENCES (id, valid_at) +-- REFERENCES part should specify PERIOD +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at daterange, + 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, valid_at) +-- both should specify PERIOD: +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at daterange, + 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, valid_at) +); +-- (parent_id, valid_at) REFERENCES (id, PERIOD valid_at) +-- FOREIGN KEY part should specify PERIOD +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at daterange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at) + REFERENCES temporal_rng (id, PERIOD valid_at) +); +-- (parent_id, valid_at) REFERENCES [implicit] +-- FOREIGN KEY part should specify PERIOD +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at daterange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at) + REFERENCES temporal_rng +); +-- (parent_id, PERIOD valid_at) REFERENCES (id) +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at daterange, + 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 daterange, + 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: +-- (parent_id, PERIOD valid_at) REFERENCES [implicit] +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at daterange, + 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; +-- (parent_id) REFERENCES [implicit] +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at daterange, + 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 +); + +-- should fail because of duplicate referenced columns: +CREATE TABLE temporal_fk_rng2rng ( + id int4range, + valid_at daterange, + parent_id int4range, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD parent_id) + REFERENCES temporal_rng (id, PERIOD id) +); + +-- Two scalar columns +DROP TABLE temporal_rng2; +CREATE TABLE temporal_rng2 ( + id1 int4range, + id2 int4range, + valid_at daterange, + CONSTRAINT temporal_rng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) +); + +CREATE TABLE temporal_fk2_rng2rng ( + id int4range, + valid_at daterange, + 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 daterange, + 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 daterange, + 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 tsrange USING tsrange(lower(valid_at), upper(valid_at)); +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 daterange USING daterange(lower(valid_at)::date, upper(valid_at)::date); + +-- with inferred PK on the referenced table: +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng; + +-- should fail because of duplicate referenced columns: +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk2 + FOREIGN KEY (parent_id, PERIOD parent_id) + REFERENCES temporal_rng (id, PERIOD id); + +-- +-- test with rows already +-- + +DELETE FROM temporal_fk_rng2rng; +DELETE FROM temporal_rng; +INSERT INTO temporal_rng (id, valid_at) VALUES + ('[1,2)', daterange('2018-01-02', '2018-02-03')), + ('[1,2)', daterange('2018-03-03', '2018-04-04')), + ('[2,3)', daterange('2018-01-01', '2018-01-05')), + ('[3,4)', daterange('2018-01-01', NULL)); + +ALTER TABLE temporal_fk_rng2rng + DROP CONSTRAINT temporal_fk_rng2rng_fk; +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-01'), '[1,2)'); +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 (id, valid_at, parent_id) VALUES ('[2,3)', daterange('2018-01-02', '2018-04-01'), '[1,2)'); +-- 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 referencing inserts +-- + +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-01'), '[1,2)'); +-- should fail: +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[2,3)', daterange('2018-01-02', '2018-04-01'), '[1,2)'); +-- now it should work: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-02-03', '2018-03-03')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[2,3)', daterange('2018-01-02', '2018-04-01'), '[1,2)'); + +-- +-- test FK referencing updates +-- + +UPDATE temporal_fk_rng2rng SET valid_at = daterange('2018-01-02', '2018-03-01') WHERE id = '[1,2)'; +-- should fail: +UPDATE temporal_fk_rng2rng SET valid_at = daterange('2018-01-02', '2018-05-01') WHERE id = '[1,2)'; +UPDATE temporal_fk_rng2rng SET parent_id = '[8,9)' WHERE id = '[1,2)'; + +-- ALTER FK DEFERRABLE + +BEGIN; + INSERT INTO temporal_rng (id, valid_at) VALUES + ('[5,6)', daterange('2018-01-01', '2018-02-01')), + ('[5,6)', daterange('2018-02-01', '2018-03-01')); + INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES + ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); + ALTER TABLE temporal_fk_rng2rng + ALTER CONSTRAINT temporal_fk_rng2rng_fk + DEFERRABLE INITIALLY DEFERRED; + + DELETE FROM temporal_rng WHERE id = '[5,6)'; --should not fail yet. +COMMIT; -- should fail here. + +-- +-- test FK referenced updates NO ACTION +-- + +TRUNCATE temporal_rng, temporal_fk_rng2rng; +-- a PK update that succeeds because the numeric id isn't referenced: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); +UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') WHERE id = '[5,6)'; +-- a PK update that succeeds even though the numeric id is referenced because the range isn't: +DELETE FROM temporal_rng WHERE id = '[5,6)'; +INSERT INTO temporal_rng (id, valid_at) VALUES + ('[5,6)', daterange('2018-01-01', '2018-02-01')), + ('[5,6)', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); +UPDATE temporal_rng SET valid_at = daterange('2016-02-01', '2016-03-01') +WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); +-- a PK update that fails because both are referenced: +UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') +WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- changing the scalar part fails: +UPDATE temporal_rng SET id = '[7,8)' +WHERE id = '[5,6)' AND valid_at = daterange('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,4)'; +UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') +WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); + +-- +-- test FK referenced updates RESTRICT +-- + +TRUNCATE temporal_rng, temporal_fk_rng2rng; +ALTER TABLE temporal_fk_rng2rng + DROP CONSTRAINT temporal_fk_rng2rng_fk; +ALTER TABLE temporal_fk_rng2rng + ADD CONSTRAINT temporal_fk_rng2rng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng + ON UPDATE RESTRICT; +-- a PK update that succeeds because the numeric id isn't referenced: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); +UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') WHERE id = '[5,6)'; +-- a PK update that succeeds even though the numeric id is referenced because the range isn't: +DELETE FROM temporal_rng WHERE id = '[5,6)'; +INSERT INTO temporal_rng (id, valid_at) VALUES + ('[5,6)', daterange('2018-01-01', '2018-02-01')), + ('[5,6)', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); +UPDATE temporal_rng SET valid_at = daterange('2016-02-01', '2016-03-01') +WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); +-- a PK update that fails because both are referenced: +UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') +WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- changing the scalar part fails: +UPDATE temporal_rng SET id = '[7,8)' +WHERE id = '[5,6)' AND valid_at = daterange('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,4)'; +UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') +WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); + +-- +-- test FK referenced deletes NO ACTION +-- + +TRUNCATE temporal_rng, temporal_fk_rng2rng; +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 (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); +DELETE FROM temporal_rng WHERE id = '[5,6)'; +-- a PK delete that succeeds even though the numeric id is referenced because the range isn't: +INSERT INTO temporal_rng (id, valid_at) VALUES + ('[5,6)', daterange('2018-01-01', '2018-02-01')), + ('[5,6)', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); +DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); +-- a PK delete that fails because both are referenced: +DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('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,4)'; +DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); + +-- +-- test FK referenced deletes RESTRICT +-- + +TRUNCATE temporal_rng, temporal_fk_rng2rng; +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 (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); +DELETE FROM temporal_rng WHERE id = '[5,6)'; +-- a PK delete that succeeds even though the numeric id is referenced because the range isn't: +INSERT INTO temporal_rng (id, valid_at) VALUES + ('[5,6)', daterange('2018-01-01', '2018-02-01')), + ('[5,6)', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); +DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); +-- a PK delete that fails because both are referenced: +DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('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,4)'; +DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); + +-- +-- test ON UPDATE/DELETE options +-- + +-- test FK referenced updates CASCADE +INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[4,5)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); +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 referenced updates SET NULL +INSERT INTO temporal_rng (id, valid_at) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'), '[9,10)'); +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 referenced updates SET DEFAULT +INSERT INTO temporal_rng (id, valid_at) VALUES ('[-1,-1]', daterange(null, null)); +INSERT INTO temporal_rng (id, valid_at) VALUES ('[12,13)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01'), '[12,13)'); +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,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)'); +CREATE TABLE tp2 partition OF temporal_partitioned_rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)'); +INSERT INTO temporal_partitioned_rng (id, valid_at, name) VALUES + ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), + ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), + ('[2,3)', 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,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)'); +CREATE TABLE tfkp2 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)'); + +-- +-- partitioned FK referencing inserts +-- + +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES + ('[1,2)', daterange('2000-01-01', '2000-02-15'), '[1,2)'), + ('[1,2)', daterange('2001-01-01', '2002-01-01'), '[2,3)'), + ('[2,3)', daterange('2000-01-01', '2000-02-15'), '[1,2)'); +-- should fail: +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES + ('[3,4)', daterange('2010-01-01', '2010-02-15'), '[1,2)'); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES + ('[3,4)', daterange('2000-01-01', '2000-02-15'), '[3,4)'); + +-- +-- partitioned FK referencing updates +-- + +UPDATE temporal_partitioned_fk_rng2rng SET valid_at = daterange('2000-01-01', '2000-02-13') WHERE id = '[2,3)'; +-- move a row from the first partition to the second +UPDATE temporal_partitioned_fk_rng2rng SET id = '[4,5)' WHERE id = '[1,2)'; +-- move a row from the second partition to the first +UPDATE temporal_partitioned_fk_rng2rng SET id = '[1,2)' WHERE id = '[4,5)'; +-- should fail: +UPDATE temporal_partitioned_fk_rng2rng SET valid_at = daterange('2000-01-01', '2000-04-01') WHERE id = '[1,2)'; + +-- +-- partitioned FK referenced updates NO ACTION +-- + +TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2016-01-01', '2016-02-01')); +UPDATE temporal_partitioned_rng SET valid_at = daterange('2018-01-01', '2018-02-01') WHERE id = '[5,6)'; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); +UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-02-01', '2016-03-01') + WHERE id = '[5,6)' 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,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); + +-- +-- partitioned FK referenced deletes NO ACTION +-- + +TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); +DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); +-- should fail: +DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); + +-- +-- partitioned FK referenced updates RESTRICT +-- + +TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; +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 (id, valid_at) VALUES ('[5,6)', daterange('2016-01-01', '2016-02-01')); +UPDATE temporal_partitioned_rng SET valid_at = daterange('2018-01-01', '2018-02-01') WHERE id = '[5,6)'; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); +UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-02-01', '2016-03-01') + WHERE id = '[5,6)' 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,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); + +-- +-- partitioned FK referenced deletes RESTRICT +-- + +TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); +DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); +-- should fail: +DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); + +-- +-- partitioned FK referenced 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 referenced deletes CASCADE +-- + +-- +-- partitioned FK referenced 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 referenced deletes SET NULL +-- + +-- +-- partitioned FK referenced 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 referenced deletes SET DEFAULT +-- + +DROP TABLE temporal_partitioned_fk_rng2rng; +DROP TABLE temporal_partitioned_rng; + RESET datestyle; -- 2.42.0