WITHOUT OVERLAPS foreign key allows referencing EXCLUDE constraint
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t254014psql -h localhost -U postgresBuilt from patchset v1 (message #1), October 22, 2026 at 04:46 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t254014_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t254014_1 && git checkout t254014_1Patchset v1 (message #1) is on t254014_1
Hi Hackers,
Andres reported that temporal foreign keys allow serialization
anomalies because their FOR KEY SHARE doesn't conflict with the
referenced key.[0]/messages/by-id/sesqxzfcujkcnrwpir5xvhy7iamhsbjiqt6r2xqmb7hqkulte6@bbuc42qzanzs 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]: /messages/by-id/sesqxzfcujkcnrwpir5xvhy7iamhsbjiqt6r2xqmb7hqkulte6@bbuc42qzanzs
Yours,
--
Paul ~{:-)
pj@illuminatedcomputing.com