Horton

Re: SQL:2011 application time

Paul A Jungwirth <pj@illuminatedcomputing.com>

View thread
From: Paul Jungwirth <pj@illuminatedcomputing.com>
To: jian he <jian.universality@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>, Peter Eisentraut <peter@eisentraut.org>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2024-05-12T03:25:45Z
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

On 5/11/24 17:00, jian he wrote:
>> I hope I understand the problem correctly.
>> my understanding is that we are trying to solve a corner case:
>> create table t(a int4range, b int4range, primary key(a, b WITHOUT OVERLAPS));
>> insert into t values ('[1,2]','empty'), ('[1,2]','empty');
>>
> 
> 
> but we still not yet address for cases like:
> create table t10(a int4range, b int4range, unique (a, b WITHOUT OVERLAPS));
> insert into t10 values ('[1,2]','empty'), ('[1,2]','empty');
> 
> one table can have more than one temporal unique constraint,
> for each temporal unique constraint adding a check isempty constraint
> seems not easy.

I think we should add the not-empty constraint only for PRIMARY KEYs, not all UNIQUE constraints. 
The empty edge case is very similar to the NULL edge case, and while every PK column must be 
non-null, we do allow nulls in ordinary UNIQUE constraints. If users want to have 'empty' in those 
constraints, I think we should let them. And then the problems you give don't arise.

> Maybe we can just mention that the special 'empty' range value makes
> temporal unique constraints not "unique".

Just documenting the behavior is also an okay solution here I think. I see two downsides though: (1) 
it makes rangetype temporal keys differ from PERIOD temporal keys (2) it could allow more 
planner/etc bugs than we have thought of. So I think it's worth adding the constraint instead.

> also we can make sure that
> FOREIGN KEY can only reference primary keys, not unique temporal constraints.
> so the unique temporal constraints not "unique" implication is limited.
> I played around with it, we can error out these cases in the function
> transformFkeyCheckAttrs.

I don't think it is a problem to reference a temporal UNIQUE constraint, even if it contains empty 
values. An empty value means you're not asserting that row at any time (though another row might 
assert the same thing for some time), so it could never contribute toward fulfilling a reference anyway.

I do think it would be nice if the *reference* could contain empty values. Right now the FK SQL will 
cause that to never match, because we use `&&` as an optimization, but we could tweak the SQL (maybe 
for v18 instead) so that users could get away with that kind of thing. As I said in an earlier 
email, this would be you an escape hatch to reference a temporal table from a non-temporal table. 
Otherwise temporal tables are "contagious," which is a bit of a drawback.

Yours,

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