public inbox for [email protected]
help / color / mirror / Atom feedFrom: Sam Gabrielsson <[email protected]>
To: Paul Jungwirth <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: jian he <[email protected]>
Cc: Isaac Morland <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Jeff Davis <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: SQL:2011 application time
Date: Mon, 04 Nov 2024 22:16:37 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
<CACJufxHPQaD+q6o_AMg1PG6ETOWC=VqzN_=6QV9C5zbR8j=Tww@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CACJufxESqArbu=R8m3a+ZF-Yia26KTGpEnyqz+8d5BXb1o=c=w@mail.gmail.com>
<CACJufxECGaLjMcN8TJbX1fGrMFKs6qu+6RiZm98=sLcpiVpu2g@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CA+renyUFC13F0tYKxEENZtWA0YVuS5Tv+ZQkEkAwuDO1-Xke-A@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
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).
If there exists a single referencing row for a foreign key (with default
ON UPDATE NO ACTION) with a range such as:
c d
|----------|
and a single row in the referenced table, and the referenced row's range
is updated as in one of the following cases:
a b c d e f
X>>>>>>>>>>>|==============================| ERROR 1: [a,f) updated
to [b,f) or
|==============================|<<<<<<<<<<<X [a,f) updated
to [a,e)
|==================|<<<<<<<<<<<<<<< ERROR 2: [b,) updated
to [b,e)
X>>>>>>>>>>>|================================== ERROR 3: [a,) updated
to [b,)
then an error is incorrectly raised (also, if the referencing range is
[c,) instead of [c,d), then the last case also fails). See SQL-code
below for how to reproduce the errors.
---
CREATE TABLE temporal_rng (
id int4range,
valid_at daterange,
CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
);
CREATE TABLE temporal_fk_rng2rng (
id int4range,
valid_at daterange,
parent_id int4range,
CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT
OVERLAPS),
CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD
valid_at) REFERENCES temporal_rng
);
-- ERROR 1
INSERT INTO temporal_rng (id, valid_at) VALUES
('[1,2)', daterange('2018-01-01', '2018-03-01'));
INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
('[2,3)', daterange('2018-01-15', '2018-02-01'), '[1,2)');
-- 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)=([1,2), [2018-01-01,2018-03-01)) is still
referenced from table "temporal_fk_rng2rng".
UPDATE temporal_rng
SET valid_at = daterange('2018-01-05', '2018-03-01')
WHERE id = '[1,2)' AND valid_at = daterange('2018-01-01', '2018-03-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)=([1,2), [2018-01-01,2018-03-01)) is still
referenced from table "temporal_fk_rng2rng".
UPDATE temporal_rng
SET valid_at = daterange('2018-01-01', '2018-02-15')
WHERE id = '[1,2)' AND valid_at = daterange('2018-01-01', '2018-03-01');
-- ERROR 2
TRUNCATE temporal_rng, temporal_fk_rng2rng;
INSERT INTO temporal_rng (id, valid_at) VALUES
('[1,2)', daterange('2018-01-05', NULL));
INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
('[2,3)', daterange('2018-01-15', '2018-02-01'), '[1,2)');
-- 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)=([1,2), [2018-01-05,)) is still
referenced from table "temporal_fk_rng2rng".
UPDATE temporal_rng
SET valid_at = daterange('2018-01-05', '2018-02-15')
WHERE id = '[1,2)' AND valid_at = daterange('2018-01-05', NULL);
-- ERROR 3
TRUNCATE temporal_rng, temporal_fk_rng2rng;
INSERT INTO temporal_rng (id, valid_at) VALUES
('[1,2)', daterange('2018-01-01', NULL));
INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
('[2,3)', daterange('2018-01-15', '2018-02-01'), '[1,2)');
-- 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)=([1,2), [2018-01-01,)) is still
referenced from table "temporal_fk_rng2rng".
UPDATE temporal_rng
SET valid_at = daterange('2018-01-05', NULL)
WHERE id = '[1,2)' AND valid_at = daterange('2018-01-01', NULL);
---
I think the problem is the check in ri_restrict:
SELECT 1 FROM [ONLY] <fktable> x WHERE $1 = fkatt1 [AND ...]
FOR KEY SHARE OF x
it will be performed in the NO ACTION case when ri_Check_Pk_Match
returns false, and it'll then incorrectly assume that the presence of a
referencing row in the <fktable> is an error. However, ri_Check_Pk_Match
only tests wheter a temporal primary key's old range is contained by the
multirange that includes its new updated range. If that's true, then all
references are necessarily still valid. However, even if it is not
contained, all references can still be valid. So, only testing for the
presence of a referencing row is not enough.
For example, for ERROR1, the range [a,f) is updated to [b,f):
a b c d f
X>>>>>>>>>>>|==============================|
Clearly the old range:
a c d f
|==========================================|
is no longer contained by (the multirange returned by range_agg of) the
new range:
b c d f
|==============================|
So ri_Check_Pk_Match returns false. Though the row in the referencing
table:
c d
|----------|
only specifies the range [c,d), so the temporal referential integrity
still holds. However, the ri_restrict test will find a row in the
referencing table and because of that raise an error.
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
/Sam
view thread (35+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: SQL:2011 application time
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox