Re: SQL:2011 application time - Mailing list pgsql-hackers

On Sat, Sep 18, 2021 at 5:46 PM Corey Huinker <corey.huinker@gmail.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.