From dca68656d934cab3e88a39bb8b834f0027d80f26 Mon Sep 17 00:00:00 2001 From: jian he Date: Fri, 22 Mar 2024 13:30:56 +0800 Subject: [PATCH v32 1/1] minor refactor temporal FKs with multiranges regression test --- src/test/regress/expected/without_overlaps.out | 10 ++++++++++ src/test/regress/sql/without_overlaps.sql | 11 +++++++++++ 2 files changed, 21 insertions(+) diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out index ca26acb8..3da6d869 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -837,11 +837,16 @@ INSERT INTO temporal_rng (id, valid_at) VALUES INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); UPDATE temporal_rng SET valid_at = daterange('2016-02-01', '2016-03-01') WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); +begin; + ALTER TABLE temporal_fk_rng2rng + ALTER CONSTRAINT temporal_fk_rng2rng_fk + DEFERRABLE INITIALLY DEFERRED; -- a PK update that fails because both are referenced: UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". +rollback; -- changing the scalar part fails: UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); @@ -905,10 +910,15 @@ INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); +begin; + ALTER TABLE temporal_fk_rng2rng + ALTER CONSTRAINT temporal_fk_rng2rng_fk + DEFERRABLE INITIALLY DEFERRED; -- a PK delete that fails because both are referenced: DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". +rollback; -- then delete the objecting FK record and the same PK delete succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql index e5fec3fc..cb59ac47 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -674,9 +674,15 @@ INSERT INTO temporal_rng (id, valid_at) VALUES INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); UPDATE temporal_rng SET valid_at = daterange('2016-02-01', '2016-03-01') WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); + +begin; + ALTER TABLE temporal_fk_rng2rng + ALTER CONSTRAINT temporal_fk_rng2rng_fk + DEFERRABLE INITIALLY DEFERRED; -- a PK update that fails because both are referenced: UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +rollback; -- changing the scalar part fails: UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); @@ -739,8 +745,13 @@ INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); +begin; + ALTER TABLE temporal_fk_rng2rng + ALTER CONSTRAINT temporal_fk_rng2rng_fk + DEFERRABLE INITIALLY DEFERRED; -- a PK delete that fails because both are referenced: DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +rollback; -- then delete the objecting FK record and the same PK delete succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); base-commit: 085e759e9da77b7f5e859d23f72653f36277a053 prerequisite-patch-id: 3589d4c51b5f52b03318705b1c070d7db559898a prerequisite-patch-id: f03afdd3b00acd6310276e66e4c3a9fd26b3f631 -- 2.34.1