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: Isaac Morland <isaac.morland@gmail.com>, Robert Haas <robertmhaas@gmail.com>, Jeff Davis <pgsql@j-davis.com>, Peter Eisentraut <peter@eisentraut.org>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2024-07-18T03:34:42Z
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 7/9/24 00:15, jian he wrote:
>> **Option 2**: Add a new operator, called &&&, that works like && except an empty range *does*
>> overlap another empty range. Empty ranges should still not overlap anything else. This would fix the
>> exclusion constraint. You could add `(5, 'empty')` once but not twice. This would allow empties to
>> people who want to use them. (We would still forbid them if you define a PERIOD, because those come
>> with the CHECK constraint mentioned above.)
>> And there is almost nothing to code. But it is mathematically suspect to say an empty range overlaps
>> something small (something with zero width) but not something big. Surely if a && b and b <@ c, then
>> a && c? So this feels like the kind of elegant hack that you eventually regret.
> I think we can Forbid empties,not not mess with pg_class.
> 
> to make the communication smooth, i've set the base commit to
> 46a0cd4cefb4d9b462d8cc4df5e7ecdd190bea92
> {Add temporal PRIMARY KEY and UNIQUE constraints}
> https://git.postgresql.org/cgit/postgresql.git/commit/?id=46a0cd4cefb4d9b462d8cc4df5e7ecdd190bea92
> you can git reset --hard 46a0cd4cefb4d9b462d8cc4df5e7ecdd190bea92
> then apply the attached patch.
> 
> I hope I understand it correctly.
> previously revert is only because the special value: empty.
> i tried to use the operator &&&, new gist strategy number, pg_amop
> entry to solve the problem.
> Now with the applied patch, if the range column is specified WITHOUT OVERLAPS,
> then this column is not allowed to have any empty range value.
> 
> logic work through:
> * duplicate logic of range_overlaps but disallow empty value. also
> have the operator &&&, (almost equivalent to &&)
> * add new gist strategy number
> * thanks to  add stratnum GiST support function
> (https://git.postgresql.org/cgit/postgresql.git/commit/?id=6db4598fcb82a87a683c4572707e522504830a2b)
> now we can set the strategy number to the mapped new function
> (equivalent to range_overlaps, but error out empty value)
> * in ComputeIndexAttrs, set the strategy number to the newly created
> StrategyNumber in "else if (iswithoutoverlaps)" block.
> * Similarly refactor src/backend/utils/adt/rangetypes_gist.c make the
> index value validation using newly created function.

I like this approach a lot, but I'd like to hear what some other people think?

Jian he's &&& operator is similar to what I proposed upthread, but when either operand is an empty 
value it simply raises an error. (It should be an ereport, not an elog, and I think 
multirange_overlaps_multirange_internal is missing the empty check, but I can clean things up when I 
integrate it into the patch series.)

This is much simpler than everything I'm doing: checking for empties in the executor phase, adding a 
field to pg_class, setting things in the relcache, and checking for empties in existing rows when 
you add an index. This patch uses existing infrastructure to do all the work. It seems like a much 
cleaner solution.

Unlike my proposed &&& operator, it doesn't have weird mathematical consequences.

At first I thought raising an error was not great, but it's the same thing you get when you divide 
by zero. It's fine for an operator to have a restricted domain of inputs. And we would only use this 
internally for primary keys and unique constraints, where indeed raising an error is just what we want.

If I don't hear objections (or think of something myself :-), I'm inclined to use this approach.

But what do people think?

Yours,

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