Re: WIP: System Versioned Temporal Table

On Fri, Jan 8, 2021 at 11:38 AM Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)com>
wrote:

> On Fri, Jan 8, 2021 at 4:50 PM Ryan Lambert <ryan(at)rustprooflabs(dot)com>
> wrote:
> >
> > On Fri, Jan 8, 2021 at 5:34 AM Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)com>
> wrote:
> >>
> >> On Fri, Jan 8, 2021 at 7:34 AM Simon Riggs <
> simon(dot)riggs(at)enterprisedb(dot)com> wrote:
> >> >
> >> > On Fri, Jan 8, 2021 at 7:13 AM Simon Riggs <
> simon(dot)riggs(at)enterprisedb(dot)com> wrote:
> >> >
> >> > > I've minimally rebased the patch to current head so that it compiles
> >> > > and passes current make check.
> >> >
> >> > Full version attached
> >>
> >> New version attached with improved error messages, some additional
> >> docs and a review of tests.
> >>
> >
> > The v10 patch fails to make on the current master branch (15b824da).
> Error:
>
> Updated v11 with additional docs and some rewording of messages/tests
> to use "system versioning" correctly.
>
> No changes on the points previously raised.
>
> --
> Simon Riggs http://www.EnterpriseDB.com/

Thank you! The v11 applies and installs. I tried a simple test,
unfortunately it appears the versioning is not working. The initial value
is not preserved through an update and a new row does not appear to be
created.

CREATE TABLE t
(
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
v BIGINT NOT NULL
)
WITH SYSTEM VERSIONING
;

Verify start/end time columns created.

t=# \d t
Table "public.t"
Column | Type | Collation | Nullable |
Default
-----------+--------------------------+-----------+----------+----------------------------------
id | bigint | | not null | generated by
default as identity
v | bigint | | not null |
StartTime | timestamp with time zone | | not null | generated
always as row start
EndTime | timestamp with time zone | | not null | generated
always as row end
Indexes:
"t_pkey" PRIMARY KEY, btree (id, "EndTime")

Add a row and check the timestamps set as expected.

INSERT INTO t (v) VALUES (1);

SELECT * FROM t;
id | v | StartTime | EndTime
----+---+-------------------------------+----------
1 | 1 | 2021-01-08 20:56:20.848097+00 | infinity

Update the row.

UPDATE t SET v = -1;

The value for v updated but StartTime is the same.

SELECT * FROM t;
id | v | StartTime | EndTime
----+----+-------------------------------+----------
1 | -1 | 2021-01-08 20:56:20.848097+00 | infinity

Querying the table for all versions only returns the single updated row (v
= -1) with the original row StartTime. The original value has disappeared
entirely it seems.

SELECT * FROM t
FOR SYSTEM_TIME FROM '-infinity' TO 'infinity';

I also created a non-versioned table and later added the columns using
ALTER TABLE and encountered the same behavior.

Ryan Lambert