SQL:2011 application time

On Wed, Jun 30, 2021 at 10:39 AM Paul A Jungwirth
<pj@illuminatedcomputing.com> wrote:

Here is a set of patches to add SQL:2011 application-time support (aka
valid-time).

Here is a small fix to prevent `FOR PORTION OF valid_at FROM MAXVALUE
TO foo` or `FROM foo TO MINVALUE`. I rebased on latest master too.

Yours,
Paul

On Fri, Jul 2, 2021 at 2:39 PM Paul A Jungwirth
<pj@illuminatedcomputing.com> wrote:

On Wed, Jun 30, 2021 at 10:39 AM Paul A Jungwirth
<pj@illuminatedcomputing.com> wrote:

Here is a set of patches to add SQL:2011 application-time support (aka
valid-time).

Here is a small fix to prevent `FOR PORTION OF valid_at FROM MAXVALUE
TO foo` or `FROM foo TO MINVALUE`. I rebased on latest master too.

Here is a patch set that cleans up the catalog docs for pg_period. The
columns have changed since that was written, and also we use a
different sgml structure on those pages now. Note pg_period still
contains a couple essentially-unused columns, perislocal and
perinhcount. Those are intended for supporting table inheritance, so
I've left them in.

Paul

On Sat, Jul 03, 2021 at 10:46:55AM -0700, Paul A Jungwirth wrote:

On Fri, Jul 2, 2021 at 2:39 PM Paul A Jungwirth
<pj@illuminatedcomputing.com> wrote:

On Wed, Jun 30, 2021 at 10:39 AM Paul A Jungwirth
<pj@illuminatedcomputing.com> wrote:

Here is a set of patches to add SQL:2011 application-time support (aka
valid-time).

Here is a small fix to prevent `FOR PORTION OF valid_at FROM MAXVALUE
TO foo` or `FROM foo TO MINVALUE`. I rebased on latest master too.

Here is a patch set that cleans up the catalog docs for pg_period. The
columns have changed since that was written, and also we use a
different sgml structure on those pages now. Note pg_period still
contains a couple essentially-unused columns, perislocal and
perinhcount. Those are intended for supporting table inheritance, so
I've left them in.

Hi Paul,

Thanks for working on this. It would be a great improvement.

I wanted to test the patches but:

patch 01: does apply but doesn't compile, attached the compile errors.
patch 04: does not apply clean.

Please fix and resend.

--
Jaime Casanova
Director de Servicios Profesionales
SystemGuards - Consultores de PostgreSQL

On Sat, Sep 4, 2021 at 12:56 PM Jaime Casanova
<jcasanov@systemguards.com.ec> wrote:

patch 01: does apply but doesn't compile, attached the compile errors.
patch 04: does not apply clean.

Thanks for taking a look! I've rebased & made it compile again. v7 attached.

Yours,
Paul

On Mon, Sep 6, 2021 at 12:53 PM Paul A Jungwirth <
pj@illuminatedcomputing.com> wrote:

On Sat, Sep 4, 2021 at 12:56 PM Jaime Casanova
<jcasanov@systemguards.com.ec> wrote:

patch 01: does apply but doesn't compile, attached the compile errors.
patch 04: does not apply clean.

Thanks for taking a look! I've rebased & made it compile again. v7
attached.

Yours,
Paul

Hi,
For v7-0001-Add-PERIODs.patch :

+ * Portions Copyright (c) 1996-2018, PostgreSQL Global Development Group

It seems the year (2018) should be updated to 2021.

For RemovePeriodById(), it seems table_open() can be called
after SearchSysCache1(). This way, if HeapTupleIsValid(tup) is true,
table_open() can be skipped.

For tablecmds.c, AT_PASS_ADD_PERIOD is defined as 5 with AT_PASS_ADD_CONSTR
etc moved upward. Do we need to consider compatibility ?

There are a few TODO's such as:
+ * TODO: What about periods?

Are they going to be addressed in the next round of patches ?

There seems to be some overlap between ATExecAddPeriod()
and AddRelationNewPeriod().
Is it possible to reduce code duplication ?

Cheers

On Mon, Sep 06, 2021 at 12:52:37PM -0700, Paul A Jungwirth wrote:

On Sat, Sep 4, 2021 at 12:56 PM Jaime Casanova
<jcasanov@systemguards.com.ec> wrote:

patch 01: does apply but doesn't compile, attached the compile errors.
patch 04: does not apply clean.

Thanks for taking a look! I've rebased & made it compile again. v7 attached.

patch 01: does apply but gives a compile warning (which is fixed by patch
02)
"""
parse_utilcmd.c: In function ‘generateClonedIndexStmt’:
parse_utilcmd.c:1730:2: warning: ISO C90 forbids mixed declarations and code [-Wdeclaration-after-statement]
Period *p = makeNode(Period);
^~~~~~
"""

patch 03: produces these compile errors.

analyze.c: In function ‘transformForPortionOfBound’:
analyze.c:1171:3: warning: ISO C90 forbids mixed declarations and code [-Wdeclaration-after-statement]
A_Const *n2 = makeNode(A_Const);
^~~~~~~
analyze.c:1172:10: error: ‘union ValUnion’ has no member named ‘type’
n2->val.type = T_Null;
^
analyze.c:1172:18: error: ‘T_Null’ undeclared (first use in this function)
n2->val.type = T_Null;
^~~~~~
analyze.c:1172:18: note: each undeclared identifier is reported only once for each function it appears in

