Feedback-ID: ie0a040ee:Fastmail
Message-ID: <23923ad9-a468-4c2e-8926-e47d15d1eb23@eisentraut.org>
Date: Mon, 27 Jan 2025 16:56:18 +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>
 <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@illuminatedcomputing.com>
Content-Language: en-US
From: Peter Eisentraut <peter@eisentraut.org>
In-Reply-To: <bca3e7e6-ac1f-476d-9725-d6aec9100ed0@illuminatedcomputing.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/23923ad9-a468-4c2e-8926-e47d15d1eb23%40eisentraut.org>
Precedence: bulk

On 24.01.25 00:20, Paul Jungwirth wrote:
> What about caching the FK's query plan? Could the RESTRICT test ever 
> reuse the constraint oid from the NO ACTION tests just above it? I'm 
> pretty sure that's not supposed to happen, but if it used a plan 
> generated from the NO ACTION SQL, it would exhibit the behavior we're 
> seeing. It also makes sense why it only appeared after 1772d554b0.

I think this call in ri_restrict()

     ri_BuildQueryKey(&qkey, riinfo, RI_PLAN_RESTRICT);

needs to use a different third argument for NO ACTION vs. RESTRICT, 
since we are now sometimes using different queries for them.

However, the RI_QueryKey also uses the constraint OID as part of the 
hash key, so even this mistake would not trigger any bad effect unless 
we also have OID collisions?

I'm also not able to reproduce this exact regression.diffs pattern by 
messing with various conditionals in ri_restrict(), to intentionally 
cause a confusion between the NO ACTION and RESTRICT cases.

Nevertheless, this line of investigation seems most plausible to me at 
the moment.