Re: SQL:2011 application time - Mailing list pgsql-hackers

On 2/13/25 05:23, Peter Eisentraut wrote:
> 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.

I can see how this is plausible given a very strict reading of the standard, but I don't think it 
makes sense practically. And perhaps an ever stricter reading will take us back to a more practical 
understanding.

Starting with the practical argument: let's say the referenced table has two rows, with (id, 
valid_at) of (1, '[2000-01-01,2001-01-01)') and (1, '[2010-01-01,2011-01-01)'), and the referencing 
table has a row with (id, valid_at) of (1, '[2010-03-01,2010-04-01)'), and we have `referencing (id, 
PERIOD valid_at) REFERENCES referenced (id, PERIOD valid_at)`. then deleting *either* referenced row 
would cause a RESTRICT key to fail? If that is what the user wants, why not just make a non-temporal 
foreign key? If I create a temporal foreign key, it would be very surprising for it simply to ignore 
its temporal parts.

No major RDBMS vendor has implemented temporal foreign keys yet, so we don't have much to compare 
to. But Vik's periods extension doesn't behave this way for RESTRICT keys.[1] I don't think Hettie's 
does either,[2] although I'm less familiar with her project. I think she might not distinguish 
between NO ACTION and RESTRICT. I will see if I can find any other examples of how this is 
implemented, but even XTDB doesn't seem to have temporal FKs. I also checked the Teradata docs,[4] 
but they seem to support temporal FKs only as documentation, and don't enforce them. (Also 
Teradata's support precedes SQL:2011, so it's not a great guide anyway.) The IBM DB2[5] docs don't 
describe any difference between RESTRICT and NO ACTION in considering the PERIOD. (In my tests their 
temporal FKs have never actually worked, but I'll try again and see what results I get.)

None of the books about temporal tables written after SQL:2011 say RESTRICT constraints should 
ignore valid-time, but surely they would call out such a counterintuitive behavior. They criticize 
the standard pretty freely in other ways. There are lots of shorter writeups about SQL:2011 foreign 
keys,[3] and I've never seen any say that a RESTRICT key should work this way.

I think this interpretation has some tunnel vision. When we have a section that has nothing to say 
about temporal foreign keys, we shouldn't use it to discard what other sections *do* say about them.

Also I think an even stricter reading is possible. The standard says, "any change to a referenced 
column in the referenced table is prohibited if there is a matching row." The "referenced column" is 
about the *change*, but the "matching row" doesn't talk about columns or non-columns. Nothing says 
we should ignore the PERIOD part when finding matches. In addition, even for the "change" part, I 
think "referenced columns" should include the start/end columns of the PERIOD. Those are part of the 
reference. If they change, we need to look for matches.

But here are a few more subtle questions. In all cases suppose you have the same rows as above, with 
an ON UPDATE RESTRICT ON DELETE RESTRICT constraint.

Suppose you UPDATE the referenced 2010 row to be (1, '[2010-01-02,2011-01-01)'). Should it fail? I 
say no: you didn't remove any part of the referenced valid time.

Suppose you UPDATE the referenced 2010 row to be (1, '[2010-06-01,2011-01-01)'). Should it fail? I 
say yes: you did remove part of the referenced valid time.

Support you DELETE the referenced 2010 row with `FOR PORTION OF valid_at FROM 2010-01-01 TO 
2010-01-05`. I say it shouldn't fail, because again you didn't erase any of the referenced valid 
time. Otherwise you're saying that one referenced tuple spanning all of 2010 behaves differently 
from two tuples, one for '[2010-01-01,2010-01-05)' and another for '[2010-01-05,2011-01-01)'. That 
doesn't make sense, because they represent the same history.

Support you DELETE the referenced 2010 row with `FOR PORTION OF valid_at FROM 2010-01-01 TO 
2010-06-01`. I say it should fail, because again you did erase part of the referenced valid time.

Instead of those two DELETE commands, suppose you UPDATE the id to 2, with the same FOR PORTION OF. 
The first should pass and the second should fail. I but I could see an argument why they should both 
fail (like the next question).

Now suppose you UPDATE some other column, but not the id, with `FOR PORTION OF valid_at FROM 
2010-01-01 TO 2010-02-01`. Should it fail? The old referenced row is now valid only from Jan 1 to 
Feb 1, orphaning part of the reference. But you also inserted a replacement with valid_at of 
'[2010-02-01,2011-01-01)'. So the reference is still okay. With NO ACTION this is clearly allowed. 
With RESTRICT I'm inclined to say it's *still* allowed, but you could make a case that it's not.

One reason I think these cases are still allowed, even with RESTRICT, is that inserting "leftovers" 
should be transparent. It shouldn't matter whether you have one row with a big span, or many rows 
with little spans. It is surprising to get a failure in one case but not the other, when they 
represent the same history. With such unpredictability, I can't see a developer ever using a 
RESTRICT temporal constraint.

To me this is all pretty straightforward, but perhaps it would be safest to just disable RESTRICT 
for now. I can send a patch for that shortly.

Yours,

[1] https://github.com/xocolatl/periods/blob/master/periods--1.2.sql#L1715-L1744 and 
https://github.com/xocolatl/periods/blob/master/periods--1.2.sql#L2100
[2] https://github.com/hettie-d/pg_bitemporal/tree/master/sql
[3] For example https://sigmodrecord.org/publications/sigmodRecord/1209/pdfs/07.industry.kulkarni.pdf
[4] 

https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/ANSI-Temporal-Table-Support/Working-With-ANSI-Valid-Time-Tables/Creating-ANSI-Valid-Time-Tables/Usage-Notes-for-Creating-ANSI-Valid-Time-Tables/Temporal-Referential-Constraints-for-ANSI-Valid-Time-Tables
[5] https://www.ibm.com/docs/en/db2-for-zos/12?topic=constraints-referential

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