Re: WIP: System Versioned Temporal Table - Mailing list pgsql-hackers

On Fri, Jan 8, 2021 at 4:50 PM Ryan Lambert <ryan@rustprooflabs.com> wrote:
>
> On Fri, Jan 8, 2021 at 5:34 AM Simon Riggs <simon.riggs@enterprisedb.com> wrote:
>>
>> On Fri, Jan 8, 2021 at 7:34 AM Simon Riggs <simon.riggs@enterprisedb.com> wrote:
>> >
>> > On Fri, Jan 8, 2021 at 7:13 AM Simon Riggs <simon.riggs@enterprisedb.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