Re: SQL:2011 application time

On Sat, Sep 18, 2021 at 5:46 PM Corey Huinker <corey(dot)huinker(at)gmail(dot)com>
wrote:

> SYSTEM_TIME seems to allow for DATE values in the start_time and end_time
> fields,
> though I cannot imagine how that would ever be practical, unless it were
> somehow
> desirable to reject subsequent updates within a 24 hour timeframe.
>

I agree that for SYSTEM_TIME it doesn't make much sense to use anything but
the smallest time granularity.

The two "big wins" of infinity seemed (to me) to be:
>
> 1. the ability to add "AND end_time = 'infinity'" as a cheap way to get
> current rows
> 2. clauses like "WHERE CURRENT_DATE - 3 BETWEEN start_time AND end_time"
> would work.
>

Yes. OTOH there is equivalent syntax for ranges, e.g. `valid_at @> now()`.
But if you had a real PERIOD then that wouldn't be available, since you
can't use a PERIOD as an expression. Personally I think that's a shame, and
I wonder if PERIODs should be another kind of expression (much like a
column value) that evaluates to an equivalent range. Then you'd get all
kinds of operators & functions that work with them, you could `SELECT`
them, `GROUP BY` them, pass them to functions, etc.

The spec doesn't say anything about using PERIODs in those places, but it
*does* have a section on period *predicates*, which seem to be allowed
anywhere you can put an expression. The spec's discussion of this is in
4.14.2 ("Operations involving periods") and 8.20 ("<period predicate>"),
and says there should be predicates for overlap, equals, contains,
precedes, succeeds, immediately precedes, and immediately succeeds. So in
the spec, the smallest possible "element" is not a bare PERIOD, but rather
these predicates. My patch doesn't include these (it's a lot of new
syntax), and no other RDBMS seems to have implemented them. I'm inclined to
just treat PERIODs like ranges, or at least maybe let you cast from one to
another. (Casting is weird though since if a bare PERIOD isn't a valid
expression, what are you casting from/to?)

I should add that using +-Infinity for application-time bounds is
completely acceptable under my patch; you just have the option to use NULL
instead. So your examples of filtering above are fine. There aren't any
operations where we have to set a bounded rangepart to unbounded, so we
never pass a NULL; only the user would do that. We do bless NULLs by
translating MINVALUE/MAXVALUE to NULL, but that is necessary to support
arbitrary types. Even that could be refined so that we use +-Infinity when
available but NULL elsewhere. Or we could just drop MINVALUE/MAXVALUE
entirely. It's my own addition to make sentinels less arbitrary; it's not
in the standard.

One of my design goals was to let people favor ranges over PERIODs if they
like. Forcing people to use +-Infinity doesn't completely eliminate that
goal, but it does mean your ranges are different than you're used to seeing
(`[2020-01-01, Infinity)' vs [2020-01-01,)`. More importantly you can only
use {date,ts,tstz}range for application-time periods, not other rangetypes.
So I'd prefer to keep NULL bounds *possible*, even if MINVALUE/MAXVALUE
aren't giving it a sanction.

This was the alternative method of system versioning I proposed recently in
> the system versioning thread
> 1. The regular table remains unchanged, but a pg_class attribute named
> "relissystemversioned" would be set to true
> 2. I'm unsure if the standard allows dropping a column from a table
> while it is system versioned, and the purpose behind system versioning
> makes me believe the answer is a strong "no" and requiring DROP COLUMN to
> fail on relissystemversioned = 't' seems pretty straightforward.
> 3. The history table would be given a default name of $FOO_history
> (space permitting), but could be overridden with the history_table option.
> 4. The history table would have relkind = 'h'
>

+1 so far. Behavior of DDL in temporal tables is almost untouched even in
the academic literature I've read. (My bibliography mentions a few places
that at least mention that it's a hard problem.) Forbidding to drop a
column seems pretty harsh---but on the other hand that's just the tip of
the iceberg, so failing is probably the practical choice. For example what
happens to old rows if you add a NOT NULL constraint? For application-time
we can make the user responsible for figuring out the most sensible thing,
but for SYSTEM_TIME we have to figure that out ourselves. But what about
column type changes, or domains? What about removing an enum option? Or
adding a CHECK constraint? With SYSTEM_TIME the user is supposed to be
unable to change the history data, so they can't accommodate it to future
requirements.

5. The history table will only have rows that are not current, so it is
> created empty.
> 6. As such, the table is effectively append-only, in a way that vacuum
> can actually leverage, and likewise the fill factor of such a table should
> never be less than 100.
> 7. The history table could only be updated only via system defined
> triggers (insert,update,delete, alter to add columns), or row migration
> similar to that found in partitioning. It seems like this would work as the
> two tables working as partitions of the same table, but presently we can't
> have multi-parent partitions.
>

I don't think they should be sibling partitions, but I do think it would be
cool if you could ask for the history table to be partitioned. Mariadb
offers a way to do this (see my blog post comparing SQL:2011
implementations). It doesn't have to be in the first patch though, and it's
not part of the standard.

8. The history table would be indexed the same as the base table,
> except that all unique indexes would be made non-unique, and an index of pk
> + start_time + end_time would be added
>

Is there any value to indexing both start_time and end_time? Just one
already takes you to a single row.

The system-time code would need to know how to handle application-time PKs
since they are a little different, but that's not hard. And it still is
just adding a column (or two if you think they should both be there).

The history table also should not have any FKs, and no FKs should reference
it.

9. The primary key of the base table would remain the existing pk vals,
> and would basically function normally, with triggers to carry forth changes
> to the history table. The net effect of this is that the end_time value of
> all rows in the main table would always be the chosen "current" value
> (infinity, null, 9999-12-31, etc) and as such might not actually _need_ to
> be stored.
>

Interesting thought that we wouldn't really even need to store the end
time. I don't have an opinion about whether the optimization is worth the
complexity, but yeah it seems possible.

10. Queries that omit the FOR SYSTEM_TIME clause, as well as ones that
> use FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP, would simply use the base
> table directly with no quals to add.
> 11. Queries that use FOR SYSTEM_TIME and not FOR SYSTEM_TIME AS OF
> CURRENT_TIMESTAMP, then the query would do a union of the base table and
> the history table with quals applied to both.
>

I like this, but it means people can't filter directly on the columns
themselves as you suggest above. Can we detect when they're doing that?
Keep in mind it might be happening inside a user-defined function, etc. So
perhaps it is safer to always use the UNION.

12. It's a fair question whether the history table would be something
> that could be queried directly. I'm inclined to say no, because that allows
> for things like SELECT FOR UPDATE, which of course we'd have to reject.
> 13. If a history table is directly referenceable, then SELECT
> permission can be granted or revoked as normal, but all
> insert/update/delete/truncate options would raise an error.
>

It seems to break the abstraction to let people query the history table
directly. OTOH sometimes it's helpful to see behind the curtain. I could go
either way here, but I slightly favor letting people do it.

14. DROP SYSTEM VERSIONING from a table would be quite straightforward
> - the history table would be dropped along with the triggers that reference
> it, setting relissystemversioned = 'f' on the base table.
>

I like this approach a lot, and I think it's a better design than carrying
all the history inside the main table. I also like how bitemporal will Just
Work^TM. One is in user-space and the other is controlled by
Postgres---which fits the intention.

Yours,
Paul