Feedback-ID: ie0a040ee:Fastmail
Message-ID: <9bb3eea7-820f-4932-92cb-cb564fc44caf@eisentraut.org>
Date: Thu, 23 Jan 2025 16:31:09 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: SQL:2011 application time
To: Paul Jungwirth <pj@illuminatedcomputing.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, jian he <jian.universality@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 vignesh C <vignesh21@gmail.com>
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
 <95c6d49b-7d60-4ad0-820c-e00ae1b2a3d4@illuminatedcomputing.com>
 <57ea0668-5205-426e-b934-efc89f2186c2@illuminatedcomputing.com>
 <6dc196d4-8853-49d7-8dd3-e391058451c1@eisentraut.org>
 <bf6b3fc5-b1c2-4fdf-ad27-5de45770c58c@illuminatedcomputing.com>
 <66e47fc0-77b7-4763-9b70-5c0b7ff15f21@eisentraut.org>
 <a2ee0c1a-5ef0-43ab-897c-b56adb25b34f@illuminatedcomputing.com>
 <333d3886-b737-45c3-93f4-594c96bb405d@eisentraut.org>
 <e8e51e74-10d7-410d-a1d1-8ec681d8436a@eisentraut.org>
 <f4056005-0fb7-417a-bcb3-e704f9aeadc3@illuminatedcomputing.com>
 <6a2247d9-461d-4b0a-b692-f0f3c0fab27f@eisentraut.org>
 <8f995a4c-64e3-40cd-bfed-06b54a280d36@illuminatedcomputing.com>
 <0832f18d-523f-4c15-acb0-338a785150c5@illuminatedcomputing.com>
 <5f0c25de-2c13-4fb5-a4df-a1e03c43cd14@illuminatedcomputin! g.com>
 <1b45f81d-4e99-4813-a2c6-72bf5fd80980@eisentraut.org>
 <3611540.1737518430@sss.pgh.pa.us>
Content-Language: en-US
From: Peter Eisentraut <peter@eisentraut.org>
In-Reply-To: <3611540.1737518430@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/9bb3eea7-820f-4932-92cb-cb564fc44caf%40eisentraut.org>
Precedence: bulk

On 22.01.25 05:00, Tom Lane wrote:
> Peter Eisentraut <peter@eisentraut.org> writes:
>> I have committed the fix for foreign key NO ACTION (patch 0002, this did
>> not require patch 0001).
> 
> That commit seems to be causing occasional buildfarm failures:
> 
> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=indri&dt=2025-01-22%2001%3A29%3A35
> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=mylodon&dt=2025-01-22%2001%3A17%3A14
> 
> Both of these look like
> 
> --- /Users/buildfarm/bf-data/HEAD/pgsql.build/src/test/regress/expected/without_overlaps.out	2025-01-21 20:29:36
> +++ /Users/buildfarm/bf-data/HEAD/pgsql.build/src/test/regress/results/without_overlaps.out	2025-01-21 20:43:08
> @@ -1792,8 +1792,6 @@
>     SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN daterange('2018-01-01', '2018-01-05')
>                         WHEN lower(valid_at) = '2018-02-01' THEN daterange('2018-01-05', '2018-03-01') END
>     WHERE id = '[6,7)';
> -ERROR:  update or delete on table "temporal_rng" violates RESTRICT setting of foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
> -DETAIL:  Key (id, valid_at)=([6,7), [2018-01-01,2018-02-01)) is referenced from table "temporal_fk_rng2rng".
>   -- a PK update that fails because both are referenced (even before commit):
>   BEGIN;
>     ALTER TABLE temporal_fk_rng2rng
> 
> ie, an expected error did not get thrown.

I suspect the nested locking clauses in the new SQL query in the patch. 
I don't see anything else in the patch that would possibly create this 
kind unstable behavior.

Paul, what do you think?

I'll revert the patch soon unless we have a quick fix coming.