Feedback-ID: ie0a040ee:Fastmail
Content-Type: multipart/mixed; boundary="------------yb78c8EgP02btOrF14ZNzl7V"
Message-ID: <3e061027-781d-42c1-b6e8-bafbf3b51033@eisentraut.org>
Date: Thu, 13 Feb 2025 14:23:25 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: SQL:2011 application time
From: Peter Eisentraut <peter@eisentraut.org>
To: Paul Jungwirth <pj@illuminatedcomputing.com>
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>
 <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@illuminatedcomputing.com>
 <1b45f81d-4e99-4813-a2c6-72bf5fd80980@eisentraut.org>
 <e773bc11-4ac1-40de-bb91-814e02f05b6d@eisentraut.org>
Content-Language: en-US
In-Reply-To: <e773bc11-4ac1-40de-bb91-814e02f05b6d@eisentraut.org>
Archived-At: <https://www.postgresql.org/message-id/3e061027-781d-42c1-b6e8-bafbf3b51033%40eisentraut.org>
Precedence: bulk

This is a multi-part message in MIME format.
--------------yb78c8EgP02btOrF14ZNzl7V
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 23.01.25 16:40, Peter Eisentraut wrote:
> I think my interpretation of what RESTRICT should do is different.
> 
> The clause "Execution of referential actions" in the SQL standard only 
> talks about referenced and referencing columns, not periods.  So this 
> would mean you can change the period columns all you want (as long as 
> they maintain referential integrity).  So it would be like the NO ACTION 
> case.  But you can't change any of the non-period columns on the primary 
> key if they are referenced by any referencing columns, even if the 
> respective periods are disjoint.
> 
> Maybe this makes sense, or maybe this is a mistake (neglected to update 
> this part when periods were introduced?).  But in any case, I can't get 
> from this to what the patch does.  When I apply the tests in the patch 
> without the code changes, what I would intuitively like are more errors 
> than the starting state, but your patch results in fewer errors.

After staring at this a bit more, I think my interpretation above was 
not correct.  This seems better:

The clause "Execution of referential actions" in the SQL standard only
talks about referenced and referencing columns, not periods.  The 
RESTRICT error is raised when a "matching row" exists in the referencing 
table.  The "matching row" is determined purely by looking at the 
"normal" columns of the key, not the period columns.

So in our implementation in ri_restrict(), ISTM, we just need to ignore 
the period/range columns when doing the RESTRICT check.

Attached is a quick patch that demonstrates how this could work.  I 
think the semantics of this are right and make sense.

--------------yb78c8EgP02btOrF14ZNzl7V
Content-Type: text/plain; charset=UTF-8;
 name="0001-WIP-Fix-RESTRICT-behavior-for-temporal-foreign-keys.patch.nocfbot"
Content-Disposition: attachment;
 filename*0="0001-WIP-Fix-RESTRICT-behavior-for-temporal-foreign-keys.pat";
 filename*1="ch.nocfbot"
Content-Transfer-Encoding: base64

RnJvbSA3ZDJhZTIyNmRhNjc4Mzg4MjUxMjBmNzU5YTkxMjM2MmNmOTFlMDY1IE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBQZXRlciBFaXNlbnRyYXV0IDxwZXRlckBlaXNlbnRy
YXV0Lm9yZz4KRGF0ZTogVGh1LCAxMyBGZWIgMjAyNSAxMzoxNToyMyArMDEwMApTdWJqZWN0
OiBbUEFUQ0hdIFdJUDogRml4IFJFU1RSSUNUIGJlaGF2aW9yIGZvciB0ZW1wb3JhbCBmb3Jl
aWduIGtleXMKCi0tLQogc3JjL2JhY2tlbmQvdXRpbHMvYWR0L3JpX3RyaWdnZXJzLmMgfCAx
MiArKysrKysrKysrKysKIDEgZmlsZSBjaGFuZ2VkLCAxMiBpbnNlcnRpb25zKCspCgpkaWZm
IC0tZ2l0IGEvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L3JpX3RyaWdnZXJzLmMgYi9zcmMvYmFj
a2VuZC91dGlscy9hZHQvcmlfdHJpZ2dlcnMuYwppbmRleCA4NDczNDQ4ODQ5Yy4uYmJkMWQ1
NzEyZjIgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9yaV90cmlnZ2Vycy5j
CisrKyBiL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9yaV90cmlnZ2Vycy5jCkBAIC03ODcsNiAr
Nzg3LDEyIEBAIHJpX3Jlc3RyaWN0KFRyaWdnZXJEYXRhICp0cmlnZGF0YSwgYm9vbCBpc19u
b19hY3Rpb24pCiAJCQlPaWQJCQlwa190eXBlID0gUklBdHRUeXBlKHBrX3JlbCwgcmlpbmZv
LT5wa19hdHRudW1zW2ldKTsKIAkJCU9pZAkJCWZrX3R5cGUgPSBSSUF0dFR5cGUoZmtfcmVs
LCByaWluZm8tPmZrX2F0dG51bXNbaV0pOwogCisJCQlpZiAocmlpbmZvLT5oYXNwZXJpb2Qg
JiYgIWlzX25vX2FjdGlvbikKKwkJCXsKKwkJCQlpZiAoaSA9PSByaWluZm8tPm5rZXlzIC0g
MSkKKwkJCQkJY29udGludWU7CisJCQl9CisKIAkJCXF1b3RlT25lTmFtZShhdHRuYW1lLAog
CQkJCQkJIFJJQXR0TmFtZShma19yZWwsIHJpaW5mby0+ZmtfYXR0bnVtc1tpXSkpOwogCQkJ
c3ByaW50ZihwYXJhbW5hbWUsICIkJWQiLCBpICsgMSk7CkBAIC0yNzM0LDYgKzI3NDAsMTIg
QEAgcmlfUmVwb3J0VmlvbGF0aW9uKGNvbnN0IFJJX0NvbnN0cmFpbnRJbmZvICpyaWluZm8s
CiAJCQlEYXR1bQkJZGF0dW07CiAJCQlib29sCQlpc251bGw7CiAKKwkJCWlmIChyaWluZm8t
Pmhhc3BlcmlvZCAmJiBpc19yZXN0cmljdCkKKwkJCXsKKwkJCQlpZiAoaWR4ID09IHJpaW5m
by0+bmtleXMgLSAxKQorCQkJCQljb250aW51ZTsKKwkJCX0KKwogCQkJbmFtZSA9IE5hbWVT
dHIoYXR0LT5hdHRuYW1lKTsKIAogCQkJZGF0dW0gPSBzbG90X2dldGF0dHIodmlvbGF0b3Jz
bG90LCBmbnVtLCAmaXNudWxsKTsKLS0gCjIuNDguMQoK

--------------yb78c8EgP02btOrF14ZNzl7V--