--
Jaime Casanova
Director de Servicios Profesionales
SystemGuards - Consultores de PostgreSQL

On Fri, Sep 10, 2021 at 6:50 PM Jaime Casanova
<jcasanov@systemguards.com.ec> wrote:

patch 01: does apply but gives a compile warning (which is fixed by patch
02)
[snip]
patch 03: produces these compile errors.

I did a rebase and fixed this new error, as well as the warnings.

On Mon, Sep 6, 2021 at 1:40 PM Zhihong Yu <zyu@yugabyte.com> wrote:

+ * Portions Copyright (c) 1996-2018, PostgreSQL Global Development Group

It seems the year (2018) should be updated to 2021.

Done.

For RemovePeriodById(), it seems table_open() can be called after SearchSysCache1(). This way, if HeapTupleIsValid(tup) is true, table_open() can be skipped.

This seems like it permits a race condition when two connections both
try to drop the period, right?

For tablecmds.c, AT_PASS_ADD_PERIOD is defined as 5 with AT_PASS_ADD_CONSTR etc moved upward. Do we need to consider compatibility ?

I don't think there is a compatibility problem---can you explain?
These symbols aren't used outside tablecmds.c and the values aren't
saved anywhere AFAIK.

There are a few TODO's such as:
Are they going to be addressed in the next round of patches ?

These are mostly questions I'm hoping a reviewer can help me answer,
but I'll take a pass through them and see which I can remove myself.
Several are for adding support for partitioned tables, where I would
definitely appreciate help.

There seems to be some overlap between ATExecAddPeriod() and AddRelationNewPeriod().
Is it possible to reduce code duplication ?

I've refactored those functions to remove some duplication, but I
think I prefer the old version---let me know if you have suggestions
to avoid the duplication in a nicer way.

Oh also I realized fp_triggers.c wasn't included in the last few patch
files---I'm sorry about that!

Latest files attached. Thanks for the reviews!

Paul

So I've been eagerly watching this thread and hoping to have time to devote
to it. I've also been looking at the thread at
/messages/by-id/CALAY4q8Pp699qv-pJZc4toS-e2NzRJKrvaX-xqG1aqj2Q+Ww-w@mail.gmail.com
that covers system versioning, and per our conversation far too long ago
(again, my bad) it's obvious that the two efforts shouldn't do anything
that would be in conflict with one another, as we eventually have to
support bitemporal [1]https://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeaturessql2011.pdf tables: tables that have both system versioning and
an application period.

Below is a list of observations and questions about this proposed patch of
itself in isolation, but mostly about how it relates to the work being done
for system versioning.

1. This patch creates a pg_period catalog table, whereas the system
versioning relies on additions to pg_attribute to identify the start/end
columns. Initially I thought this was because it was somehow possible to
have *multiple* application periods defined on a table, but in reading [1]https://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeaturessql2011.pdf
I see that there are some design suppositions that would make a second
application period impossible[2]In the bitemporal table example in [1] - the application period get the defined primary key, and the system_time period would be merely unique. I can also see where having this table
would facilitate the easy creation of INFORMATION_SCHEMA.PERIODS. I was
previously unaware that this info schema table was a thing, but I have
found references to it, though I'm unclear as to whether it's supposed to
have information about system versioned tables in it as well.

Q 1.1. Would a bitemporal table have two entries in that view?
Q 1.2. Could you see being able to implement this without pg_period, using
only additions to pg_attribute (start/end for system temporal, start/end
for application, plus an addition for period name)?
Q 1.3. Can you see a way to represent the system versioning in pg_period
such that bitemporal tables were possible?

2. The system versioning effort has chosen 'infinity' as their end-time
value, whereas you have chosen NULL as that makes sense for an unbounded
range. Other databases seem to leverage '9999-12-31 23:59:59' (SQLServer,
IIRC) whereas some others seem to used '2999-12-31 23:59:59' but those
might have been home-rolled temporal implementations. To further add to the
confusion, the syntax seems to specify the keyword of MAXVALUE, which
further muddies things. The system versioning people went with 'infinity'
seemingly because it prescribe and end to the world like SQLServer did, but
also because it allowed for a primary key based on (id, endtime) and that's
just not possible with NULL endtime values.

Q 2.1. Do you have any thoughts about how to resolve this notational logjam?

3. I noticed some inconsistency in the results from various "SELECT * FROM
portion_of_test" examples. In some, the "valid_at" range is shown but not
columns that make it up, and in some others, the "valid_from" and
"valid_to" columns are shown, with no mention of the period. From what I've
seen, the period column should be invisible unless invoked, like ctid or
xmin.

4. The syntax '2018-03-04' AT TIME ZONE INTERVAL '2' HOUR TO MINUTE simply
confounded me. I googled around for it, but could find no matches for
postgres exception in mailing list discussions circa 2003. I tried it out
myself and, lo and behold

# SELECT '2018-03-04' AT TIME ZONE INTERVAL '2' HOUR TO MINUTE;
timezone
---------------------
2018-03-04 05:02:00
(1 row)

