MIME-Version: 1.0
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
 <CA+renyUMKg4-MyVbAi8Kj12Srd9BkU5Gt3FdbGdNr-ezHpL6cw@mail.gmail.com>
 <5650d77a-1d73-fd8d-4410-8e97dbeff572@illuminatedcomputing.com>
 <CA+renyWqpNdsi9OaPmXheeQL72adi_iz36VgVsqyyGpQ20FDAg@mail.gmail.com>
 <21964319-46e5-3e47-217b-6ac4169bdf13@enterprisedb.com> <CA+renyX0ipvY6A_jUOHeB1q9mL4bEYfAZ5FBB7G7jUo5bykjrA@mail.gmail.com>
 <f42e5be5-cd3e-bc4f-83e4-04a1e0270b17@enterprisedb.com> <27361388-f5ab-ea36-ea35-41d68a90e60d@illuminatedcomputing.com>
 <152e4c2c-36b4-bdc0-1b62-e9f9e8b68adc@enterprisedb.com> <251B44D1-D3E0-47E4-AB9D-4A848EDB495E@yesql.se>
 <1c674adf-4114-e8d8-cec7-ac10e2f424c7@enterprisedb.com> <CA+renyVyMOb+t6QNoq=PBK1CY_oa9wdJrObjj6S1-RK9CxYwKQ@mail.gmail.com>
 <831f17ba-ff36-1380-a475-a7cd2c65a89c@enterprisedb.com> <ab4cf49c-9839-3426-5114-f596fa9ee76b@illuminatedcomputing.com>
 <6f010a6e-8e20-658b-dc05-dc9033a694da@eisentraut.org> <7d44dade-ff31-5eef-80ac-78ed838066f6@postgresfriends.org>
 <88dadcc1-6652-ff7d-264d-73906a53dee9@illuminatedcomputing.com>
 <CACJufxHD2kv6hCy8yjVk486+d5rcuJS2XxkNH80g9Ui6Rz81Cg@mail.gmail.com>
 <CACJufxFTqiag-4fDPWJLxwwX+2gcZZWJPM9GyhPYD0tH=uM+jQ@mail.gmail.com> <CA+renyV4RxkWx57Vi5k_k0d7mkT7+F2kHYuKkwNPBaSOCaotFA@mail.gmail.com>
In-Reply-To: <CA+renyV4RxkWx57Vi5k_k0d7mkT7+F2kHYuKkwNPBaSOCaotFA@mail.gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Sat, 9 Sep 2023 15:54:37 +0800
Message-ID: <CACJufxG7DKLhyJgBaFttdXZe-4dOVu3NVED+4sdegdGs6R7JdQ@mail.gmail.com>
Subject: Re: SQL:2011 application time
To: Paul A Jungwirth <pj@illuminatedcomputing.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CACJufxG7DKLhyJgBaFttdXZe-4dOVu3NVED%2B4sdegdGs6R7JdQ%40mail.gmail.com>
Precedence: bulk

hi
I am confused by (pk,fk) on delete on update (restriction and no
action) result based on v13.
related post: https://stackoverflow.com/questions/14921668/difference-between-restrict-and-no-action
Please check the following test and comments.

---common setup for test0, test1,test2,test3
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
);
commit;

----------------no_action_vs_restriction test0
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;
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]');

/*
expect below to fail.
since to be deleted range is being referenced (in temporal_fk_rng2rng)
but the v13 patch won't fail.
*/
delete from temporal_rng
    FOR PORTION OF valid_at FROM '2018-01-06' TO '2018-01-11'
WHERE   id = '[5,5]'
AND     valid_at @> '2018-01-05'::timestamp;
TABLE temporal_rng \; table temporal_fk_rng2rng;
ROLLBACK;


----------------no_action_vs_restriction test1
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 RESTRICT
ON UPDATE RESTRICT;
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]');

/*
expect the below command not to fail.
since to be deleted range is not being referenced in temporal_fk_rng2rng)
but the v13 patch will fail.
*/
delete from temporal_rng
    FOR PORTION OF valid_at FROM '2018-01-12' TO '2018-01-20'
WHERE   id = '[5,5]'
AND     valid_at @> '2018-01-05'::timestamp;
ROLLBACK;


----------------no_action_vs_restriction test2
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;
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]');
/*
expect below command fail.
since to be deleted range is being referenced (in temporal_fk_rng2rng)
*/
UPDATE temporal_rng FOR PORTION OF valid_at FROM '2018-01-06' TO '2018-01-08'
SET     id = '[7,7]'
WHERE   id = '[5,5]'
AND     valid_at @> '2018-01-05'::timestamp;
TABLE temporal_rng \; table temporal_fk_rng2rng;

ROLLBACK;


----------------no_action_vs_restriction test3
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 RESTRICT
ON UPDATE RESTRICT;
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]');

/*
expect the below command not to fail.
since to be deleted range is not being referenced in temporal_fk_rng2rng)
but the v13 patch will fail.
*/
UPDATE temporal_rng FOR PORTION OF valid_at FROM '2018-01-12' TO '2018-01-20'
SET     id = '[7,7]'
WHERE   id = '[5,5]'
AND     valid_at @> '2018-01-05'::timestamp;
ROLLBACK;