Hi, While looking at FOR PORTION OF (see [1]), some AI tool noted that FPO can lead to issues with temporal foreign keys. I don't think the issues were really related to FPO, hence this new thread.
I don't think these are particularly hard to fix. 1) wrong lock level used with exclusion constraints See exclusion-lock.spec. The problem is that the constraint is an exclusion constraint, which relcache doesn't include in the set of key-columns (as it's not unique). Which in turn means that FOR KEY SHARE is used, which does not conflict with the UPDATE. 2) VALIDATE CONSTRAINT is broken See validation.sql QueueFKConstraintValidation() allocates a zeroed NewConstraint but omits setting conwithperiod from conperiod. That leads to taking the wrong path in validateForeignKeyConstraint(). FWIW, the issue it flagged with FPO was just that there can be temporary spurious errors due to fkeys in some edge cases. But those are afaict also present for non-FPO cases, and are arguably correct (the "problem" is what snapshot is used to look for required rows, after waiting for the row lock on a row deletion - a since then newly inserted row is not discovered). Greetings, Andres Freund [1] https://postgr.es/m/vquveff5flfpsgsd55dkjqplhphziah7a7kggnemzfv5krrhet%40jxp5ubrpmxhy
setup
{
CREATE TABLE parent (id int4range NOT NULL, valid_at int4range NOT NULL);
CREATE TABLE child (id int4range NOT NULL, valid_at int4range NOT NULL);
INSERT INTO parent VALUES ('[1,2)', '[0,10)');
}
teardown { DROP TABLE child, parent; }
session s1
step exclusion {
ALTER TABLE parent ADD CONSTRAINT parent_key
EXCLUDE USING gist (id WITH =, valid_at WITH &&);
ALTER TABLE child ADD CONSTRAINT child_fk
FOREIGN KEY (id, PERIOD valid_at) REFERENCES parent (id, PERIOD valid_at);
}
step temporal_pk {
ALTER TABLE parent ADD CONSTRAINT parent_key
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
ALTER TABLE child ADD CONSTRAINT child_fk
FOREIGN KEY (id, PERIOD valid_at) REFERENCES parent (id, PERIOD valid_at);
}
step s1begin { BEGIN ISOLATION LEVEL READ COMMITTED; }
step s1insert { INSERT INTO child VALUES ('[1,2)', '[0,10)'); }
step s1commit { COMMIT; }
step result {
SELECT * FROM parent;
SELECT c.*, coalesce(c.valid_at <@
(SELECT range_agg(p.valid_at) FROM parent p WHERE p.id = c.id), false)
AS covered FROM child c;
}
step validate {
ALTER TABLE child DROP CONSTRAINT child_fk;
ALTER TABLE child ADD CONSTRAINT child_fk
FOREIGN KEY (id, PERIOD valid_at) REFERENCES parent (id, PERIOD valid_at);
}
session s2
step s2begin { BEGIN ISOLATION LEVEL READ COMMITTED; }
step s2shrink { UPDATE parent SET valid_at = '[0,5)' WHERE id = '[1,2)'; }
step s2commit { COMMIT; }
# this permutation shows a wrong result, as noticeable by the result and
# validate step outputs
permutation exclusion s1begin s2begin s1insert s2shrink s1commit s2commit
result validate
# when a PK is also present everything works.
permutation temporal_pk s1begin s2begin s1insert s2shrink s1commit s2commit
result validate
validation.sql
Description: application/sql
