Message-ID: <472e52a1-1960-49e3-a029-f271ba2670d8@illuminatedcomputing.com>
Date: Wed, 19 Mar 2025 20:33:36 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: SQL:2011 application time
To: Peter Eisentraut <peter@eisentraut.org>
Cc: 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>
 <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@illuminatedcomputing.com>
 <1b45f81d-4e99-4813-a2c6-72bf5fd80980@eisentraut.org>
 <e773bc11-4ac1-40de-bb91-814e02f05b6d@eisentraut.org>
 <3e061027-781d-42c1-b6e8-bafbf3b51033@eisentraut.org>
 <c3af7816-1944-4030-ab73-5b055de8cba3@illuminatedcomputing.com>
 <3b33690e-0d67-47a5-8424-93ac1fed4c26@eisentraut.org>
 <1085d8a3-17a7-4639-afd2-bd6dfa09713d@illuminatedcomputing.com>
 <22c4dd72-66fa-4f76-95f2-757d754e2aad@eisentraut.org>
From: Paul Jungwirth <pj@illuminatedcomputing.com>
Content-Language: en-US
In-Reply-To: <22c4dd72-66fa-4f76-95f2-757d754e2aad@eisentraut.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/472e52a1-1960-49e3-a029-f271ba2670d8%40illuminatedcomputing.com>
Precedence: bulk

On 3/3/25 02:05, Peter Eisentraut wrote:
 > In the theory of the SQL standard, executing referential actions and checking the foreign-key
 > constraint are two separate steps.  So it kind of goes like this:
 >
 > 1. run command
 > 2. run any referential actions
 > 3. check that foreign key is still satisfied
 >
 > This is why the default referential action is called "NO ACTION": It just skips the step 2.  But it
 > still does step 3.
 >
 > This means that under RESTRICT and with my interpretation, the check for a RESTRICT violation in
 > step 2 can "ignore" the period part, but the step 3 still has to observe the period part.
 >
 > In the implementation, these steps are mostly combined into one trigger function, so it might be a
 > bit tricky to untangle them.

I understand that there are those separate steps. But it still doesn't make sense for RESTRICT to 
ignore the temporal part of the key. Actually, talking about "actions" reminds me of another reason: 
the effect of CASCASE/SET NULL/SET DEFAULT actions should also be limited to only the part of 
history that was updated/deleted in the referenced row. (This is why their implementation depends on 
FOR PORTION OF.) Otherwise a temporal CASCADE/SET NULL/SET DEFAULT would wreck havoc on your data. 
But if that's how these other actions behave, shouldn't RESTRICT behave the same way? Again, it's 
not clear why anyone would want a temporal foreign key that ignores its temporal attribute. And as I 
explained before, I don't think that's what a careful read of the standard says.

Yours,

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