From: | Vik Fearing <vik(dot)fearing(at)2ndquadrant(dot)com> |
---|---|
To: | Surafel Temesgen <surafel3000(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: WIP: System Versioned Temporal Table |
Date: | 2019-10-23 20:02:50 |
Message-ID: | 3de42e96-45c3-faaa-af20-30f127724601@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 23/10/2019 17:56, Surafel Temesgen wrote:
>
> Hi all ,
>
> Temporal table is one of the main new features added in sql standard
> 2011. From that I will like to implement system versioned temporal
> table which allows to keep past and present data so old data can be
> queried.
>
Excellent! I've been wanting this feature for a long time now. We're
the last major database to not have it.
I tried my hand at doing it in core, but ended up having better success
at an extension: https://github.com/xocolatl/periods/
> Am propose to implement it like below
>
> CREATE
>
> In create table only one table is create and both historical and
> current data will be store in it. In order to make history and current
> data co-exist row end time column will be added implicitly to primary
> key. Regarding performance one can partition the table by row end time
> column order to make history data didn't slowed performance.
>
If we're going to be implicitly adding stuff to the PK, we also need to
add that stuff to the other unique constraints, no? And I think it
would be better to add both the start and the end column to these keys.
Most of the temporal queries will be accessing both.
> INSERT
>
> In insert row start time column and row end time column behave like a
> kind of generated stored column except they store current transaction
> time and highest value supported by the data type which is +infinity
> respectively.
>
You're forcing these columns to be timestamp without time zone. If
you're going to force a datatype here, it should absolutely be timestamp
with time zone. However, I would like to see it handle both kinds of
timestamps as well as a simple date.
> DELETE and UPDATE
>
> The old data is inserted with row end time column seated to current
> transaction time
>
I don't see any error handling for transaction anomalies. In READ
COMMITTED, you can easily end up with a case where the end time comes
before the start time. I don't even see anything constraining start
time to be strictly inferior to the end time. Such a constraint will be
necessary for application-time periods (which your patch doesn't address
at all but that's okay).
> SELECT
>
> If the query didn’t contain a filter condition that include system
> time column, a filter condition will be added in early optimization
> that filter history data.
>
> Attached is WIP patch that implemented just the above and done on top
> of commit b8e19b932a99a7eb5a. Temporal clause didn’t implemented yet
> so one can use regular filter condition for the time being
>
> NOTE: I implement sql standard syntax except it is PERIOD FOR SYSTEM
> TIME rather than PERIOD FOR SYSTEM_TIME in CREATE TABLE statement and
> system time is not selected unless explicitly asked
>
Why aren't you following the standard syntax here?
> Any enlightenment?
>
There are quite a lot of typos and other things that aren't written "the
Postgres way". But before I comment on any of that, I'd like to see the
features be implemented correctly according to the SQL standard.
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2019-10-23 21:44:34 | Re: Transparent Data Encryption (TDE) and encrypted files |
Previous Message | Stuart McGraw | 2019-10-23 19:00:47 | Re: jsonb_set() strictness considered harmful to data |