Re: SQL:2011 application time

On 11/4/24 13:16, Sam Gabrielsson wrote:
> Foreign key violation errors are incorrectly raised in a few cases for a temporal foreign key with 
> default ON UPDATE NO ACTION. Test is based on the commited v39 patches (used a snapshot version of 
> PG18 devel available from PGDG).

Thank you for the report! I confirmed that this is a problem. In ri_restrict we fail if any fk 
records still match the being-changed pk, but for temporal if you're merely shrinking the pk range, 
fk references could still wind up being valid (if you're only shrinking it a little). So we need to 
do more work.

> In the temporal NO ACTION case something similar to this (though with appropriate locks) could 
> perhaps be tested in ri_restrict (when ri_Check_Pk_Match returns false):
> 
>    SELECT 1
>    FROM (SELECT range_agg(pkperiodatt) AS r
>        FROM <pktable>
>        WHERE pkatt1 = $1 [AND ...]
>        AND pkperiodatt && $n) AS pktable,
>      (SELECT fkperiodatt AS r
>        FROM <fktable>
>        WHERE fkatt1 = $1 [AND ...]
>        AND fkperiodatt && $n) AS fktable
>    WHERE NOT fktable.r <@ pktable.r

This solution looks like it will work to me. Basically: find FKs that still match the PK, but only 
fail if they are no longer covered.

IIRC for RESTRICT it is *correct* to reject the change, so we would want to keep the old SQL there, 
and only update it for NOACTION.

I'll work on a fix and submit another set of patches.

Yours,

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com