Message-ID: <78b43581-35f6-4478-8d8a-98e66a763e74@illuminatedcomputing.com>
Date: Thu, 14 Nov 2024 09:31:40 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: SQL:2011 application time
From: Paul Jungwirth <pj@illuminatedcomputing.com>
To: Sam Gabrielsson <sam@movsom.se>
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>
References: 
 <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.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>
 <3b0e067d-9a78-46f7-8027-99c19e7cde44@illuminatedcomputing.com>
Content-Language: en-US
In-Reply-To: <3b0e067d-9a78-46f7-8027-99c19e7cde44@illuminatedcomputing.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/78b43581-35f6-4478-8d8a-98e66a763e74%40illuminatedcomputing.com>
Precedence: bulk

Just sharing my progress here since it's been a week:

On 11/6/24 17:03, Paul Jungwirth wrote:
> 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.

I'm nearly done with a patch for this. I'll try to wrap it up today and get it sent this evening.

> 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 realized there are problems with the RESTRICT case also. I've got a fix written for that too, but 
it needs some tidying up. I'll submit both patches together.

The RESTRICT case needs to find the *lost* time span(s) (because it might not be the whole thing) 
and check for references to those. To do that, it calls our without_portion support function. That 
function was intended to support FOR PORTION OF, but it happens to be exactly what we need here. So 
I'm reordering the patches a bit and adjusting the documentation there.

Yours,

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