aboutsummaryrefslogtreecommitdiff
path: root/src/backend/commands/tablecmds.c
diff options
context:
space:
mode:
Diffstat (limited to 'src/backend/commands/tablecmds.c')
-rw-r--r--src/backend/commands/tablecmds.c226
1 files changed, 186 insertions, 40 deletions
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 818ed5702cf..022ddf172a3 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"
@@ -215,6 +216,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 */
@@ -389,16 +391,17 @@ 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_has_without_overlaps);
static Oid transformFkeyCheckAttrs(Relation pkrel,
int numattrs, int16 *attnums,
- Oid *opclasses);
+ bool with_period, Oid *opclasses,
+ bool *pk_has_without_overlaps);
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 CheckAlterTableIsSafe(Relation rel);
static void ATController(AlterTableStmt *parsetree,
Relation rel, List *cmds, bool recurse, LOCKMODE lockmode,
@@ -510,7 +513,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);
@@ -520,7 +524,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);
@@ -5887,7 +5893,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
@@ -9534,6 +9541,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_has_without_overlaps;
int i;
int numfks,
numpks,
@@ -9628,6 +9637,11 @@ 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 && !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,
@@ -9647,19 +9661,41 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
numpks = transformFkeyGetPrimaryKey(pkrel, &indexOid,
&fkconstraint->pk_attrs,
pkattnum, pktypoid,
- opclasses);
+ opclasses, &pk_has_without_overlaps);
+
+ /* If the primary key uses WITHOUT OVERLAPS, the fk must use PERIOD */
+ if (pk_has_without_overlaps && !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, &pk_has_without_overlaps);
}
/*
+ * If the referenced primary key has WITHOUT OVERLAPS, the foreign key
+ * must use PERIOD.
+ */
+ if (pk_has_without_overlaps && !with_period)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_FOREIGN_KEY),
+ errmsg("foreign key must use PERIOD when referencing a primary using WITHOUT OVERLAPS"));
+
+ /*
* Now we can check permissions.
*/
checkFkeyPermissions(pkrel, pkattnum, numpks);
@@ -9693,6 +9729,28 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
}
/*
+ * Some actions are currently unsupported for foreign keys using PERIOD.
+ */
+ if (fkconstraint->fk_with_period)
+ {
+ if (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("unsupported %s action for foreign key constraint using PERIOD",
+ "ON UPDATE"));
+
+ if (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("unsupported %s action for foreign key constraint using PERIOD",
+ "ON DELETE"));
+ }
+
+ /*
* Look up the equality operators to use in the constraint.
*
* Note that we have to be careful about the difference between the actual
@@ -9738,16 +9796,56 @@ 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)
+ {
+ StrategyNumber rtstrategy;
+ bool for_overlaps = with_period && i == numpks - 1;
+
+ /*
+ * 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");
+
+ rtstrategy = for_overlaps ? RTOverlapStrategyNumber : 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)
+ {
+ HeapTuple tuple;
+
+ tuple = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclasses[i]));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for operator class %u", opclasses[i]);
+
+ ereport(ERROR,
+ errcode(ERRCODE_UNDEFINED_OBJECT),
+ for_overlaps
+ ? errmsg("could not identify an overlaps operator for foreign key")
+ : errmsg("could not identify an equality operator for foreign key"),
+ errdetail("Could not translate strategy number %d for operator class \"%s\" for access method \"%s\".",
+ rtstrategy, NameStr(((Form_pg_opclass) GETSTRUCT(tuple))->opcname), "gist"));
+ }
+ }
+ else
+ {
+ /*
+ * Check it's a btree; currently this can never fail since no
+ * other index AMs support unique indexes. If we ever did have
+ * other types of unique indexes, we'd need a way to determine
+ * which operator strategy number is equality. (We could use
+ * something like GistTranslateStratnum.)
+ */
+ if (amid != BTREE_AM_OID)
+ elog(ERROR, "only b-tree indexes are supported for foreign keys");
+ eqstrategy = BTEqualStrategyNumber;
+ }
/*
* There had better be a primary equality operator for the index.
@@ -9898,6 +9996,22 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
}
/*
+ * For FKs with PERIOD we need additional operators to check whether the
+ * referencing row's range is contained by the aggregated ranges of the
+ * referenced row(s). For rangetypes and multirangetypes this is
+ * fk.periodatt <@ range_agg(pk.periodatt). Those are the only types we
+ * support for now. 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.
*/
@@ -9913,7 +10027,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,
@@ -9929,7 +10044,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.
@@ -10014,7 +10130,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;
@@ -10100,7 +10217,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);
@@ -10176,7 +10293,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);
@@ -10234,7 +10352,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;
@@ -10282,6 +10401,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);
@@ -10399,7 +10519,7 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel,
false,
1,
false,
- false, /* conPeriod */
+ with_period, /* conPeriod */
false);
/*
@@ -10430,7 +10550,8 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel,
old_check_ok,
lockmode,
insertTriggerOid,
- updateTriggerOid);
+ updateTriggerOid,
+ with_period);
table_close(partition, NoLock);
}
@@ -10666,7 +10787,8 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel)
confdelsetcols,
true,
deleteTriggerOid,
- updateTriggerOid);
+ updateTriggerOid,
+ constrForm->conperiod);
table_close(fkRel, NoLock);
ReleaseSysCache(tuple);
@@ -10776,6 +10898,7 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
ListCell *lc;
Oid insertTriggerOid,
updateTriggerOid;
+ bool with_period;
tuple = SearchSysCache1(CONSTROID, ObjectIdGetDatum(parentConstrOid));
if (!HeapTupleIsValid(tuple))
@@ -10891,6 +11014,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,
@@ -10922,7 +11046,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 */
@@ -10956,7 +11080,8 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
false, /* no old check exists */
AccessExclusiveLock,
insertTriggerOid,
- updateTriggerOid);
+ updateTriggerOid,
+ with_period);
table_close(pkrel, NoLock);
}
@@ -11767,7 +11892,8 @@ transformColumnNameList(Oid relId, List *colList,
*
* Look up the names, attnums, and types of the primary key attributes
* for the pkrel. Also return the index OID and index opclasses of the
- * index supporting the primary key.
+ * index supporting the primary key. Also return whether the index has
+ * WITHOUT OVERLAPS.
*
* All parameters except pkrel are output parameters. Also, the function
* return value is the number of attributes in the primary key.
@@ -11778,7 +11904,7 @@ static int
transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
List **attnamelist,
int16 *attnums, Oid *atttypids,
- Oid *opclasses)
+ Oid *opclasses, bool *pk_has_without_overlaps)
{
List *indexoidlist;
ListCell *indexoidscan;
@@ -11856,6 +11982,8 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
makeString(pstrdup(NameStr(*attnumAttName(pkrel, pkattno)))));
}
+ *pk_has_without_overlaps = indexStruct->indisexclusion;
+
ReleaseSysCache(indexTuple);
return i;
@@ -11869,14 +11997,16 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
*
* Returns the OID of the unique index supporting the constraint and
* populates the caller-provided 'opclasses' array with the opclasses
- * associated with the index columns.
+ * associated with the index columns. Also sets whether the index
+ * uses WITHOUT OVERLAPS.
*
* Raises an ERROR on validation failure.
*/
static Oid
transformFkeyCheckAttrs(Relation pkrel,
int numattrs, int16 *attnums,
- Oid *opclasses)
+ bool with_period, Oid *opclasses,
+ bool *pk_has_without_overlaps)
{
Oid indexoid = InvalidOid;
bool found = false;
@@ -11923,12 +12053,12 @@ transformFkeyCheckAttrs(Relation pkrel,
indexStruct = (Form_pg_index) GETSTRUCT(indexTuple);
/*
- * Must have the right number of columns; must be unique and not a
- * partial index; forget it if there are any expressions, too. Invalid
- * indexes are out as well.
+ * Must have the right number of columns; must be unique (or if
+ * temporal then exclusion instead) and not a partial index; forget it
+ * if there are any expressions, too. Invalid indexes are out as well.
*/
if (indexStruct->indnkeyatts == numattrs &&
- indexStruct->indisunique &&
+ (with_period ? indexStruct->indisexclusion : indexStruct->indisunique) &&
indexStruct->indisvalid &&
heap_attisnull(indexTuple, Anum_pg_index_indpred, NULL) &&
heap_attisnull(indexTuple, Anum_pg_index_indexprs, NULL))
@@ -11966,6 +12096,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
@@ -11981,6 +12118,10 @@ transformFkeyCheckAttrs(Relation pkrel,
found_deferrable = true;
found = false;
}
+
+ /* We need to know whether the index has WITHOUT OVERLAPS */
+ if (found)
+ *pk_has_without_overlaps = indexStruct->indisexclusion;
}
ReleaseSysCache(indexTuple);
if (found)
@@ -12075,7 +12216,8 @@ validateForeignKeyConstraint(char *conname,
Relation rel,
Relation pkrel,
Oid pkindOid,
- Oid constraintOid)
+ Oid constraintOid,
+ bool hasperiod)
{
TupleTableSlot *slot;
TableScanDesc scan;
@@ -12103,9 +12245,11 @@ 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.
+ * 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;
/*
@@ -12256,6 +12400,7 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr
fk_trigger->whenClause = NULL;
fk_trigger->transitionRels = NIL;
fk_trigger->constrrel = NULL;
+
switch (fkconstraint->fk_del_action)
{
case FKCONSTR_ACTION_NOACTION:
@@ -12316,6 +12461,7 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr
fk_trigger->whenClause = NULL;
fk_trigger->transitionRels = NIL;
fk_trigger->constrrel = NULL;
+
switch (fkconstraint->fk_upd_action)
{
case FKCONSTR_ACTION_NOACTION:
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-22 14:30:21 +0000