Message-ID: <c46c47dc-cbea-4ca8-8ec9-30b7e239251f@illuminatedcomputing.com>
Date: Thu, 23 Jan 2025 15:54:15 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: SQL:2011 application time
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Peter Eisentraut <peter@eisentraut.org>,
 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>
 <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>
 <9bb3eea7-820f-4932-92cb-cb564fc44caf@eisentraut.org>
 <bca3e7e6-ac1f-476d-9725-d6aec9100ed0@illumin! atedcomputing.com>
 <246156.1737674914@sss.pgh.pa.us>
Content-Language: en-US
From: Paul Jungwirth <pj@illuminatedcomputing.com>
In-Reply-To: <246156.1737674914@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/c46c47dc-cbea-4ca8-8ec9-30b7e239251f%40illuminatedcomputing.com>
Precedence: bulk

On 1/23/25 15:28, Tom Lane wrote:
> Paul Jungwirth <pj@illuminatedcomputing.com> writes:
>> I can't find a regression.diffs in the second link. Is there one? I can't tell if it's the same
>> failure as in the first link as not.
> 
> It is the same, but the diff is buried in some other file,
> probably regress_log_027_stream_regress.

Ah yes, I found it. It's the same failure:

=== dumping 
/home/bf/bf-build/mylodon/HEAD/pgsql.build/testrun/recovery/027_stream_regress/data/regression.diffs ===
diff -U3 /home/bf/bf-build/mylodon/HEAD/pgsql/src/test/regress/expected/without_overlaps.out 
/home/bf/bf-build/mylodon/HEAD/pgsql.build/testrun/recovery/027_stream_regress/data/results/without_overlaps.out
--- /home/bf/bf-build/mylodon/HEAD/pgsql/src/test/regress/expected/without_overlaps.out	2025-01-21 
13:46:02.766931451 +0000
+++ 
/home/bf/bf-build/mylodon/HEAD/pgsql.build/testrun/recovery/027_stream_regress/data/results/without_overlaps.out 
2025-01-22 01:19:54.287558175 +0000
@@ -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
=== EOF ===

>> I ran installcheck-parallel on my own machine continuously over night and haven't been able to
>> reproduce this yet. How many cases have appeared on the build farm? More than these two? And just to
>> confirm: they are only since committing 1772d554b0?
> 
> I've only noticed the two, but I did not mount an aggressive search.
> It's possible that there were failures before 1772d554b0, since I
> now see that the diff is in a test case that is older than that.

Okay, I'll keep in mind that it could be older.

>> The infrequent failure made me suspect a memory error. It's hard to come up with explanations.
> 
> Same error on two different machines makes it hard to credit hardware
> glitches, if that's what you mean.  I could believe a bad pointer
> accessing unpredictable memory, but perhaps valgrind would catch that.

Right, something like a bad pointer: the kind of memory error that *I* cause, not the celestial 
bodies. But I don't think it's a great theory considering how clean, limited, and consistent the 
test failure is.

Another thought was that the test here is UPDATing two rows at once, and somehow things happen in an 
unusual order for these failures. But again for a RESTRICT check we're only querying the referencing 
table. It really looks like the RESTRICT constraint is doing the 1772d554b0 NO ACTION check. . . .

Yours,

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