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

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.

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