Re: WIP: System Versioned Temporal Table - Mailing list pgsql-hackers
| From | Corey Huinker |
|---|---|
| Subject | Re: WIP: System Versioned Temporal Table |
| Date | |
| Msg-id | CADkLM=e5m4_4JBrX__V4J68rU9z8GpcsN3r1RNPcET-ZctG9Gg@mail.gmail.com Whole thread Raw |
| In response to | Re: WIP: System Versioned Temporal Table (Simon Riggs <simon.riggs@enterprisedb.com>) |
| Responses |
Re: WIP: System Versioned Temporal Table
(Corey Huinker <corey.huinker@gmail.com>)
|
| List | pgsql-hackers |
On Sun, Sep 12, 2021 at 12:02 PM Simon Riggs <simon.riggs@enterprisedb.com> wrote:
On Fri, 10 Sept 2021 at 19:30, Jaime Casanova
<jcasanov@systemguards.com.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@gmail.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
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?
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.
pgsql-hackers by date: