Re: SQL:2011 application time

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