Re: SQL:2011 application time

jian he <jian.universality@gmail.com>

From: jian he <jian.universality@gmail.com>

To: Paul A Jungwirth <pj@illuminatedcomputing.com>

Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>

Date: 2023-09-09T07:54:37Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Rename gist stratnum support function
- 32edf732e8dc 18.0 landed
Remove support for temporal RESTRICT foreign keys
- b83e8a2ca2eb 18.0 landed
Cache NO ACTION foreign keys separately from RESTRICT foreign keys
- 9926f854d077 18.0 landed
Fix NO ACTION temporal foreign keys when the referenced endpoints change
- 1772d554b089 18.0 landed
Improve whitespace in without_overlaps test
- 888d4523f0c2 18.0 landed
Tests for logical replication with temporal keys
- 939b0908c87a 18.0 landed
Support for GiST in get_equal_strategy_number()
- 74edabce7a33 18.0 landed
Make the conditions in IsIndexUsableForReplicaIdentityFull() more explicit
- 13544e790ef8 18.0 landed
Replace get_equal_strategy_number_for_am() by get_equal_strategy_number()
- a2a475b011cf 18.0 landed
Improve internal logical replication error for missing equality strategy
- 321c287351f7 18.0 landed
Simplify IsIndexUsableForReplicaIdentityFull()
- 7727049e8f66 18.0 landed
Fix ALTER TABLE / REPLICA IDENTITY for temporal tables
- 79b575d3bc09 18.0 landed
doc: Update pg_constraint.conexclop docs for WITHOUT OVERLAPS
- f683ba0867da 18.0 landed
doc: Add PERIOD to ALTER TABLE reference docs
- d56af4c882e2 18.0 landed
doc: Add WITHOUT OVERLAPS to ALTER TABLE reference docs
- bf621059500b 18.0 landed
Add temporal FOREIGN KEY contraints
- 89f908a6d0ac 18.0 landed
- 34768ee36165 17.0 landed
Add temporal PRIMARY KEY and UNIQUE constraints
- fc0438b4e805 18.0 landed
- 46a0cd4cefb4 17.0 landed
Add stratnum GiST support function
- 7406ab623fee 18.0 landed
- 6db4598fcb82 17.0 landed
Avoid crashing when a JIT-inlined backend function throws an error.
- 5d6c64d29097 17.0 cited
Revert temporal primary keys and foreign keys
- 8aee330af55d 17.0 landed
Fix ON CONFLICT DO NOTHING/UPDATE for temporal indexes
- 144c2ce0cc75 17.0 landed
Add test for REPLICA IDENTITY with a temporal key
- 482e108cd38d 17.0 landed
Use half-open interval notation in without_overlaps tests
- 5577a71fb0cc 17.0 landed
Use daterange and YMD in without_overlaps tests instead of tsrange.
- a88c800deb6f 17.0 landed
Rename pg_constraint.conwithoutoverlaps to conperiod
- 030e10ff1a36 17.0 landed
Fix comment on gist_stratnum_btree
- 86232a49a437 17.0 landed
Add missing TAP test name
- 1ab763fc22ad 16.0 cited
Improve error handling of HMAC computations
- 5513dc6a304d 15.0 cited
Rename functions to avoid future conflicts
- ee419607381d 15.0 landed

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;