pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Paul Jungwirth <pj@illuminatedcomputing.com>
To: Sam Gabrielsson <sam@movsom.se>
Cc: Peter Eisentraut <peter@eisentraut.org>
Cc: jian he <jian.universality@gmail.com>
Cc: Isaac Morland <isaac.morland@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Jeff Davis <pgsql@j-davis.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: SQL:2011 application time
Date: Wed, 6 Nov 2024 17:03:09 -0800
Message-ID: <3b0e067d-9a78-46f7-8027-99c19e7cde44@illuminatedcomputing.com> (raw)
In-Reply-To: <e23b9d86e07301e8a38a3d0617e3719c@movsom.se>
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.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>
	<e23b9d86e07301e8a38a3d0617e3719c@movsom.se>

On 11/4/24 13:16, Sam Gabrielsson wrote:
> 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).

Thank you for the report! I confirmed that this is a problem. In ri_restrict we fail if any fk 
records still match the being-changed pk, but for temporal if you're merely shrinking the pk range, 
fk references could still wind up being valid (if you're only shrinking it a little). So we need to 
do more work.

> 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

This solution looks like it will work to me. Basically: find FKs that still match the PK, but only 
fail if they are no longer covered.

IIRC for RESTRICT it is *correct* to reject the change, so we would want to keep the old SQL there, 
and only update it for NOACTION.

I'll work on a fix and submit another set of patches.

Yours,

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com





view thread (230+ messages)  latest in thread

Message-ID: <3b0e067d-9a78-46f7-8027-99c19e7cde44@illuminatedcomputing.com>
Permalink:  ../3b0e067d-9a78-46f7-8027-99c19e7cde44@illuminatedcomputing.com/
Also on:    postgresql.org/message-id/3b0e067d-9a78-46f7-8027-99c19e7cde44@illuminatedcomputing.com

 · 

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: pgsql-hackers@postgresql.org
  Cc: pj@illuminatedcomputing.com, sam@movsom.se, peter@eisentraut.org, jian.universality@gmail.com, isaac.morland@gmail.com, robertmhaas@gmail.com, pgsql@j-davis.com, pgsql-hackers@lists.postgresql.org
  Subject: Re: SQL:2011 application time
  In-Reply-To: <3b0e067d-9a78-46f7-8027-99c19e7cde44@illuminatedcomputing.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox