Re: SQL:2011 application time

On Wed, Dec 6, 2023 at 12:59 AM Peter Eisentraut <peter(at)eisentraut(dot)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 `(at)-`(dot) 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 `(at)-@`, 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(at)illuminatedcomputing(dot)com