I really didn't expect that to work, or even "work". I can see that it
added 2 minutes to UTC's perspective on my local concept of midnight, but I
don't understand what it's supposed to mean.

Q 4.1. What does it mean?

5. I haven't seen any actual syntax conflicts between this patch and the
system versioning patch. Both teams added basically the same keywords,
though I haven't dove more deeply into any bison incompatibilities. Still,
it's a great start.

6. Overall, I'm really excited about what this will mean for data
governance in postgres.

[1]: https://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeaturessql2011.pdf
https://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeaturessql2011.pdf
[2]: In the bitemporal table example in [1] - the application period get the defined primary key, and the system_time period would be merely unique
defined primary key, and the system_time period would be merely unique

On Mon, Sep 13, 2021 at 12:12 AM Paul A Jungwirth <
pj@illuminatedcomputing.com> wrote:

Show quoted text

On Fri, Sep 10, 2021 at 6:50 PM Jaime Casanova
<jcasanov@systemguards.com.ec> wrote:

patch 01: does apply but gives a compile warning (which is fixed by patch
02)
[snip]
patch 03: produces these compile errors.

I did a rebase and fixed this new error, as well as the warnings.

On Mon, Sep 6, 2021 at 1:40 PM Zhihong Yu <zyu@yugabyte.com> wrote:

+ * Portions Copyright (c) 1996-2018, PostgreSQL Global Development Group

It seems the year (2018) should be updated to 2021.

Done.

For RemovePeriodById(), it seems table_open() can be called after

SearchSysCache1(). This way, if HeapTupleIsValid(tup) is true, table_open()
can be skipped.

This seems like it permits a race condition when two connections both
try to drop the period, right?

For tablecmds.c, AT_PASS_ADD_PERIOD is defined as 5 with

AT_PASS_ADD_CONSTR etc moved upward. Do we need to consider compatibility ?

I don't think there is a compatibility problem---can you explain?
These symbols aren't used outside tablecmds.c and the values aren't
saved anywhere AFAIK.

There are a few TODO's such as:
Are they going to be addressed in the next round of patches ?

These are mostly questions I'm hoping a reviewer can help me answer,
but I'll take a pass through them and see which I can remove myself.
Several are for adding support for partitioned tables, where I would
definitely appreciate help.

There seems to be some overlap between ATExecAddPeriod() and

AddRelationNewPeriod().

Is it possible to reduce code duplication ?

I've refactored those functions to remove some duplication, but I
think I prefer the old version---let me know if you have suggestions
to avoid the duplication in a nicer way.

Oh also I realized fp_triggers.c wasn't included in the last few patch
files---I'm sorry about that!

Latest files attached. Thanks for the reviews!

Paul

Hi Corey,

Thanks for all the good questions!

1. This patch creates a pg_period catalog table, whereas the system versioning relies on additions to pg_attribute to identify the start/end columns. Initially I thought this was because it was somehow possible to have multiple application periods defined on a table, but in reading [1] I see that there are some design suppositions that would make a second application period impossible[2]. I can also see where having this table would facilitate the easy creation of INFORMATION_SCHEMA.PERIODS. I was previously unaware that this info schema table was a thing, but I have found references to it, though I'm unclear as to whether it's supposed to have information about system versioned tables in it as well.

Yes, information_schema.periods is given by the standard. Having
pg_period seems like a natural place to store periods, since they are
separate entities. I think that is a better design than just storing
them as extra fields in pg_attribute. It follows normal normalization
rules.

The standard forbids multiple application-time periods per table. From
SQL:2011 in the SQL/Foundation section
(7IWD2-02-Foundation-2011-12.pdf available from
http://www.wiscorp.com/sql20nn.zip) under 11.27 <add table period
definition>:

5) If <table period definition> contains <application time period specification> ATPS, then:
b) The table descriptor of T shall not include a period descriptor other than a system-time period descriptor.

In other words you can add both a SYSTEM TIME period and one other
application-time period (whose name is your choice), but if you
already have an application-time period, you can't add another one.

I also checked other RDBMSes and none of them allow it either:

In Mariadb 10.6.4 (the latest) I get "ERROR 4154 (HY000); Cannot
specify more than one application-time period".

Oracle disallows it with a vague error:

SQL> create table t2 (id int, valid_from date, valid_til date,
period for valid_at (valid_from, valid_til), period for valid_at2
valid_from, valid_til));
create table t2 (id int, valid_from date, valid_til date, period
for valid_at (valid_from, valid_til), period for valid_at2
(valid_from, valid_til))

*
ERROR at line 1:
ORA-55603: invalid flashback archive or valid time period command

