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>, jian he <jian.universality@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2023-12-31T08:51:49Z
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 Wed, Dec 6, 2023 at 12:59 AM Peter Eisentraut <peter@eisentraut.org> wrote:
 >
 > On 02.12.23 19:41, Paul Jungwirth wrote:
 > > So what do you think of this idea instead?:
 > >
 > > We could add a new (optional) support function to GiST that translates
 > > "well-known" strategy numbers into the opclass's own strategy numbers.
 >
 > I had some conversations about this behind the scenes.  I think this
 > idea makes sense.

Here is a patch series with the GiST stratnum support function added. I put this into a separate 
patch (before all the temporal ones), so it's easier to review. Then in the PK patch (now #2) we 
call that function to figure out the = and && operators. I think this is a big improvement.

I provide a couple "example" implementations:

- An identity function that returns whatever you give it. The core gist opclasses use this since 
they use the RT* constants. Even though not all opclasses support all strategies, it is okay to 
return a stratnum with no amop entry. You will just get an error when you try to make a temporal PK 
with that type as the WITHOUT OVERLAPS part (which is natural for the types we're talking about).

- A function that translates RT*StrategyNumbers to BT*StrategyNumbers when possible (just 
=/</<=/>/>=, and we really only need =). This is what the btree_gist opclasses use. (No other 
RT*StrategyNumber can be translated, which means you can only use these types for the non-WIHOUT 
OVERLAPS part, but again that is natural.)

I didn't add a similar function to GIN. It's not possible to use GIN for temporal PKs, so I don't 
think it makes sense.


## Foreign Keys

For FKs, I need a couple similar things:

- The ContainedBy operator (<@ for rangetypes).
- An aggregate function to combine referenced rows (instead of hardcoding range_agg as before).

I look up ContainedBy just as I'm doing with Equal & Overlap for PKs. The aggregate function is 
another optional support function.

I broke out that support function into another independent patch here. Then I updated by FKs patch 
to use it (and the ContainedBy operator).


## FOR PORTION OF

Then for FOR PORTION OF I need an intersect operator (*) and a new "leftovers" operator.

We have an intersect operator (for range & multirange at least), but no strategy number for it, thus 
no amop entry. My patch adds that, **but** it is neither a search operator ('s') nor ordering ('o'), 
so I've added a "portion" option ('p'). I'm not sure this is completely valid, since `FOR PORTION 
OF` is not really an *index* operation, but it does seem index-adjacent: you would only/usually use 
it on something with a temporal PK (which is an index). And it is an analogous situation, where 
pg_amop entries tell us how to implement the extensible parts. But if all this seems like the wrong 
approach please let me know.

The new leftovers operator similarly has 'p' for amoppurpose and another amop entry.

The leftovers operator returns an array of T, where T is the type of the valid_at column. Then we'll 
insert a new "leftovers" row for each array entry. So we aren't assuming only "before" and "after" 
(which doesn't work for multiranges or two-dimensional spaces as you'd have with bitemporal or spatial).

But now that "leftovers" are becoming more of an external-facing part of Postgres, I feel we should 
have a less silly name. (That's too bad, because "leftovers" is memorable and non-ambiguous, and 
computer pioneers used all kinds of silly names, so if you tell me I don't have to be quite so 
professional, maybe I'll go back to it.) I considered things like "without" or "multi-subtract" or 
"except". I went with "without portion", which is nice because it evokes FOR PORTION OF and doesn't 
limit the scope to rangetypes.

For the symbol I like `@-`. It conveys the similarity to subtraction, and "@" can be a mnemonic for 
"array". (Too bad we can't use `--`, ha ha.) I also thought about `@-@`, but that is used already by 
path_length and lseg_length, and maybe a non-commutative operator deserves a non-palindromic name.

The {multi,}range_without_portion procs & operators are broken out into a separate commit, and the 
FPO patch now uses them in the exec node. It always made me a little uneasy to have rangetype code 
in nodeModifyTable.c, and now it's gone.

Then the last thing I need for FOR PORTION OF is a "constructor". In SQL:2011 you use `FOR PORTION 
OF valid_at FROM '2000-01-01' TO '2010-01-01'`. But FROM and TO don't really work for non-range 
types. So I added an alternate syntax that is `FOR PORTION OF valid_at 
(tsmultirange(tsrange('2001-01-01', '2002-02-02'), tsrange('2003-03-03', '2004-04-04')))`. In other 
words parens wrapping a value of the type you're using. I still support FROM & TO for building a 
range type, so we follow the standard.

That's it for now. Multiranges should be fully supported (but need lots more tests), as well as 
custom types. I've updated some of the docs, but I need to go through them and clarify where things 
don't necessarily have to be ranges.

Rebased to cb44a8345e.

Yours,

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