Re: SQL:2011 application time

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.