Re: [PATCH] Fix temporal foreign key validation of pre-existing rows

On Thu, Jul 23, 2026 at 12:31 PM Jacob Brazeal <jacob(dot)brazeal(at)gmail(dot)com> wrote:
>
> Hi,
>
> I found that several ALTER TABLE paths can accept pre-existing rows that
> violate a temporal foreign key.
>
> For example:
>
> CREATE EXTENSION btree_gist;
>
> CREATE TABLE temporal_pk (
> id int,
> valid_at daterange,
> PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
> );
> INSERT INTO temporal_pk VALUES
> (1, '[2000-01-01,2010-01-01)'),
> (2, '[2000-01-01,2010-01-01)');
>
> CREATE TABLE temporal_fk (
> id int,
> valid_at daterange
> ) PARTITION BY LIST (id);
> CREATE TABLE temporal_fk_p1 PARTITION OF temporal_fk FOR VALUES IN (1);
>
> ALTER TABLE temporal_fk
> ADD CONSTRAINT temporal_fk_fk
> FOREIGN KEY (id, PERIOD valid_at) REFERENCES temporal_pk;
>
> The normal row-trigger path rejects a period that overlaps the referenced
> period but is not covered by it:
>
> INSERT INTO temporal_fk VALUES (1, '[2005-01-01,2020-01-01)');
> ERROR: insert or update on table "temporal_fk_p1" violates foreign key constraint "temporal_fk_fk"
> DETAIL: Key (id, valid_at)=(1, [2005-01-01,2020-01-01)) is not present in table "temporal_pk".
>
> The same violation is accepted if the row is already present in a table that
> is then attached:
>
> CREATE TABLE temporal_fk_p2 (
> id int,
> valid_at daterange
> );
> INSERT INTO temporal_fk_p2 VALUES (2, '[2005-01-01,2020-01-01)');
>
> ALTER TABLE temporal_fk ATTACH PARTITION temporal_fk_p2 FOR VALUES IN (2);
> ALTER TABLE
>
> The constraint is then recorded as validated, with the offending row in place:
>
> SELECT conname, conperiod, convalidated FROM pg_constraint
> WHERE conname = 'temporal_fk_fk' AND conrelid = 'temporal_fk_p2'::regclass;
> conname | conperiod | convalidated
> ----------------+-----------+--------------
> temporal_fk_fk | t | t
>
> The same problem affects:
>
> - ATTACH PARTITION;
> - VALIDATE CONSTRAINT;
> - changing a foreign key from NOT ENFORCED to ENFORCED.
>
> The ATTACH PARTITION and VALIDATE CONSTRAINT cases appear to date to commit
> 89f908a6d, which introduced temporal foreign keys; the ENFORCED case was added
> later with eec0040c4, when NOT ENFORCED foreign keys were introduced. The
> original temporal-FK tests exercised the normal row-trigger path, but not these
> ALTER TABLE phase-3 validation paths. The edge case is periods that
> overlap the referenced period but are not fully covered.
>
> In each case, the temporal flag is lost when the foreign key is queued for
> ALTER TABLE phase 3. The attached patch propagates that flag at all three sites
> and adds regression tests covering both violating and valid periods.

Thanks for the patch — I reviewed it and it looks correct to me. Nice catch.

I applied the patch on top of current master and confirmed the behavior.
The core case now errors as expected, symmetric with UPDATE, and leftovers
that still satisfy the view are still allowed through. I also
exercised a few cases
beyond the included tests, all of which behave correctly.

One minor comments, neither blocking:

The comment just above the changed condition still reads "For INSERT/UPDATE
(or MERGE containing INSERT/UPDATE), if the view has the WITH CHECK OPTION ...".
Since the block now also handles DELETE FOR PORTION OF, it'd be worth
updating that
comment so it doesn't mislead a future reader — e.g. mentioning that
DELETE FOR PORTION
OF enters here because its temporal leftovers are inserted.

>
> The issue is present in PostgreSQL 18, 19, and master.
>
> I did not find an earlier report, but apologies if I missed one.
>
> Regards,
> Jacob
>

--
Regards,
Ewan Young