(Using different start/end columns for each period doesn't change the result.)

In IBM DB2 you can only have one because application-time periods must
be named "business_time" (not joking).

Mssql (2019) doesn't support application periods.

Personally I feel like it's a weird limitation and I wouldn't mind
supporting more, but my current implementation only allows for one,
and I'd have to rethink some things to do it differently.

Also: I think information_schema.periods *should* include SYSTEM_TIME
periods. The spec says (in SQL/Schemata, file
7IWD2-11-Schemata-2011-12.pdf at the link above), "The PERIODS base
table has one row for each period defined for a table. It effectively
contains a representation of the period descriptors." It doesn't say
anything about excluding system-time periods.

I checked mariadb, mssql, oracle, and db2, and I could only find this
table in db2, as syscat.periods. It includes both application-time and
system-time periods.

The spec calls for the columns table_catalog, table_schema,
table_name, period_name, start_column_name, and end_column_name. There
isn't a column to distinguish the period type, but since a period is a
system-time period iff its name is "SYSTEM_TIME", technically such a
column isn't needed.

The db2 columns are periodname, tabschema, tabname, begincolname,
endcolname, periodtype, historytabschema, and historytabname. The
periodtype column is either A or S (for application-time or
system-time).

Q 1.1. Would a bitemporal table have two entries in that view?

Yes.

Q 1.2. Could you see being able to implement this without pg_period, using only additions to pg_attribute (start/end for system temporal, start/end for application, plus an addition for period name)?

Not just period name, but also the range type associated with the
period (which should be determined at period creation, so that you can
pass an option to disambiguate if there are two ranges defined for the
same base type), the constraint oid (to prevent end <= start), and
some more data for inherited tables (not really used yet). It seems
ugly to hang all these extra values on a pg_attribute record.

Q 1.3. Can you see a way to represent the system versioning in pg_period such that bitemporal tables were possible?

Yes. Even though the name "SYSTEM_TIME" is technically enough, I'd
still include a pertype column to make distinguishing system vs
application periods easier and more obvious.

2. The system versioning effort has chosen 'infinity' as their end-time value, whereas you have chosen NULL as that makes sense for an unbounded range. Other databases seem to leverage '9999-12-31 23:59:59' (SQLServer, IIRC) whereas some others seem to used '2999-12-31 23:59:59' but those might have been home-rolled temporal implementations. To further add to the confusion, the syntax seems to specify the keyword of MAXVALUE, which further muddies things. The system versioning people went with 'infinity' seemingly because it prescribe and end to the world like SQLServer did, but also because it allowed for a primary key based on (id, endtime) and that's just not possible with NULL endtime values.

I think it's a little weird that our system-time patch mutates your
primary key. None of the other RDMBSes do that. I don't think it's
incompatible (as long as the system time patch knows how to preserve
the extra period/range data in an application-time temporal key), but
it feels messy to me.

I would prefer if system-time and application-time used the same value
to mean "unbounded". Using null means we can support any type (not
just types with +-Infinity). And it pairs nicely with range types. If
the only reason for system-time to use Infinity is the primary key, I
think it would be better not to mutate the primary key (and store the
historical records in a separate table as other RDMSes do).

Btw Oracle also uses NULL to mean "unbounded".

We presently forbid PKs from including expressions, but my patch lifts
that exception so it can index a rangetype expression built from the
period start & end columns. So even if we must include the system-time
end column in a PK, perhaps it can use a COALESCE expression to store
Infinity even while using NULL to signify "currently true" from a user
perspective.

3. I noticed some inconsistency in the results from various "SELECT * FROM portion_of_test" examples. In some, the "valid_at" range is shown but not columns that make it up, and in some others, the "valid_from" and "valid_to" columns are shown, with no mention of the period. From what I've seen, the period column should be invisible unless invoked, like ctid or xmin.

In most cases the tests test the same functionality with both PERIODs
and rangetype columns. For FKs they test all four combinations of
PERIOD-referencing-PERIOD, PERIOD-referencing-range,
range-referencing-PERIOD, and range-referencing-range. If valid_at is
a genuine column, it is included in SELECT *, but not if it is a
PERIOD.

4. The syntax '2018-03-04' AT TIME ZONE INTERVAL '2' HOUR TO MINUTE simply confounded me.

Me too! I have no idea what that is supposed to mean. But that
behavior predates my patch. I only had to deal with it because it
creates a shift-reduce conflict with `FOR PORTION OF valid_at FROM x
TO y`, where x & y are expressions. I asked about this syntax at my
PgCon 2020 talk, but I haven't ever received an answer. Perhaps
someone else knows what this kind of INTERVAL means (as a modifier of
a time value).

5. I haven't seen any actual syntax conflicts between this patch and the system versioning patch. Both teams added basically the same keywords, though I haven't dove more deeply into any bison incompatibilities. Still, it's a great start.

I think that's right. Early on the other patch used `FOR PERIOD SYSTEM
TIME (x, y)` instead of the standard `FOR PERIOD SYSTEM_TIME (x, y)`
but I believe that was fixed, so that the period name is an identifier
and not two keywords.

6. Overall, I'm really excited about what this will mean for data governance in postgres.

Me too, and thank you for the detailed review!

Yours,
Paul

In IBM DB2 you can only have one because application-time periods must

be named "business_time" (not joking).

I saw that as well, and it made me think that someone at IBM is a fan of
Flight Of The Conchords.

Personally I feel like it's a weird limitation and I wouldn't mind
supporting more, but my current implementation only allows for one,
and I'd have to rethink some things to do it differently.

I'm satisfied that it's not something we need to do in the first MVP.

Yes. Even though the name "SYSTEM_TIME" is technically enough, I'd
still include a pertype column to make distinguishing system vs
application periods easier and more obvious.

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 have seen instances where home-rolled application periods used
date values, which had similar problems where certain intermediate updates
would simply have to be discarded in favor of the one that was still
standing at midnight.

2. The system versioning effort has chosen 'infinity' as their end-time

value, whereas you have chosen NULL as that makes sense for an unbounded
range. Other databases seem to leverage '9999-12-31 23:59:59' (SQLServer,
IIRC) whereas some others seem to used '2999-12-31 23:59:59' but those
might have been home-rolled temporal implementations. To further add to the
confusion, the syntax seems to specify the keyword of MAXVALUE, which
further muddies things. The system versioning people went with 'infinity'
seemingly because it prescribe and end to the world like SQLServer did, but
also because it allowed for a primary key based on (id, endtime) and that's
just not possible with NULL endtime values.

I think it's a little weird that our system-time patch mutates your
primary key. None of the other RDMBSes do that. I don't think it's
incompatible (as long as the system time patch knows how to preserve
the extra period/range data in an application-time temporal key), but
it feels messy to me.

Per outline below, I'm proposing an alternate SYSTEM_TIME implementation
that would leave the PK as-is.

I would prefer if system-time and application-time used the same value
to mean "unbounded". Using null means we can support any type (not
just types with +-Infinity). And it pairs nicely with range types. If
the only reason for system-time to use Infinity is the primary key, I
think it would be better not to mutate the primary key (and store the
historical records in a separate table as other RDMSes do).

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. Granted, there's very specific new syntax to do that properly,
but you know somebody's gonna see the columns and try to do it that way.

Btw Oracle also uses NULL to mean "unbounded".

Huh, I missed that one. That is good in that it gives some precedence to
how you've approached it.

We presently forbid PKs from including expressions, but my patch lifts
that exception so it can index a rangetype expression built from the
period start & end columns. So even if we must include the system-time
end column in a PK, perhaps it can use a COALESCE expression to store
Infinity even while using NULL to signify "currently true" from a user
perspective.

Either way seems viable, but I understand why you want to leverage ranges
in this way.

3. I noticed some inconsistency in the results from various "SELECT *

FROM portion_of_test" examples. In some, the "valid_at" range is shown but
not columns that make it up, and in some others, the "valid_from" and
"valid_to" columns are shown, with no mention of the period. From what I've
seen, the period column should be invisible unless invoked, like ctid or
xmin.

In most cases the tests test the same functionality with both PERIODs
and rangetype columns. For FKs they test all four combinations of
PERIOD-referencing-PERIOD, PERIOD-referencing-range,
range-referencing-PERIOD, and range-referencing-range. If valid_at is
a genuine column, it is included in SELECT *, but not if it is a
PERIOD.

Ok, I'll have to look back over the test coverage to make sure that I
understand the behavior now.

4. The syntax '2018-03-04' AT TIME ZONE INTERVAL '2' HOUR TO MINUTE

simply confounded me.

Me too! I have no idea what that is supposed to mean. But that
behavior predates my patch. I only had to deal with it because it
creates a shift-reduce conflict with `FOR PORTION OF valid_at FROM x
TO y`, where x & y are expressions. I asked about this syntax at my
PgCon 2020 talk, but I haven't ever received an answer. Perhaps
someone else knows what this kind of INTERVAL means (as a modifier of
a time value).

I think I'll open this as a separate thread, because it would simplify
matters if we can reject this nonsense syntax.

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'
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.
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
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.
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.
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.
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.

The benefits to your effort here would be:

1. No change to the primary key except for the ones dictated by application
period
2. The INFORMATION_SCHEMA view need merely take into account The
pg_class.relkind = 'h' entries
3. system versioning is no longer mutating (trigger on X updates X), which
eliminates the possibility that application period triggers get into a loop
4. DROP SYSTEM VERSIONING would be entirely transparent to application
versioning.

Thoughts?

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.

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

Here are some new patches rebased on the latest master.

I haven't made any substantive changes, but I should have time soon to
take a stab at supporting partitioned tables and removing some of my own
TODOs (things like making sure I'm locking things correctly). I don't
think there is any outstanding feedback other than that.

But in the meantime here are some up-to-date patches.

Yours,

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

On Tue, Nov 16, 2021 at 3:55 PM Paul Jungwirth <pj@illuminatedcomputing.com>
wrote:

I haven't made any substantive changes, but I should have time soon to
take a stab at supporting partitioned tables and removing some of my own
TODOs (things like making sure I'm locking things correctly).

Hello,

Here are updated patches. They are rebased and clean up some of my TODOs.
Here is what remains:

- Various TODOs asking for advice about concurrency things: where to lock,
when to copy structs, etc. I'd appreciate some review on these from someone
more experienced than me.

- Supporting FOR PORTION OF against updateable views. I'll keep working on
this, but I thought there was enough progress to pass along new patches in
the meantime.

- Support partitioned tables. I think this is a medium-size effort, and I'm
not sure whether it's really needed for pg 15 or something we can add
later. I'm going to do my best to get it done though. (I should have more
time for this project now: having a sixth baby recently made side projects
challenging for a while, but lately things have been getting easier.)
Partitioning could use some design discussion though, both for application
time alone and for bitemporal tables (so overlapping with the system time
work). Here are some thoughts so far:

- Creating a PERIOD on a partitioned table should automatically create
the PERIOD (and associated constraints) on the child tables. This one seems
easy and I'll try to get it done soon.

- Sort of related, but not strictly partitioning: CREATE TABLE LIKE
should have a new INCLUDING PERIODS option. (I'm tempted to include this
under INCLUDING CONSTRAINTS, but I think a separate option is nicer since
it gives more control.)

- If you partition by something in the scalar part of the temporal PK,
that's easy. I don't think we have to do anything special there. I'd like
to add some tests about it though.

- We should allow temporal primary keys on the top-level partitioned
table, even though they are essentially exclusion constraints. Whereas in
the general case an exclusion constraint cannot prove its validity across
all the tables, a temporal PK *can* prove its validity so long the
partition key includes at least one scalar part of the temporal PK (so that
all records for one "entity" get routed to the same table).

- If you partition by the temporal part of the temporal PK, things are
harder. I'm inclined to forbid this, at least for v15. Suppose you
partition by the start time. Then you wind up with the same entity spread
across several tables, so you can't validate the overall exclusion
constraint anymore.

- OTOH you *could* partition by application-time itself (not start time
alone nor end time alone) where each partition has application-time
ranges/periods that are trimmed to fit within that partition's limits. Then
since each partition is responsible for a non-overlapping time period, you
could validate the overall exclusion constraint. You'd just have to add
some logic to tuple re-routing that could transform single records into
multiple records. For example if each partition holds a different year and
you INSERT a record that is valid for a decade, you'd have to insert one
row into ten partitions, and change the application-time range/period of
each row appropriately. This is a special kind of range partitioning. I
don't have any ideas how to make hash or list partitioning work on the
temporal part of the PK. I don't think we should allow it.

- Partitioning by application time requires no special syntax.
Partitioning by system time (if that's desired) would probably require
extra (non-standard) syntax. Mariadb has this:
https://mariadb.com/kb/en/system-versioned-tables/#storing-the-history-separately
Perhaps that is orthogonal to application-time partitioning though. It
sounds like people think we should store non-current system time in a
separate table (I agree), and in that case I think a bitemporal table that
is partitioned by scalar keys or application-time would just have a
separate system-time history table for each partition, and that would Just
Work. And if we *do* want to partition by system time too, then it would be
transparent to the application-time logic.

- Since system time doesn't add anything to your PK (or at least it
shouldn't), there is no extra complexity around dealing with exclusion
constraints. We should just guarantee that all *current* rows land in the
same partition, because for a bitemporal table that's the only one that
needs a temporal PK. I guess that means you could partition by end
system-time but not start system-time. This would be an exception to the
rule that a PK must include the partition keys. Instead we'd say that all
current (i.e. non-historical) records stay together (at the system-time
level of partitioning).

- I don't think system-time partitioning needs to be in v15. It seems
more complicated than ordinary partitioning.

Yours,
Paul

On 21.11.21 02:51, Paul A Jungwirth wrote:

Here are updated patches. They are rebased and clean up some of my
TODOs.

This patch set looks very interesting. It's also very big, so it's
difficult to see how to get a handle on it. I did a pass through it
to see if there were any obvious architectural or coding style
problems. I also looked at some of your TODO comments to see if I had
something to contribute there.

I'm confused about how to query tables based on application time
periods. Online, I see examples using AS OF, but in the SQL standard
I only see this used for system time, which we are not doing here.
What is your understanding of that?

v10-0001-Add-PERIODs.patch

src/backend/commands/tablecmds.c

Might be worth explaining somewhere why AT_PASS_ADD_PERIOD needs to be
its own pass. -- Ah, this is explained in ATPrepCmd(). Maybe that is
okay, but I would tend to prefer a comprehensive explanation here
rather than sprinkled around.

make_period_not_backward(): Hardcoding the name of the operator as "<"
is not good. You should perhaps lookup the less-than operator in the
type cache. Look around for TYPECACHE_LT_OPR for how this is usually done.

validate_period(): Could use an explanatory comment. There are a
bunch of output arguments, and it's not clear what all of this is
supposed to do, and what "validating" is in this context.

MergeAttributes(): I would perhaps initially just prohibit inheritance
situations that involve periods on either side. (It should work for
partitioning, IMO, but that should be easier to arrange.)

AlterTableGetLockLevel(): The choice of AccessExclusiveLock looks
correct. I think the whole thing can also be grouped with some of the
other "affects concurrent SELECTs" cases?

Maybe the node type Period could have a slightly more specific name,
perhaps PeriodDef, analogous to ColumnDef?

I didn't follow why indexes would have periods, for example, the new
period field in IndexStmt. Is that explained anywhere?

While reading this patch I kept wondering whether it would be possible
to fold periods into pg_attribute, perhaps with negative attribute
numbers. Have you looked into something like that? No doubt it's
also complicated, but it might simplify some things, like the name
conflict checking.

v10-0002-Add-temporal-PRIMARY-KEY-and-UNIQUE-constraints.patch

src/backend/catalog/Catalog.pm: I see you use this change in the
subsequent patches, but I would recommend skipping all this. The
comments added are kind of redundant with the descr fields anyway.

transformIndexConstraint(): As above, we can't look up the && operator
by name. In this case, I suppose we should look it up through the
index AM support operators.

Further, the additions to this function are very complicated and not
fully explained. I'm suspicious about things like
findNewOrOldColumn() -- generally we should look up columns by number
not name. Perhaps you can add a header comment or split out the code
further into smaller functions.

pg_dump.c getIndexes() has been refactored since to make
version-specific additions easier. But your patch is now failing to
apply because of this.

Of course, the main problem in this patch is that for most uses it
requires btree_gist. I think we should consider moving that into
core, or at least the support for types that are most relevant to this
functionality, specifically the date/time types. Aside from user
convenience, this would also allow writing more realistic test cases.

v10-0003-Add-UPDATE-DELETE-FOR-PORTION-OF.patch

Use of MINVALUE and MAXVALUE for unbounded seems problematic to me.
(If it is some value, it is not really larger than any value.) We
have the keyword UNBOUNDED, which seems better suited.

src/backend/access/brin/brin_minmax_multi.c

These renaming changes seem unrelated (but still seem like a good
idea). Should they be progressed separately?

Again, some hardcoded operator name lookup in this patch.

I don't understand why a temporal primary key is required for doing
UPDATE FOR PORTION OF. I don't see this in the standard.

v10-0004-Add-temporal-FOREIGN-KEYs.patch

Do we really need different trigger names depending on whether the
foreign key is temporal?

range_as_string() doesn't appear to be used anywhere.

I ran out of steam on this patch, it's very big. But it seems sound
in general.

How to proceed. I suppose we could focus on committing 0001 and 0002
first. That would be a sensible feature set even if the remaining
patches did not make a release. I do feel we need to get btree_gist
into core. That might be a big job by itself. I'm also bemused why
btree_gist is so bloated compared to btree_gin. btree_gin uses macros
to eliminate duplicate code where btree_gist is full of
copy-and-paste. So there are some opportunities there to make things
more compact. Is there anything else you think we can do as
preparatory work to make the main patches more manageable?

On Wed, Jan 5, 2022 at 11:07 AM Peter Eisentraut <
peter.eisentraut@enterprisedb.com> wrote:

On 21.11.21 02:51, Paul A Jungwirth wrote:

Here are updated patches. They are rebased and clean up some of my
TODOs.

This patch set looks very interesting. It's also very big, so it's
difficult to see how to get a handle on it. I did a pass through it
to see if there were any obvious architectural or coding style
problems. I also looked at some of your TODO comments to see if I had
something to contribute there.

I'm confused about how to query tables based on application time
periods. Online, I see examples using AS OF, but in the SQL standard
I only see this used for system time, which we are not doing here.
What is your understanding of that?

Paul has previously supplied me with this document
https://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeaturessql2011.pdf
and that formed the basis of a lot of my questions a few months earlier.

There was similar work being done for system periods, which are a bit
simpler but require a side (history) table to be created. I was picking
people's brains about some aspects of system versioning to see if I could
help bringing that into this already very large patchset, but haven't yet
felt like I had done enough research to post it.

It is my hope that we can at least get the syntax for both application and
system versioning committed, even if it's just stubbed in with
not-yet-supported errors.

On Wed, Jan 5, 2022 at 8:07 AM Peter Eisentraut
<peter.eisentraut@enterprisedb.com> wrote:

This patch set looks very interesting.

Thank you for the review!

I'll work on your feedback but in the meantime here are replies to
your questions:

I'm confused about how to query tables based on application time
periods. Online, I see examples using AS OF, but in the SQL standard
I only see this used for system time, which we are not doing here.

Correct, the standard only gives it for system time. I think
application time is intended to be more "in user space" so it's fine
to use regular operators in your WHERE condition against the time
columns, whereas system time is more of a managed thing---automatic,
read-only, possibly stored in a separate table. Having a special
syntax cue lets the RDBMS know it needs to involve the historical
records.

validate_period(): Could use an explanatory comment. There are a
bunch of output arguments, and it's not clear what all of this is
supposed to do, and what "validating" is in this context.

I'm not too happy with that function, but a previous reviewer asked me
to factor out what was shared between the CREATE TABLE and ALTER TABLE
cases. It does some sanity checks on the columns you've chosen, and
along the way it collects info about those columns that we'll need
later. But yeah all those out parameters are pretty ugly. I'll see if
I can come up with a stronger abstraction for it, and at the very
least I'll add some comments.

MergeAttributes(): I would perhaps initially just prohibit inheritance
situations that involve periods on either side. (It should work for
partitioning, IMO, but that should be easier to arrange.)

Okay. I'm glad to hear you think partitioning won't be too hard. It is
one of the last things, but to me it's a bit intimidating.

I didn't follow why indexes would have periods, for example, the new
period field in IndexStmt. Is that explained anywhere?

When you create a primary key or a unique constraint (which are backed
by a unique index), you can give a period name to make it a temporal
constraint. We create the index first and then create the constraint
as a side-effect of that (e.g. index_create calls
index_constraint_create). The analysis phase generates an IndexStmt.
So I think this was mostly a way to pass the period info down to the
constraint. It probably doesn't actually need to be stored on pg_index
though. Maybe it does for index_concurrently_create_copy. I'll add
some comments, but if you think it's the wrong approach let me know.

While reading this patch I kept wondering whether it would be possible
to fold periods into pg_attribute, perhaps with negative attribute
numbers. Have you looked into something like that? No doubt it's
also complicated, but it might simplify some things, like the name
conflict checking.

Hmm, I thought that sort of thing would be frowned upon. :-) But also
it seems like periods really do have a bunch of details they need
beyond what other attributes have (e.g. the two source attributes, the
matching range type, the period type (application-vs-system), maybe
some extra things for table inheritance.

Also are you sure we aren't already using negative attnums somewhere
already? I thought I saw something like that.

Of course, the main problem in this patch is that for most uses it
requires btree_gist. I think we should consider moving that into
core, or at least the support for types that are most relevant to this
functionality, specifically the date/time types. Aside from user
convenience, this would also allow writing more realistic test cases.

I think this would be great too. How realistic do you think it is? I
figured since exclusion constraints are also pretty useless without
btree_gist, it wasn't asking too much to have people install the
extension, but still it'd be better if it were all built in.

src/backend/access/brin/brin_minmax_multi.c

These renaming changes seem unrelated (but still seem like a good
idea). Should they be progressed separately?

I can pull this out into a separate patch. I needed to do it because
when I added an `#include <rangetypes.h>` somewhere, these conflicted
with the range_{de,}serialize functions declared there.

I don't understand why a temporal primary key is required for doing
UPDATE FOR PORTION OF. I don't see this in the standard.

You're right, it's not in the standard. I'm doing that because
creating the PK is when we add the triggers to implement UPDATE FOR
PORTION OF. I thought it was acceptable since we also require a
PK/unique constraint as the referent of a foreign key. But we could
avoid it if I went back to the executor-based FOR PORTION OF
implementation, since that doesn't depend on triggers. What do you
think?

Also: I noticed recently that you can't use FOR PORTION OF against an
updatable view. I'm working on a new patch set to fix that. But the
main reason is this PK check. So that's maybe another reason to go
back to the executor implementation.

How to proceed. I suppose we could focus on committing 0001 and 0002
first.

That would be great! I don't think either is likely to conflict with
future system-time work.

Is there anything else you think we can do as
preparatory work to make the main patches more manageable?

I think it would be smart to have a rough plan for how this work will
be compatible with system-time support. Corey & I have talked about
that a lot, and In general they are orthogonal, but it would be nice
to have details written down somewhere.

Yours,
Paul

On 1/5/22 11:03 PM, Corey Huinker wrote:

There was similar work being done for system periods, which are a bit
simpler but require a side (history) table to be created.

This is false. SYSTEM_TIME periods do not need any kind of history.
This was one of the problems I had with Surafel's attempt because it was
confusing the period with SYSTEM VERSIONING. Versioning needs the
period but the inverse is not true.
--
Vik Fearing

On Thu, Jan 6, 2022 at 6:45 AM Vik Fearing <vik@postgresfriends.org> wrote:

On 1/5/22 11:03 PM, Corey Huinker wrote:

There was similar work being done for system periods, which are a bit
simpler but require a side (history) table to be created.

This is false. SYSTEM_TIME periods do not need any kind of history.
This was one of the problems I had with Surafel's attempt because it was
confusing the period with SYSTEM VERSIONING. Versioning needs the
period but the inverse is not true.

This is an interesting point. Syntactically, there are three different
things: the generated started/end columns, the period declaration, and
the WITH SYSTEM VERSIONING modifier to the table. You could declare a
system period without making the table versioned. Practically speaking
I don't know why you'd ever create a system period without a versioned
table (do you know of any uses Vik?), but perhaps we can exploit the
separation to add system periods in the same patch that adds
application periods.

The first two bits of syntax *are* tied together: you need columns
with GENERATED ALWAYS AS ROW START/END to declare the system period,
and less intuitively the standard says you can't use AS ROW START/END
unless those columns appear in a system period (2.e.v.2 under Part 2:
Foundation, 11.3 <table definition>). Personally I'd be willing to
ignore that latter requirement. For one thing, what does Postgres do
with the columns if you drop the period? Dropping the columns
altogether seems very harsh, so I guess you'd just remove the
GENERATED clause.

Another weird thing is that you don't (can't) say STORED for those
columns. But they are certainly stored somewhere. I would store the
values just like any other column (even if non-current rows get moved
to a separate table). Also then you don't have to do anything extra
when the GENERATED clause is dropped.

