Re: SQL:2011 PERIODS vs Postgres Ranges?

On Wed, Nov 6, 2019 at 9:31 PM Paul A Jungwirth
<pj(at)illuminatedcomputing(dot)com> wrote:
> I've also added some progress on adding FOR PORTION OF to UPDATE and DELETE
> (mostly UPDATE).

I could use some guidance on where in the query-processing pipeline I
should implement some things here. Basically if you say

UPDATE t FOR PORTION OF valid_at FROM t1 TO t2

then we need to do several things:

- Add a qual like `valid_at && tsrange(t1, t2)`. (I'll assume valid_at
is a tsrange column for example's sake, but really it can be any range
type. Also valid_at may be a PERIOD instead of a range, which means
the start/end are two concrete columns instead, but that doesn't
change anything notable here.)
- Add a target entry like `SET valid_at = valid_at * tsrange(t1, t2)`.
(* = intersection. Basically each bound should be truncated to fit
within t1/t2.)
- If either bound was "cut" then also do an INSERT to restore the
cut-off part, leaving all columns unchanged except for the time part.

(DELETE t FOR PORTION OF is very similar.)

I think I understand the ModifyTable executor node enough to be able
to add the optional INSERTs there when necessary. Adding the qual and
the target entry is where I want advice.

So far I've been able to add a ForPortionOfClause when parsing and a
ForPortionOfExpr when analyzing (much like how we handle ON CONFLICT).
I could use those to add a qual and a target list entry during
analysis (in fact I've tried that and it seems to work), but I'm
pretty sure that's wrong. I recall a long post to pgsql-hackers a
month or three back lamenting how new contributors often do work in
the analysis phase that should happen later. (I can't find that now,
but if anyone has a link I'd appreciate it!) Some considerations (not
an exhaustive list):

- FOR PORTION OF should work on partitioned tables.
- It should work on automatically-updateable views.
- It should work on views with CHECK OPTION.
- It should work on views with an UPDATE rule.
- It should do the right thing for EXPLAIN output (whatever that is).
- If a function does a FOR PORTION OF command, then printing the
function definition should show that clause (and nothing extra).
- Same for printing a rule definition.
- Probably if you give a FOR PORTION OF we should forbid you from
SETting the time column(s) at the same time, since we want to set them
automatically.
- Triggers should work normally. (We *should* fire ROW triggers for
the INSERTs of the "cut off" bits. Mariadb fires them in this order,
which seems correct to me: BEFORE UPDATE, BEFORE INSERT, AFTER INSERT,
BEFORE INSERT, AFTER INSERT, AFTER UPDATE. I guess we probably want to
fire STATEMENT triggers too, probably once for each INSERT. I'll check
what other systems do there.)

So I'm thinking the right place to add the quals & target entry is
either the end of the rewriting phase or the beginning of the planning
phase. (I can still build the expressions in the analysis phase, but I
need to keep them "off to the side" in a new forPortionOf attribute
until the right time.) We definitely want the extra qual soon enough
to help choose indexes. Perhaps we even want to see it in EXPLAIN
output (which happens if I add it during analysis); personally I kind
of find that helpful. Do we want to add it after processing rewrite
rules (and will that change EXPLAIN output)?

For adding the target entry, if we are forbidding the user from
SETting things, that check needs to happen after processing rewrite
rules, right? (Of course it doesn't hurt to check in several places if
there is some reason to do that.)

Btw I thought about whether we could implement this feature completely
on top of either triggers or rules, but I don't think it's quite that
simple. Basically: because you could also UPDATE/DELETE the table
*without* a FOR PORTION OF, sometimes we need to do the extra things
and sometimes not, and we need a way of knowing which is which. And
then supporting PERIODs requires a little extra "magic" beyond that.
But if someone has a great idea I'm open to hearing about it. :-)

Thanks,
Paul