MIME-Version: 1.0
Date: Mon, 04 Nov 2024 22:16:37 +0100
From: Sam Gabrielsson <sam@movsom.se>
To: Paul Jungwirth <pj@illuminatedcomputing.com>
Cc: Peter Eisentraut <peter@eisentraut.org>, jian he
 <jian.universality@gmail.com>, Isaac Morland <isaac.morland@gmail.com>,
 Robert Haas <robertmhaas@gmail.com>, Jeff Davis <pgsql@j-davis.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: SQL:2011 application time
In-Reply-To: <57ea0668-5205-426e-b934-efc89f2186c2@illuminatedcomputing.com>
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
 <CACJufxHPQaD+q6o_AMg1PG6ETOWC=VqzN_=6QV9C5zbR8j=Tww@mail.gmail.com>
 <b4363a52-2279-4612-8953-1100fab0c772@illuminatedcomputing.com>
 <30dc3561-d7ab-42be-a1c5-096643423590@illuminatedcomputing.com>
 <b824fc91-f534-4096-8d94-de8e21b06915@illuminatedcomputing.com>
 <73d09e24-b6b9-4c89-8c0a-8842ca0f81f9@illuminatedcomputing.com>
 <c002230c-8879-4b2c-ad05-585d6572347c@illuminatedcomputing.com>
 <CACJufxESqArbu=R8m3a+ZF-Yia26KTGpEnyqz+8d5BXb1o=c=w@mail.gmail.com>
 <CACJufxECGaLjMcN8TJbX1fGrMFKs6qu+6RiZm98=sLcpiVpu2g@mail.gmail.com>
 <081aa43c-3a97-4bd5-a498-ba02e0a7570d@illuminatedcomputing.com>
 <54390149-11d6-4f52-bbe8-f69832ceb3bd@eisentraut.org>
 <eedaaca9-d615-4934-ab00-39ec376d9b37@eisentraut.org>
 <CA+renyUFC13F0tYKxEENZtWA0YVuS5Tv+ZQkEkAwuDO1-Xke-A@mail.gmail.com>
 <9b7cb814-c78c-489f-85b2-32be4398647f@illuminatedcomputing.com>
 <95c6d49b-7d60-4ad0-820c-e00ae1b2a3d4@illuminatedcomputing.com>
 <57ea0668-5205-426e-b934-efc89f2186c2@illuminatedcomputing.com>
User-Agent: Loopia Webmail/1.6.9
Message-ID: <e23b9d86e07301e8a38a3d0617e3719c@movsom.se>
Content-Type: text/plain; charset=US-ASCII;
 format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/e23b9d86e07301e8a38a3d0617e3719c%40movsom.se>
Precedence: bulk

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