Re: SQL:2011 application time

On Fri, Sep 15, 2023 at 12:11 AM Paul Jungwirth
<pj@illuminatedcomputing.com> wrote:
>
> Thanks for the thorough review and testing!
>
> Here is a v14 patch with the segfault and incorrect handling of NO
> ACTION and RESTRICT fixed (and reproductions added to the test suite).
>

another case:
BEGIN;
DROP TABLE IF EXISTS temporal_rng, temporal_fk_rng2rng;
CREATE TABLE temporal_rng ( id int4range,valid_at tsrange);
ALTER TABLE temporal_rng
ADD CONSTRAINT temporal_rng_pk
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
CREATE TABLE temporal_fk_rng2rng (
id int4range,
valid_at tsrange,
parent_id int4range
);
INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01')),
                                 ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
INSERT INTO temporal_fk_rng2rng
VALUES ('[3,3]', tsrange('2018-01-05','2018-01-10'), '[5,5]');
commit;


BEGIN;
ALTER TABLE temporal_fk_rng2rng
DROP CONSTRAINT IF EXISTS temporal_fk_rng2rng_fk;
ALTER TABLE temporal_fk_rng2rng
ADD CONSTRAINT temporal_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_rng
    ON DELETE NO ACTION
ON UPDATE NO ACTION;
ALTER TABLE temporal_fk_rng2rng ALTER CONSTRAINT
temporal_fk_rng2rng_fk  DEFERRABLE INITIALLY DEFERRED;

delete from temporal_rng; ---should not fail.
commit; ---fail in here.

-------------------------------
seems in ATExecAlterConstrRecurse change to

/*
* Update deferrability of RI_FKey_noaction_del,
* RI_FKey_noaction_upd, RI_FKey_check_ins and RI_FKey_check_upd
* triggers, but not others; see createForeignKeyActionTriggers
* and CreateFKCheckTrigger.
*/
if (tgform->tgfoid != F_RI_FKEY_NOACTION_DEL &&
tgform->tgfoid != F_TRI_FKEY_NOACTION_DEL &&
tgform->tgfoid != F_RI_FKEY_NOACTION_UPD &&
tgform->tgfoid != F_TRI_FKEY_NOACTION_UPD &&
tgform->tgfoid != F_RI_FKEY_CHECK_INS &&
tgform->tgfoid != F_TRI_FKEY_CHECK_INS &&
tgform->tgfoid != F_RI_FKEY_CHECK_UPD &&
tgform->tgfoid != F_TRI_FKEY_CHECK_UPD)
continue;

will work.