If we wanted to support system-time periods without building all of
system versioning, what would that look like? At first I thought it
would be a trivial addition to part-1 of the patch here, but the more
I think about it the more it seems to deserve its own patch.

One rule I think we should follow is that using a non-system-versioned
table (with a system period) should get you to the same place as using
a system-versioned table and then removing the system versioning. But
the standard says that dropping system versioning should automatically
drop all historical records (2 under Part 2: Foundation, 11.30 <drop
system versioning clause>). That actually makes sense though: when you
do DML we automatically update the start/end columns, but we don't
save copies of the previous data (and incidentally the end column will
always be the max value.) So there is a use case, albeit a thin one:
you get a Rails-like updated_at column that is maintained
automatically by your RDBMS. That is pretty easy, but I think I'd
still break it out into a separate patch. I'm happy to work on that as
something that builds on top of my part-1 patch here.

Yours,
Paul

But
the standard says that dropping system versioning should automatically
drop all historical records (2 under Part 2: Foundation, 11.30 <drop
system versioning clause>). That actually makes sense though: when you
do DML we automatically update the start/end columns, but we don't
save copies of the previous data (and incidentally the end column will
always be the max value.)

This is what I was referring to when I mentioned a side-table.
deleting history would be an O(1) operation. Any other
misunderstandings are all mine.