Hi Hackers,
Andres reported that temporal foreign keys allow serialization
anomalies because their FOR KEY SHARE doesn't conflict with the
referenced key.[0] Actually if the reference is a WITHOUT OVERLAPS
primary key or uniqueness constraint, then the lock does conflict, and
there is no serialization anomaly. But we are erroneously allowing a
temporal foreign key to reference a plain EXCLUDE constraint, and in
that case there is no lock conflict.
The real bug here is permitting that reference in the first place. An
EXCLUDE constraint doesn't guarantee uniqueness (because of empty
ranges but also it could have any operator the user chooses). The
cause was checking indisexclusion but not indisunique. A WITHOUT
OVERLAPS constraint's index will have both. So this patch updates the
rules to forbid referencing an EXCLUDE constraint.
That fix is easy, but dealing with existing bad FKs from v18 is
trickier. They will cause failures when using pg_restore or
pg_upgrade. I think it is unlikely that anyone actually created
temporal foreign keys this way, but I included a new check in
pg_upgrade to detect them, just in case. I also log a warning when we
find such a foreign key. The release notes could include this SQL to
detect them, with a suggestion to replace them with references to real
WITHOUT OVERLAPS constraints:
SELECT c.conrelid::regclass AS table_name, c.conname AS constraint_name
FROM pg_constraint c JOIN pg_index i ON i.indexrelid = c.conindid
WHERE c.contype = 'f' AND c.conperiod AND NOT i.indisunique;
The warning only needs to be in v18, so I've broken it out into a
separate commit.
The first patch here applies cleanly against master, but has a
(routine) merge conflict in pg_upgrade/check.c to against
REL_{18,19}_STABLE. For 18 that hunk can actually be dropped, since
there is no need to check for this when upgrading from 17 to 18.
Getting this fix into 19 before release would reduce the chance
someone creates this bug, and it means we only have to carry the code
to print warnings in the 18 branch. Should I add it to the 19 Open
Items list?
[0]
https://www.postgresql.org/message-id/sesqxzfcujkcnrwpir5xvhy7iamhsbjiqt6r2xqmb7hqkulte6%40bbuc42qzanzs
Yours,
--
Paul ~{:-)
[email protected]
From 73211a6ddb8ef9b52f846c80ac2eda8bc670d604 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <[email protected]>
Date: Wed, 30 Sep 2026 12:02:19 -0700
Subject: [PATCH v1 1/2] Fix temporal FKs referencing invalid constraints
A foreign key with a PERIOD part could reference an exclusion constraint (as
long as you said PERIOD in the REFERENCES part of the constraint command). But
that is not good enough: it needs to be a WITHOUT OVERLAPS primary key or unique
constraint. For one thing, an exclusion constraint doesn't guarantee uniqueness:
it doesn't prevent empty ranges, and we don't even check what operators the user
chose. For another, UPDATing it doesn't conflict with FOR KEY SHARE, so the
foreign key would permit serialization anomalies.
This commit forbids that combination. We also detect attempts to reference
actual primary keys that lack WITHOUT OVERLAPS. This failed before, but with an
unhelpful error message.
Existing databases from 18.x may already have such foreign keys. Restoring
them would fail, so we added a check to pg_upgrade so that users can replace
them with references to WITHOUT OVERLAPS constraints before upgrading.
For the release notes: users should look for affected foreign keys with this
query, and drop and recreate them referencing a primary key or unique
constraint using WITHOUT OVERLAPS:
SELECT c.conrelid::regclass AS table_name, c.conname AS constraint_name
FROM pg_constraint c JOIN pg_index i ON i.indexrelid = c.conindid
WHERE c.contype = 'f' AND c.conperiod AND NOT i.indisunique;
Reported-By: Andres Freund <[email protected]>
Author: Paul A. Jungwirth <[email protected]>
Discussion: https://www.postgresql.org/message-id/sesqxzfcujkcnrwpir5xvhy7iamhsbjiqt6r2xqmb7hqkulte6%40bbuc42qzanzs
Backpatch-through: 18
---
src/backend/commands/tablecmds.c | 21 +++--
src/bin/pg_upgrade/check.c | 88 +++++++++++++++++++
.../regress/expected/without_overlaps.out | 46 ++++++++++
src/test/regress/sql/without_overlaps.sql | 44 ++++++++++
4 files changed, 193 insertions(+), 6 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 0274d892f2e..859d2b78da7 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -10305,6 +10305,15 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
with_period, opclasses, &pk_has_without_overlaps);
}
+ /*
+ * If the referencing foreign key uses PERIOD, the primary key must use
+ * WITHOUT OVERLAPS
+ */
+ if (!pk_has_without_overlaps && with_period)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_FOREIGN_KEY),
+ errmsg("foreign key using PERIOD must reference a primary key or unique constraint using WITHOUT OVERLAPS"));
+
/*
* If the referenced primary key has WITHOUT OVERLAPS, the foreign key
* must use PERIOD.
@@ -14009,12 +14018,11 @@ transformFkeyCheckAttrs(Relation pkrel,
indexStruct = (Form_pg_index) GETSTRUCT(indexTuple);
/*
- * 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.
+ * 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.
*/
- if (indexStruct->indnkeyatts == numattrs &&
- (with_period ? indexStruct->indisexclusion : indexStruct->indisunique) &&
+ if (indexStruct->indnkeyatts == numattrs && indexStruct->indisunique &&
indexStruct->indisvalid &&
heap_attisnull(indexTuple, Anum_pg_index_indpred, NULL) &&
heap_attisnull(indexTuple, Anum_pg_index_indexprs, NULL))
@@ -14077,7 +14085,8 @@ transformFkeyCheckAttrs(Relation pkrel,
/* We need to know whether the index has WITHOUT OVERLAPS */
if (found)
- *pk_has_without_overlaps = indexStruct->indisexclusion;
+ *pk_has_without_overlaps = indexStruct->indisunique &&
+ indexStruct->indisexclusion;
}
ReleaseSysCache(indexTuple);
if (found)
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index 150c4fc34b9..ffed2221dc6 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -30,6 +30,7 @@ static void check_for_incompatible_polymorphics(ClusterInfo *cluster);
static void check_for_tables_with_oids(ClusterInfo *cluster);
static void check_for_not_null_inheritance(ClusterInfo *cluster);
static void check_for_gist_inet_ops(ClusterInfo *cluster);
+static void check_for_temporal_fks_to_exclusion_constraints(ClusterInfo *cluster);
static void check_for_new_tablespace_dir(void);
static void check_for_user_defined_encoding_conversions(ClusterInfo *cluster);
static void check_for_unicode_update(ClusterInfo *cluster);
@@ -688,6 +689,15 @@ check_and_dump_old_cluster(void)
if (GET_MAJOR_VERSION(old_cluster.major_version) <= 1800)
check_for_gist_inet_ops(&old_cluster);
+ /*
+ * PG 18 allowed a foreign key with PERIOD to reference a plain exclusion
+ * constraint, but that is no longer allowed, so restoring such a foreign
+ * key would fail. Minor releases fixed this, but a cluster could still
+ * have foreign keys created before the fix.
+ */
+ if (GET_MAJOR_VERSION(old_cluster.major_version) >= 1800)
+ check_for_temporal_fks_to_exclusion_constraints(&old_cluster);
+
/*
* While not a check option, we do this now because this is the only time
* the old server is running.
@@ -1812,6 +1822,84 @@ check_for_gist_inet_ops(ClusterInfo *cluster)
check_ok();
}
+/*
+ * Callback function for processing results of query for
+ * check_for_temporal_fks_to_exclusion_constraints()'s UpgradeTask. If the
+ * query returned any rows (i.e., the check failed), write the details to the
+ * report file.
+ */
+static void
+process_temporal_fks_to_exclusion_constraints(DbInfo *dbinfo, PGresult *res,
+ void *arg)
+{
+ UpgradeTaskReport *report = (UpgradeTaskReport *) arg;
+ int ntups = PQntuples(res);
+ int i_nspname = PQfnumber(res, "nspname");
+ int i_relname = PQfnumber(res, "relname");
+ int i_conname = PQfnumber(res, "conname");
+
+ if (ntups == 0)
+ return;
+
+ if (report->file == NULL &&
+ (report->file = fopen_priv(report->path, "w")) == NULL)
+ pg_fatal("could not open file \"%s\": %m", report->path);
+
+ fprintf(report->file, "In database: %s\n", dbinfo->db_name);
+
+ for (int rowno = 0; rowno < ntups; rowno++)
+ fprintf(report->file, " %s.%s.%s\n",
+ PQgetvalue(res, rowno, i_nspname),
+ PQgetvalue(res, rowno, i_relname),
+ PQgetvalue(res, rowno, i_conname));
+}
+
+/*
+ * Verify that no foreign keys with PERIOD reference a plain exclusion
+ * constraint instead of a primary key or unique constraint using WITHOUT
+ * OVERLAPS. Such foreign keys can no longer be created, so they would fail
+ * to restore.
+ */
+static void
+check_for_temporal_fks_to_exclusion_constraints(ClusterInfo *cluster)
+{
+ UpgradeTaskReport report;
+ UpgradeTask *task = upgrade_task_create();
+ const char *query = "SELECT n.nspname, c.relname, con.conname "
+ "FROM pg_catalog.pg_constraint con, pg_catalog.pg_index i, "
+ " pg_catalog.pg_class c, pg_catalog.pg_namespace n "
+ "WHERE con.contype = 'f' AND con.conperiod"
+ " AND con.conindid = i.indexrelid AND NOT i.indisunique"
+ " AND con.conrelid = c.oid AND c.relnamespace = n.oid";
+
+ prep_status("Checking for foreign keys referencing exclusion constraints");
+
+ report.file = NULL;
+ snprintf(report.path, sizeof(report.path), "%s/%s",
+ log_opts.basedir,
+ "temporal_fks_to_exclusion_constraints.txt");
+
+ upgrade_task_add_step(task, query,
+ process_temporal_fks_to_exclusion_constraints,
+ true, &report);
+ upgrade_task_run(task, cluster);
+ upgrade_task_free(task);
+
+ if (report.file)
+ {
+ fclose(report.file);
+ pg_log(PG_REPORT, "fatal");
+ pg_fatal("Your installation contains foreign keys using PERIOD that reference an\n"
+ "exclusion constraint instead of a primary key or unique constraint using\n"
+ "WITHOUT OVERLAPS. These are no longer allowed. Drop them, and recreate\n"
+ "them referencing a primary key or unique constraint using WITHOUT OVERLAPS.\n"
+ "A list of foreign keys with the problem is in the file:\n"
+ " %s", report.path);
+ }
+ else
+ check_ok();
+}
+
/*
* Callback function for processing results of query for
* check_for_user_defined_encoding_conversions()'s UpgradeTask. If the query
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index 8e3f6eee0b0..efdf79c17dd 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -1573,6 +1573,52 @@ 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;
+-- Referencing a non-temporal primary key should fail:
+CREATE TABLE temporal_rng3 (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_rng3_pk PRIMARY KEY (id, valid_at)
+);
+CREATE TABLE temporal_fk_rng2rng (
+ id int4range,
+ valid_at daterange,
+ parent_id int4range,
+ CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_rng3 (id, PERIOD valid_at)
+);
+ERROR: foreign key using PERIOD must reference a primary key or unique constraint using WITHOUT OVERLAPS
+CREATE TABLE temporal_fk_rng2rng (
+ id int4range,
+ valid_at daterange,
+ parent_id int4range,
+ CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_rng3
+);
+ERROR: foreign key using PERIOD must reference a primary key or unique constraint using WITHOUT OVERLAPS
+DROP TABLE temporal_rng3;
+-- Referencing an exclusion constraint should fail:
+CREATE TABLE temporal_rng3 (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_rng3_excl EXCLUDE USING gist (id WITH =, valid_at WITH &&)
+);
+CREATE TABLE temporal_fk_rng2rng (
+ id int4range,
+ valid_at daterange,
+ parent_id int4range,
+ CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_rng3 (id, PERIOD valid_at)
+);
+ERROR: there is no unique constraint matching given keys for referenced table "temporal_rng3"
+CREATE TABLE temporal_fk_rng2rng (
+ id int4range,
+ valid_at daterange,
+ parent_id int4range,
+ CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_rng3
+);
+ERROR: there is no primary key for referenced table "temporal_rng3"
+DROP TABLE temporal_rng3;
--
-- test ALTER TABLE ADD CONSTRAINT
--
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index 46bdd7856a4..786f3066a84 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -1119,6 +1119,50 @@ CREATE TABLE temporal_fk2_rng2rng (
\d temporal_fk2_rng2rng
DROP TABLE temporal_fk2_rng2rng;
+-- Referencing a non-temporal primary key should fail:
+CREATE TABLE temporal_rng3 (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_rng3_pk PRIMARY KEY (id, valid_at)
+);
+CREATE TABLE temporal_fk_rng2rng (
+ id int4range,
+ valid_at daterange,
+ parent_id int4range,
+ CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_rng3 (id, PERIOD valid_at)
+);
+CREATE TABLE temporal_fk_rng2rng (
+ id int4range,
+ valid_at daterange,
+ parent_id int4range,
+ CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_rng3
+);
+DROP TABLE temporal_rng3;
+
+-- Referencing an exclusion constraint should fail:
+CREATE TABLE temporal_rng3 (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_rng3_excl EXCLUDE USING gist (id WITH =, valid_at WITH &&)
+);
+CREATE TABLE temporal_fk_rng2rng (
+ id int4range,
+ valid_at daterange,
+ parent_id int4range,
+ CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_rng3 (id, PERIOD valid_at)
+);
+CREATE TABLE temporal_fk_rng2rng (
+ id int4range,
+ valid_at daterange,
+ parent_id int4range,
+ CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_rng3
+);
+DROP TABLE temporal_rng3;
+
--
-- test ALTER TABLE ADD CONSTRAINT
--
--
2.47.3
From bd21a61b6ee730ee357d9e7292c07b62ea9ab1e0 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <[email protected]>
Date: Wed, 30 Sep 2026 12:02:51 -0700
Subject: [PATCH v1 2/2] Warn about temporal FKs that reference exclusion
constraints
This is for REL_18_STABLE only.
The previous commit stopped foreign keys with PERIOD from referencing a plain
exclusion constraint, but earlier 18.x releases allowed it, so databases may
already have such foreign keys. They can't reliably enforce referential
integrity, and restoring from an old pg_dump will fail. So this commit warns
users when we notice them. We warn when building an RI plan for such a foreign
key and also when cloning one for a new partition.
Later branches don't need this: there these foreign keys could only come from
18.x, and restoring them (with pg_dump or pg_upgrade) fails with the new
error.
Author: Paul A. Jungwirth <[email protected]>
Discussion: https://www.postgresql.org/message-id/sesqxzfcujkcnrwpir5xvhy7iamhsbjiqt6r2xqmb7hqkulte6%40bbuc42qzanzs
---
src/backend/commands/tablecmds.c | 40 +++++++++++
src/backend/utils/adt/ri_triggers.c | 38 ++++++++++
.../regress/expected/without_overlaps.out | 72 +++++++++++++++++++
src/test/regress/sql/without_overlaps.sql | 61 ++++++++++++++++
4 files changed, 211 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 859d2b78da7..a8c26de7e5a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -613,6 +613,8 @@ static void CloneForeignKeyConstraints(List **wqueue, Relation parentRel,
static void CloneFkReferenced(Relation parentRel, Relation partitionRel);
static void CloneFkReferencing(List **wqueue, Relation parentRel,
Relation partRel);
+static void warnIfPeriodFkIndexNotUnique(bool with_period, Oid indexOid,
+ const char *conname, Oid relid);
static void createForeignKeyCheckTriggers(Oid myRelOid, Oid refRelOid,
Constraint *fkconstraint, Oid constraintOid,
Oid indexOid,
@@ -11501,6 +11503,9 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel)
conkey, conpfeqop, conppeqop, conffeqop,
numfkdelsetcols, confdelsetcols, false,
constrForm->conperiod);
+ warnIfPeriodFkIndexNotUnique(constrForm->conperiod, partIndexId,
+ fkconstraint->conname,
+ constrForm->conrelid);
/* ... and recurse */
addFkRecurseReferenced(fkconstraint,
fkRel,
@@ -11746,6 +11751,9 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
conppeqop, conffeqop,
numfkdelsetcols, confdelsetcols,
false, with_period);
+ warnIfPeriodFkIndexNotUnique(with_period, indexOid,
+ get_constraint_name(address.objectId),
+ RelationGetRelid(partRel));
/* Done with the cloned constraint's tuple */
ReleaseSysCache(tuple);
@@ -11776,6 +11784,38 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
table_close(trigrel, RowExclusiveLock);
}
+/*
+ * warnIfPeriodFkIndexNotUnique
+ *
+ * Earlier 18.x releases let a PERIOD foreign key reference a plain exclusion
+ * constraint. ATAddForeignKeyConstraint rejects that now, but cloning such a
+ * foreign key for a new partition copies its index without that check, so
+ * warn when it happens.
+ */
+static void
+warnIfPeriodFkIndexNotUnique(bool with_period, Oid indexOid,
+ const char *conname, Oid relid)
+{
+ HeapTuple indtup;
+ bool isunique;
+
+ if (!with_period)
+ return;
+
+ indtup = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexOid));
+ if (!HeapTupleIsValid(indtup))
+ elog(ERROR, "cache lookup failed for index %u", indexOid);
+ isunique = ((Form_pg_index) GETSTRUCT(indtup))->indisunique;
+ ReleaseSysCache(indtup);
+
+ if (!isunique)
+ ereport(WARNING,
+ errmsg("foreign key constraint \"%s\" on table \"%s\" references an exclusion constraint instead of a primary key or unique constraint using WITHOUT OVERLAPS",
+ conname, get_rel_name(relid)),
+ errdetail("Such a foreign key cannot reliably enforce referential integrity."),
+ errhint("Drop the foreign key and recreate it referencing a primary key or unique constraint using WITHOUT OVERLAPS."));
+}
+
/*
* When the parent of a partition receives [the referencing side of] a foreign
* key, we must propagate that foreign key to the partition. However, the
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 40c1591ac7b..878d185bfc2 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -138,6 +138,7 @@ typedef struct RI_ConstraintInfo
* delete */
char confmatchtype; /* foreign key's match type */
bool hasperiod; /* if the foreign key uses PERIOD */
+ bool pk_not_unique; /* PERIOD FK references a non-unique EXCLUDE */
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 */
@@ -364,6 +365,7 @@ static RI_ConstraintInfo *ri_FetchConstraintInfo(Trigger *trigger,
Relation trig_rel, bool rel_is_pk);
static RI_ConstraintInfo *ri_LoadConstraintInfo(Oid constraintOid);
static Oid get_ri_constraint_root(Oid constrOid);
+static void ri_WarnIfNotUnique(const RI_ConstraintInfo *riinfo);
static SPIPlanPtr ri_PlanCheck(const char *querystr, int nargs, const Oid *argtypes,
RI_QueryKey *qkey, Relation fk_rel, Relation pk_rel);
static bool ri_PerformCheck(const RI_ConstraintInfo *riinfo,
@@ -648,6 +650,8 @@ RI_FKey_check(TriggerData *trigdata)
appendStringInfoString(&querybuf, "(x1.r)");
}
+ ri_WarnIfNotUnique(riinfo);
+
/* Prepare and save the plan */
qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
&qkey, fk_rel, pk_rel);
@@ -817,6 +821,8 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
appendStringInfoString(&querybuf, "(x1.r)");
}
+ ri_WarnIfNotUnique(riinfo);
+
/* Prepare and save the plan */
qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
&qkey, fk_rel, pk_rel);
@@ -1093,6 +1099,8 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
appendStringInfoString(&querybuf, " FOR KEY SHARE OF x");
+ ri_WarnIfNotUnique(riinfo);
+
/* Prepare and save the plan */
qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
&qkey, fk_rel, pk_rel);
@@ -2039,6 +2047,7 @@ RI_PartitionRemove_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
int i;
riinfo = ri_FetchConstraintInfo(trigger, fk_rel, false);
+ ri_WarnIfNotUnique(riinfo);
/*
* We don't check permissions before displaying the error message, on the
@@ -2546,14 +2555,22 @@ ri_LoadConstraintInfo(Oid constraintOid)
* opclass of the PK element for these. This all gets cached (as does the
* generated plan), so there's no performance issue.
*/
+ riinfo->pk_not_unique = false;
if (riinfo->hasperiod)
{
Oid opclass = get_index_column_opclass(conForm->conindid, riinfo->nkeys);
+ HeapTuple indtup;
FindFKPeriodOpers(opclass,
&riinfo->period_contained_by_oper,
&riinfo->agged_period_contained_by_oper,
&riinfo->period_intersect_oper);
+
+ indtup = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(conForm->conindid));
+ if (!HeapTupleIsValid(indtup))
+ elog(ERROR, "cache lookup failed for index %u", conForm->conindid);
+ riinfo->pk_not_unique = !((Form_pg_index) GETSTRUCT(indtup))->indisunique;
+ ReleaseSysCache(indtup);
}
/* Metadata used by fast path. */
@@ -2688,6 +2705,27 @@ InvalidateConstraintCacheCallBack(Datum arg, SysCacheIdentifier cacheid,
}
+/*
+ * ri_WarnIfNotUnique -
+ *
+ * Earlier 18.x releases let a foreign key with PERIOD reference a plain
+ * exclusion constraint, which doesn't guarantee uniqueness, so such a foreign
+ * key can't reliably enforce referential integrity. We no longer allow
+ * creating one, but some may already exist, so warn when we find one.
+ */
+static void
+ri_WarnIfNotUnique(const RI_ConstraintInfo *riinfo)
+{
+ if (!riinfo->pk_not_unique)
+ return;
+
+ ereport(WARNING,
+ errmsg("foreign key constraint \"%s\" on table \"%s\" references an exclusion constraint instead of a primary key or unique constraint using WITHOUT OVERLAPS",
+ NameStr(riinfo->conname), get_rel_name(riinfo->fk_relid)),
+ errdetail("Such a foreign key cannot reliably enforce referential integrity."),
+ errhint("Drop the foreign key and recreate it referencing a primary key or unique constraint using WITHOUT OVERLAPS."));
+}
+
/*
* Prepare execution plan for a query to enforce an RI restriction
*/
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index efdf79c17dd..46e0ff2edcf 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -1619,6 +1619,78 @@ CREATE TABLE temporal_fk_rng2rng (
);
ERROR: there is no primary key for referenced table "temporal_rng3"
DROP TABLE temporal_rng3;
+-- Older releases allowed referencing an exclusion constraint. We can't
+-- create such a foreign key any more, so simulate one by pointing a valid
+-- foreign key at an exclusion constraint's index. Checking it should warn,
+-- but only when we build the plan, not for every row.
+CREATE TABLE temporal_rng3 (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_rng3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+ CONSTRAINT temporal_rng3_excl EXCLUDE USING gist (id WITH =, valid_at WITH &&)
+);
+CREATE TABLE temporal_fk_rng2rng (
+ id int4range,
+ valid_at daterange,
+ parent_id int4range,
+ CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_rng3 (id, PERIOD valid_at)
+);
+UPDATE pg_constraint SET conindid = 'temporal_rng3_excl'::regclass
+ WHERE conname = 'temporal_fk_rng2rng_fk';
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+ ('[1,2)', daterange('2018-01-01', '2019-01-01'), '[1,2)'),
+ ('[2,3)', daterange('2019-01-01', '2020-01-01'), '[1,2)');
+WARNING: foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" references an exclusion constraint instead of a primary key or unique constraint using WITHOUT OVERLAPS
+DETAIL: Such a foreign key cannot reliably enforce referential integrity.
+HINT: Drop the foreign key and recreate it referencing a primary key or unique constraint using WITHOUT OVERLAPS.
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+ ('[3,4)', daterange('2018-01-01', '2019-01-01'), '[1,2)');
+DROP TABLE temporal_fk_rng2rng;
+-- Cloning such a foreign key to a new partition should warn too.
+CREATE TABLE temporal_partitioned_fk_rng2rng (
+ id int4range,
+ valid_at daterange,
+ parent_id int4range,
+ CONSTRAINT temporal_partitioned_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_rng3 (id, PERIOD valid_at)
+) PARTITION BY LIST (id);
+UPDATE pg_constraint SET conindid = 'temporal_rng3_excl'::regclass
+ WHERE conname = 'temporal_partitioned_fk_rng2rng_fk';
+CREATE TABLE tfkp1 PARTITION OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[1,2)');
+WARNING: foreign key constraint "temporal_partitioned_fk_rng2rng_fk" on table "tfkp1" references an exclusion constraint instead of a primary key or unique constraint using WITHOUT OVERLAPS
+DETAIL: Such a foreign key cannot reliably enforce referential integrity.
+HINT: Drop the foreign key and recreate it referencing a primary key or unique constraint using WITHOUT OVERLAPS.
+CREATE TABLE tfkp2 (LIKE temporal_partitioned_fk_rng2rng);
+ALTER TABLE temporal_partitioned_fk_rng2rng ATTACH PARTITION tfkp2 FOR VALUES IN ('[2,3)');
+WARNING: foreign key constraint "temporal_partitioned_fk_rng2rng_fk" on table "tfkp2" references an exclusion constraint instead of a primary key or unique constraint using WITHOUT OVERLAPS
+DETAIL: Such a foreign key cannot reliably enforce referential integrity.
+HINT: Drop the foreign key and recreate it referencing a primary key or unique constraint using WITHOUT OVERLAPS.
+DROP TABLE temporal_partitioned_fk_rng2rng;
+DROP TABLE temporal_rng3;
+-- Likewise when the referenced table is partitioned.
+CREATE TABLE temporal_partitioned_rng3 (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_partitioned_rng3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+ CONSTRAINT temporal_partitioned_rng3_excl EXCLUDE USING gist (id WITH =, valid_at WITH &&)
+) PARTITION BY LIST (id);
+CREATE TABLE temporal_fk_rng2rng (
+ id int4range,
+ valid_at daterange,
+ parent_id int4range,
+ CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_partitioned_rng3 (id, PERIOD valid_at)
+);
+UPDATE pg_constraint SET conindid = 'temporal_partitioned_rng3_excl'::regclass
+ WHERE conname = 'temporal_fk_rng2rng_fk';
+CREATE TABLE tp1 PARTITION OF temporal_partitioned_rng3 FOR VALUES IN ('[1,2)');
+WARNING: foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" references an exclusion constraint instead of a primary key or unique constraint using WITHOUT OVERLAPS
+DETAIL: Such a foreign key cannot reliably enforce referential integrity.
+HINT: Drop the foreign key and recreate it referencing a primary key or unique constraint using WITHOUT OVERLAPS.
+DROP TABLE temporal_fk_rng2rng;
+DROP TABLE temporal_partitioned_rng3;
--
-- test ALTER TABLE ADD CONSTRAINT
--
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index 786f3066a84..2c2dccf4626 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -1163,6 +1163,67 @@ CREATE TABLE temporal_fk_rng2rng (
);
DROP TABLE temporal_rng3;
+-- Older releases allowed referencing an exclusion constraint. We can't
+-- create such a foreign key any more, so simulate one by pointing a valid
+-- foreign key at an exclusion constraint's index. Checking it should warn,
+-- but only when we build the plan, not for every row.
+CREATE TABLE temporal_rng3 (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_rng3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+ CONSTRAINT temporal_rng3_excl EXCLUDE USING gist (id WITH =, valid_at WITH &&)
+);
+CREATE TABLE temporal_fk_rng2rng (
+ id int4range,
+ valid_at daterange,
+ parent_id int4range,
+ CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_rng3 (id, PERIOD valid_at)
+);
+UPDATE pg_constraint SET conindid = 'temporal_rng3_excl'::regclass
+ WHERE conname = 'temporal_fk_rng2rng_fk';
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+ ('[1,2)', daterange('2018-01-01', '2019-01-01'), '[1,2)'),
+ ('[2,3)', daterange('2019-01-01', '2020-01-01'), '[1,2)');
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+ ('[3,4)', daterange('2018-01-01', '2019-01-01'), '[1,2)');
+DROP TABLE temporal_fk_rng2rng;
+-- Cloning such a foreign key to a new partition should warn too.
+CREATE TABLE temporal_partitioned_fk_rng2rng (
+ id int4range,
+ valid_at daterange,
+ parent_id int4range,
+ CONSTRAINT temporal_partitioned_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_rng3 (id, PERIOD valid_at)
+) PARTITION BY LIST (id);
+UPDATE pg_constraint SET conindid = 'temporal_rng3_excl'::regclass
+ WHERE conname = 'temporal_partitioned_fk_rng2rng_fk';
+CREATE TABLE tfkp1 PARTITION OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[1,2)');
+CREATE TABLE tfkp2 (LIKE temporal_partitioned_fk_rng2rng);
+ALTER TABLE temporal_partitioned_fk_rng2rng ATTACH PARTITION tfkp2 FOR VALUES IN ('[2,3)');
+DROP TABLE temporal_partitioned_fk_rng2rng;
+DROP TABLE temporal_rng3;
+-- Likewise when the referenced table is partitioned.
+CREATE TABLE temporal_partitioned_rng3 (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_partitioned_rng3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+ CONSTRAINT temporal_partitioned_rng3_excl EXCLUDE USING gist (id WITH =, valid_at WITH &&)
+) PARTITION BY LIST (id);
+CREATE TABLE temporal_fk_rng2rng (
+ id int4range,
+ valid_at daterange,
+ parent_id int4range,
+ CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_partitioned_rng3 (id, PERIOD valid_at)
+);
+UPDATE pg_constraint SET conindid = 'temporal_partitioned_rng3_excl'::regclass
+ WHERE conname = 'temporal_fk_rng2rng_fk';
+CREATE TABLE tp1 PARTITION OF temporal_partitioned_rng3 FOR VALUES IN ('[1,2)');
+DROP TABLE temporal_fk_rng2rng;
+DROP TABLE temporal_partitioned_rng3;
+
--
-- test ALTER TABLE ADD CONSTRAINT
--
--
2.47.3
