Re: WIP: System Versioned Temporal Table

On Sun, Sep 12, 2021 at 12:02 PM Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)com>
wrote:

> On Fri, 10 Sept 2021 at 19:30, Jaime Casanova
> <jcasanov(at)systemguards(dot)com(dot)ec> wrote:
> >
> > On Tue, Aug 10, 2021 at 01:20:14PM +0100, Simon Riggs wrote:
> > > On Wed, 14 Jul 2021 at 12:48, vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > >
> > > > The patch does not apply on Head anymore, could you rebase and post a
> > > > patch. I'm changing the status to "Waiting for Author".
> > >
> > > OK, so I've rebased the patch against current master to take it to v15.
> > >
> > > I've then worked on the patch some myself to make v16 (attached),
> > > adding these things:
> > >
> >
> > Hi Simon,
> >
> > This one doesn't apply nor compile anymore.
> > Can we expect a rebase soon?
>
> Hi Jaime,
>
> Sorry for not replying.
>
> Yes, I will rebase again to assist the design input I have requested.
> Please expect that on Sep 15.
>
> Cheers
>
> --
> Simon Riggs http://www.EnterpriseDB.com/
>
>
>
I've been interested in this patch, especially with how it will
interoperate with the work on application periods in
https://www.postgresql.org/message-id/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com
. I've written up a few observations and questions in that thread, and
wanted to do the same here, as the questions are a bit narrower but no less
interesting.

1. Much of what I have read about temporal tables seemed to imply or almost
assume that system temporal tables would be implemented as two actual
separate tables. Indeed, SQLServer appears to do it that way [1] with
syntax like

WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WebsiteUserInfoHistory));

Q 1.1. Was that implementation considered and if so, what made this
implementation more appealing?

2. The endtime column constraint which enforces GENERATED ALWAYS AS ROW END
seems like it would have appeal outside of system versioning, as a lot of
tables have a last_updated column, and it would be nice if it could handle
itself and not rely on fallible application programmers or require trigger
overhead.

Q 2.1. Is that something we could break out into its own patch?

3. It is possible to have bi-temporal tables (having both a system_time
period and a named application period) as described in [2], the specific
example was

CREATE TABLE Emp(
ENo INTEGER,
EStart DATE,
EEnd DATE,
EDept INTEGER,
PERIOD FOR EPeriod (EStart, EEnd),
Sys_start TIMESTAMP(12) GENERATED ALWAYS AS ROW START,
Sys_end TIMESTAMP(12) GENERATED ALWAYS AS ROW END,
EName VARCHAR(30),
PERIOD FOR SYSTEM_TIME(Sys_start, Sys_end),
PRIMARY KEY (ENo, EPeriod WITHOUT OVERLAPS),
FOREIGN KEY (Edept, PERIOD EPeriod) REFERENCES Dept (DNo, PERIOD DPeriod)
) WITH SYSTEM VERSIONING

What's interesting here is that in the case of a bitemporal table, it was
the application period that got the defined primary key. The paper went on
that only the _current_ rows of the table needed to be unique for, as it
wasn't possible to create rows with past system temporal values. This
sounds like a partial index to me, and luckily postgres can do referential
integrity on any unique index, not just primary keys. In light of the
assumption of a history side-table, I guess I shouldn't be surprised.

Q 3.1. Do you think that it would be possible to implement system
versioning with just a unique index?
Q 3.2. Are there any barriers to using a partial index as the hitch for a
foreign key? Would it be any different than the implied "and endtime =
'infinity'" that's already being done?

4. The choice of 'infinity' seemed like a good one initially - it's not
null so it can be used in a primary key, it's not some hackish magic date
like SQLServer's '9999-12-31 23:59:59.9999999'. However, it may not jibe as
well with application versioning, which is built very heavily upon range
types (and multirange types), and those ranges are capable of saying that a
record is valid for an unbounded amount of time in the future, that's
represented with NULL, not infinity. It could be awkward to have the system
endtime be infinity and the application period endtime be NULL.

Q 4.1. Do you have any thoughts about how to resolve this?

5. System versioning columns were indicated with additional columns in
pg_attribute.

Q 5.1. If you were to implement application versioning yourself, would you
just add additional columns to pg_attribute for those?

6. The current effort to implement application versioning creates an
INFORMATION_SCHEMA view called PERIODS. I wasn't aware of this one before
but there seems to be precedent for it existing.

Q 6.1. Would system versioning belong in such a view?

7. This is a trifle, but the documentation is inconsistent about starttime
vs StartTime and endtime vs EndTime.

8. Overall, I'm really excited about both of these efforts, and I'm looking
for ways to combine the efforts, perhaps starting with a patch that
implements the SQL syntax, but raises not-implemented errors, and each
effort could then build off of that.

[1] https://docs.microsoft.com/en-us/azure/azure-sql/temporal-tables
[2]
https://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeaturessql2011.pdf