Horton

Re: SQL:2011 application time

Paul A Jungwirth <pj@illuminatedcomputing.com>

View thread
From: Paul Jungwirth <pj@illuminatedcomputing.com>
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>
Date: 2025-02-26T05:15:43Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Rename gist stratnum support function

  2. Remove support for temporal RESTRICT foreign keys

  3. Cache NO ACTION foreign keys separately from RESTRICT foreign keys

  4. Fix NO ACTION temporal foreign keys when the referenced endpoints change

  5. Improve whitespace in without_overlaps test

  6. Tests for logical replication with temporal keys

  7. Support for GiST in get_equal_strategy_number()

  8. Make the conditions in IsIndexUsableForReplicaIdentityFull() more explicit

  9. Replace get_equal_strategy_number_for_am() by get_equal_strategy_number()

  10. Improve internal logical replication error for missing equality strategy

  11. Simplify IsIndexUsableForReplicaIdentityFull()

  12. Fix ALTER TABLE / REPLICA IDENTITY for temporal tables

  13. doc: Update pg_constraint.conexclop docs for WITHOUT OVERLAPS

  14. doc: Add PERIOD to ALTER TABLE reference docs

  15. doc: Add WITHOUT OVERLAPS to ALTER TABLE reference docs

  16. Add temporal FOREIGN KEY contraints

  17. Add temporal PRIMARY KEY and UNIQUE constraints

  18. Add stratnum GiST support function

  19. Avoid crashing when a JIT-inlined backend function throws an error.

  20. Revert temporal primary keys and foreign keys

  21. Fix ON CONFLICT DO NOTHING/UPDATE for temporal indexes

  22. Add test for REPLICA IDENTITY with a temporal key

  23. Use half-open interval notation in without_overlaps tests

  24. Use daterange and YMD in without_overlaps tests instead of tsrange.

  25. Rename pg_constraint.conwithoutoverlaps to conperiod

  26. Fix comment on gist_stratnum_btree

  27. Add missing TAP test name

  28. Improve error handling of HMAC computations

  29. Rename functions to avoid future conflicts

Attachments

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