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

On 2/21/25 07:21, Peter Eisentraut wrote:
 > On 17.02.25 07:42, Paul Jungwirth wrote:
 > I think maybe we have a different idea of what RESTRICT should do in the first place.  Because all
 > the different behavior options come from the same underlying difference.
 >
 > Consider a related example.  What if you have in the referenced table just one row:
 >
 > (1, '[2000-01-01,2015-01-01)')
 >
 > and in the referencing row as above
 >
 > (1, '[2010-03-01,2010-04-01)')
 >
 > with ON UPDATE RESTRICT and ON DELETE RESTRICT.  And then you run
 >
 > UPDATE pk SET valid_at = '[2000-01-01,2021-01-01)' WHERE id = 1;
 >
 > So this extends the valid_at of the primary key row, which is completely harmless for the
 > referential integrity.  But I argue that this is an error under ON UPDATE RESTRICT.  Because that's
 > the whole point of RESTRICT over NO ACTION: Even harmless changes to the primary key row are
 > disallowed if the row is referenced.
 >
 > If we accept that this is an error, then the rest follows.  If the primary row is split into two:
 >
 > (1, '[2000-01-01,2011-01-01)')
 > (1, '[2011-01-01,2015-01-01)')
 >
 > then the command that extends the validity
 >
 > UPDATE pk SET valid_at = '[2011-01-01,2021-01-01)'
 >    WHERE id = 1 AND valid_at = '[2011-01-01,2015-01-01)';
 >
 > must also be an error, even though the row it is updating is not actually the one that is
 > referenced.  If this were allowed, then the behavior would be different depending on in which way
 > the primary key ranges are split up, which is not what we want.

I agree with that last principle: it shouldn't matter how the primary keys are split up. But it 
seems to me that "matches" in the standard should include the period. It does for NO ACTION, so why 
not RESTRICT? That's why your example of expanding the referenced range succeeds. None of the 
referenced moments were changed, so there are no referencing moments to match.

 > I'm not sure what other behavior of RESTRICT there might be that is internally consistent and is
 > meaningfully different from NO ACTION.

The difference between RESTRICT and NO ACTION for temporal foreign keys is the same as the 
difference for ordinary foreign keys: we perform the check prior to applying any "action" or 
allowing any other commands to provide substitutes for the lost references. There are tests in 
sql/without_overlaps.sql showing how their behavior differs.

Also you haven't yet explained why anyone would *want* to use RESTRICT as you've described it, since 
the temporal part of the key is just ignored, and they could just define a non-temporal foreign key 
instead. Or to be precise, it fails *more* than a non-temporal foreign key, because changing the 
period can violate the constraint, even though we ignore the period when looking for matches.

But since we don't agree on the behavior, it seems best to me to wait to implement RESTRICT. Not 
much is lost, since NO ACTION is so similar. We can wait for the SQL committee to clarify things, or 
see what another RDBMS vendor does.

FWIW IBM DB2 claims to support temporal RESTRICT foreign keys,[1] but this week I tested 11.5 and 
12.1 via evaluation downloads, IBM Cloud, and AWS Marketplace. In all cases I got an error like this:

    db2 => create table t (id integer not null, ds date not null, de date not null, name varchar(4000), 
period business_time (ds, de));
    DB20000I  The SQL command completed successfully.
    db2 => alter table t add constraint tpk primary key (id, business_time without overlaps);
    DB20000I  The SQL command completed successfully.
    db2 =>
    db2 => create table t2 (id integer not null, ds date not null, de date not null, name 
varchar(4000), t_id integer, period business_time (ds, de));
    DB20000I  The SQL command completed successfully.
    db2 => alter table t2 add constraint t2pk primary key (id, business_time without overlaps);
    DB20000I  The SQL command completed successfully.
    db2 => alter table t2 add constraint t2fkt foreign key (t_id, period business_time) references t 
(id, period business_time) on delete restrict;
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0104N  An unexpected token "business_time" was found following "gn key
    (t_id, period".  Expected tokens may include:  "<space>".  SQLSTATE=42601

It looks like the docs are just wrong, and they don't recognize the `period` keyword yet. (The error 
message suggests that `period` is being interpreted as a column name, and there should be a comma or 
closing paren after it.) I tried a lot of other guesses at different syntax, but nothing worked. 
Maybe it is only supported on z/OS, not Linux? If anyone knows someone who works on/with DB2, I'd be 
glad to talk to them.

Curiously, their docs say that temporal foreign keys *only* support ON DELETE RESTRICT:[2]

 > ON DELETE RESTRICT must be specified when PERIOD BUSINESS_TIME is also specified.

Here are some patches removing support for RESTRICT and also rebasing to fix a lot of merge 
conflicts. The rebase is to 6c349d83b6.

[1] https://www.ibm.com/docs/en/db2-for-zos/13?topic=constraints-referential
[2] https://www.ibm.com/docs/en/db2-for-zos/13?topic=statements-alter-table

Yours,

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