arkhipov / temporal_tables Goto Github PK

I'm looking for a temporal solution that will work on AWS.
AWS does not support this extension.
Can I use the plpgsql versioning function directly?
regards
Simon

Hi, first of all thanks for the great extension!

Are there any plans for releasing of the 1.1.0 version?
We are currently using the master version with manual instalation (without any issues).
But manual installation is not comfortable as PGXN..

Hello,

I am trying to install the temporal_tables extension in Windows 10 Professional. I am using PostgreSQL 11.7 and I have been searching high and low for a way to do this in Windows but all I've found have been installation instructions for Linux.

Thanks for your assistance,

Jay2010

During our research for the memory “leak“ issue (#24) we found out, that „hash_entry“ will not be found in function “insert_history_row” in some cases, leading to refilling “hash_entry” on every call of the function.

This issue occurs on tables with e.g. defined constraints or default values. In function “insert_history_row” the check

                if (hash_entry->natts == -1 ||
                        RelationGetRelid(history_relation) != hash_entry->history_relid ||
                        !equalTupleDescs(tupdesc, hash_entry->tupdesc) ||
                        !equalTupleDescs(history_tupdesc, hash_entry->history_tupdesc))

always evaluates to true as equalTupleDescs(*tupdesc, hash_entry->tupdesc) and equalTupleDescs(history_tupdesc, hash_entry->history_tupdesc) evaluate to false. In our cases this was because in function equalTupleDesc the check if (attr1->attnotnull != attr2->attnotnull) evaluates to false as “attr2->attnotnull” is 0, even for columns with “NOT NULL“ set.

As far as we could find out the cause seem to be the way the tuple description is copied into the “hash_entry” in function “fill_versioning_hash_entry”: “CreateTupleDescCopy” does explicitly not copy default values and constraints. Using “CreateTupleDescCopyConstr” instead does. Using “CreateTupleDescCopy” instead fix this for us:

diff --git a/versioning.c b/versioning.c
index 2497c2f..41ffcdd 100644
--- a/versioning.c
+++ b/versioning.c
@@ -561,8 +561,8 @@ fill_versioning_hash_entry(VersioningHashEntry *hash_entry,
                oldcontext = MemoryContextSwitchTo(TopMemoryContext);
 
                hash_entry->history_relid = RelationGetRelid(history_relation);
-               hash_entry->tupdesc = CreateTupleDescCopy(tupdesc);
-               hash_entry->history_tupdesc = CreateTupleDescCopy(history_tupdesc);
+               hash_entry->tupdesc = CreateTupleDescCopyConstr(tupdesc);
+               hash_entry->history_tupdesc = CreateTupleDescCopyConstr(history_tupdesc);
 
                hash_entry->attnums = palloc(natts * sizeof(int));
                memcpy(hash_entry->attnums, attnums, natts * sizeof(int));

Running PG13 under CentOS 7 in a docker container and using pgmodeler for my database modeling. Since I am new to temporal_tables I am experimenting with the employees and employees_history example found on the web.

Pgmodeler has worked extremely well for my other database modeling but I have run into a problem with my temporal tables and am not sure where the problem lies. Using pgmodeler I add SQL code to a table to create a history table and this SQL code is run after creating the table:

CREATE TABLE test.employees_history (LIKE employees EXCLUDING INDEXES EXCLUDING CONSTRAINTS);

test is the schema I am using for this example and is also the default for my model. The above example creates test.employees_history based on the employees table in the test schema.

In pgmodeler I have also added a versioning_trigger to the employee table accepting sys_period, employees_history and true as the three arguments (pgmodeler does not seem to accept a schema designation added to the second argument.)

Database creation and table creation runs fine and I can add rows to the test.employees as expected. However, when I try to UPDATE a row in the employees table there is an error message:

UPDATE test.employees SET salary = 11200 WHERE name = 'Bernhard Marx';
ERROR: relation "employees_history" does not exist

The table test.employees_history does exist and since this is the first update of a row in the table employees employees_history is empty.

As far as I can tell, there could be two reasons for the error message. The first one is that the versioning_trigger does not handle schemas, the second one is that there is an issue in pgmodeler.

Thoughts welcome!

Below is output from postgres:

training=# \dS test.employees
Table "test.employees"
Column | Type | Collation | Nullable | Default
------------+--------------------------+-----------+----------+---------
name | text | | not null |
department | text | | |
salary | numeric(20,2) | | |
created_at | timestamp with time zone | | not null | now()
sys_period | tstzrange | | not null |
Indexes:
"employees_pk" PRIMARY KEY, btree (name)
Triggers:
versioning_trigger BEFORE INSERT OR DELETE OR UPDATE ON test.employees FOR EACH ROW EXECUTE FUNCTION versioning('sys_period', 'employees_history', 'true')

Does the versioning_trigger above look correct bearing in mind that I am not in the public schema but in the test schema?

When I create trigger as in your README it works just fine, I update a row and see result in the history table. Trigger looks like this:

create trigger test_versioning_trigger1
before update on testtable
for each row execute procedure versioning('sys_valid', 'testtable_history', true);

But when I try to execute versioning function within another function I get an error:

Kernel error: ERROR:  function versioning(unknown, unknown, boolean) does not exist

Procedure and trigger look like this:

create or replace function test_versioning() returns trigger as
$$
begin
    return versioning('sys_valid', 'testtable_history', true);
end;
$$
language plpgsql;

create trigger test_versioning_trigger2
before update on testtable
for each row execute procedure test_versioning();

I suppose the problem is not in the temporal_tables extension but I'm new to plpgsql and would appreciate any help.

I have a table containing meta-information about network devices. One such information is a timestamp when the device was last seen on the network. This value is updated regularly, and as such would trigger a new history entry every time the device is polled even if nothing else has changed.

At the moment I am working on implementing a history of changes to these devices and came across temporal tables which does exactly what I want.

Unfortunately the issue explained above will create plenty of useless revisions.

I could move the column from the DB into a separate table and reference it, but that would become quite cumbersome as the value is used as a filter in almost all application query.

It would help a lot if I could simply ignore that column from the versioning process.

Thanks for the extension, it looks to be exactly what we're looking for. I'm having some problems following the install instructions though, although it may just be that make installcheck doesn't work until after you've run make install.

make works fine, but I get failures on make installcheck.

+ ERROR:  could not open extension control file "/usr/local/Cellar/postgresql/9.4.1/share/postgresql/extension/temporal_tables.control": No such file or directory

So I copied files into postgresql/extension/ until it was happy (eventually cp temporal_tables* /usr/local/Cellar/postgresql/9.4.1/share/postgresql/extension/) and now have the following error:

+ ERROR:  could not access file "$libdir/temporal_tables": No such file or directory

However if I run make install before make installcheck then all the tests pass.

http://pgxnclient.projects.pgfoundry.org/ gives 403 forbidden

Hey there,

I'm having some problems getting this to work on 9.3.

I seem to be able to run make and make install, but make installcheck and creating the extension errors out.

postgres=# create extension temporal_tables;
ERROR:  could not load library 
"/usr/lib/postgresql/9.3/lib/temporal_tables.so":
/usr/lib/postgresql/9.3/lib/temporal_tables.so: undefined symbol:       
HeapTupleHeaderGetXmin

Here's my pg_config:

BINDIR = /usr/lib/postgresql/9.3/bin
DOCDIR = /usr/share/doc/postgresql-doc-9.3
HTMLDIR = /usr/share/doc/postgresql-doc-9.3
INCLUDEDIR = /usr/include/postgresql
PKGINCLUDEDIR = /usr/include/postgresql
INCLUDEDIR-SERVER = /usr/include/postgresql/9.3/server
LIBDIR = /usr/lib
PKGLIBDIR = /usr/lib/postgresql/9.3/lib
LOCALEDIR = /usr/share/locale
MANDIR = /usr/share/postgresql/9.3/man
SHAREDIR = /usr/share/postgresql/9.3
SYSCONFDIR = /etc/postgresql-common
PGXS = /usr/lib/postgresql/9.3/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--with-tcl' '--with-perl' '--with-python' '--with-pam' '--with-openssl' '--with-libxml' '--with-libxslt' '--with-tclconfig=/usr/lib/tcl8.    5' '--with-tkconfig=/usr/lib/tk8.5' '--with-includes=/usr/include/tcl8.5' 'PYTHON=/usr/bin/python' '--mandir=/usr/share/postgresql/9.3/man' '--    docdir=/usr/share/doc/postgresql-doc-9.3' '--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/' '--datadir=/usr/share/postgresql/9.3' '--    bindir=/usr/lib/postgresql/9.3/bin' '--libdir=/usr/lib/' '--libexecdir=/usr/lib/postgresql/' '--includedir=/usr/include/postgresql/' '--enable-nls' '-    -enable-integer-datetimes' '--enable-thread-safety' '--enable-debug' '--disable-rpath' '--with-ossp-uuid' '--with-gnu-ld' '--with-pgport=5432' '--with    -system-tzdata=/usr/share/zoneinfo' 'CFLAGS=-g -O2 -fstack-protector --param=ssp-buffer-size=4 -Wformat -Wformat-security -Werror=format-security -    fPIC -pie -I/usr/include/mit-krb5 -DLINUX_OOM_ADJ=0' 'LDFLAGS=-Wl,-Bsymbolic-functions -Wl,-z,relro -Wl,-z,now -Wl,--as-needed -L/usr/lib/mit-krb5 -    L/usr/lib/x86_64-linux-gnu/mit-krb5' '--with-krb5' '--with-gssapi' '--with-ldap' 'CPPFLAGS=-D_FORTIFY_SOURCE=2'
CC = gcc
CPPFLAGS = -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include/tcl8.5
CFLAGS = -g -O2 -fstack-protector --param=ssp-buffer-size=4 -Wformat -Wformat-security -Werror=format-security -fPIC -pie -I/usr/include/mit-krb5 -    DLINUX_OOM_ADJ=0 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security     -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g
CFLAGS_SL = -fpic
LDFLAGS = -L../../../src/common -Wl,-Bsymbolic-functions -Wl,-z,relro -Wl,-z,now -Wl,--as-needed -L/usr/lib/mit-krb5 -L/usr/lib/x86_64-linux-gnu/mit-    krb5 -L/usr/lib/x86_64-linux-gnu -Wl,--as-needed
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgport -lpgcommon -lxslt -lxml2 -lpam -lssl -lcrypto -lkrb5 -lcom_err -lgssapi_krb5 -lz -ledit -lcrypt -ldl -lm
VERSION = PostgreSQL 9.3.2    

Any help would be appreciated

ERROR: incompatible library "/usr/local/lib/postgresql/temporal_tables.so": version mismatch
DETAIL: Server is version 14, library is version 13.

Note that the brew install forcefully install version 14 on your system.

Just tried to compile the package under the new release, and ran into compilation problems.

Within versioning.c:
At least one new include will be needed, utils/rangetypes.h. There are some issues with FormData_pg_attribute and Form_pg_attribute usage as well. If I get some time, I'll see if I can figure out the remainder of the issues and submit a pull request.

This is related to #9 that you have already answered.

For me I wonder if we can (or even should) use the 'statement start time' rather than the 'transaction start time' for get_system_time().

The thinking behind this is that at READ_COMMITTED isolation level the update statement at T4:

    UPDATE employees SET salary = 6800 WHERE name = 'Lenina Crowne';

... does effectively execute as at 'statement start time' (it sees the committed insert from Transaction B). To me I think there is an arguement that the time range for 'Lenina Crowne', 7000 can then be [T2,T4).

If the isolation level was SERIALISABLE then we should use the transaction start time but also Transaction A will not see the insert from Transaction B in that case. To me I think at READ_COMMITTED it should be [T2,T4) by using the 'statement start time' and then we don't need to use the time delta adjustment.

Any thoughts about that? Is the isolation level taken into account (I have not seen that in the code)?

Cheers, Rob.

after make and make install, trying to create extension:

# create extension temporal_tables ;
psql: ERROR:  could not load library "/usr/lib/postgresql/12/lib/temporal_tables.so": /usr/lib/postgresql/12/lib/temporal_tables.so: undefined symbol: heap_openrv

Build on windows for pg 13 or 14 fails
msbuild /p:pgversion=13 /p:configuration=Release /p:platform=x64

C:\temporal_tables-1.2.0\temporal_tables-1.2.0>msbuild /p:pgversion=13 /p:configuration=Release /p:platform=x64

Microsoft (R) Build Engine version 17.0.0+c9eb9dd64 for .NET Framework
Copyright (C) Microsoft Corporation. All rights reserved.
Building the projects in this solution one at a time. To enable parallel build, please add the "-m" switch.
Build started 13.12.2021 20.27.37.
Project "C:\temporal_tables-1.2.0\temporal_tables.sln" on node 1 (default targets).
ValidateSolutionConfiguration:
  Building solution configuration "Release|x64".
Project "C:\temporal_tables-1.2.0\temporal_tables.sln" (1) is building "C:\temporal_tables-1.2.0\temporal_tables.vcxproj" (2)
on node 1 (default targets).
InitializeBuildStatus:
  Touching "x64\Release\temporal_tables.tlog\unsuccessfulbuild".
ClCompile:
  C:\Program Files\Microsoft Visual Studio\2022\Community\VC\Tools\MSVC\14.30.30705\bin\HostX64\x64\CL.exe /c /Zi /nologo /W3 /WX- /diagnostics:column /sdl /O2 /Oi /GL /D WIN32 /D _WINDLL /D _UNICODE /D UNICODE /Gm- /MD /GS /Gy /fp:precise /Zc:wchar_t
  /Zc:forScope /Zc:inline /Fo"x64\Release\\" /Fd"x64\Release\vc143.pdb" /external:W3 /Gd /TC /FC /errorReport:queue versioning.c
  versioning.c
C:\Program Files\PostgreSQL\13\include\server\nodes\pg_list.h(314,11): warning C4244: 'return': conversion from '__int64' to 'int', possible loss of data [C:\temporal_tables-1.2.0\temporal_
tables.vcxproj]
C:\temporal_tables-1.2.0\versioning.c(249,49): error C2440: '=': cannot convert from 'FormData_pg_attribute' to 'Form_pg_attribute' [C:\temporal_tables-1.2.0\temporal_tables.vcxproj]
C:\temporal_tables-1.2.0\versioning.c(502,27): error C2440: '=': cannot convert from 'FormData_pg_attribute' to 'Form_pg_attribute' [C:\temporal_tables-1.2.0\temporal_tables.vcxproj]
C:\temporal_tables-1.2.0\versioning.c(514,60): error C2440: '=': cannot convert from 'FormData_pg_attribute' to 'Form_pg_attribute' [C:\temporal_tables-1.2.0\temporal_tables.vcxproj]
C:\temporal_tables-1.2.0\versioning.c(614,32): warning C4013: 'heap_openrv' undefined; assuming extern returning int [C:\temporal_tables-1.2.0\temporal_tables.vcxproj]
C:\temporal_tables-1.2.0\versioning.c(614,56): warning C4047: '=': 'Relation' differs in levels of indirection from 'int' [C:\temporal_tables-1.2.0\temporal_tables.vcxproj]
C:\temporal_tables-1.2.0\versioning.c(727,16): warning C4013: 'relation_close' undefined; assuming extern returning int [C:\temporal_tables-1.2.0\temporal_tables.vcxproj]
C:\temporal_tables-1.2.0\versioning.c(762,35): warning C4013: 'DatumGetRangeType' undefined; assuming extern returning int [C:\temporal_tables-1.2.0\temporal_tables.vcxproj]
C:\temporal_tables-1.2.0\versioning.c(762,42): warning C4047: '=': 'RangeType *' differs in levels of indirection from 'int' [C:\temporal_tables-1.2.0\temporal_tables.vcxproj]
C:\temporal_tables-1.2.0\versioning.c(880,41): warning C4013: 'RangeTypeGetDatum' undefined; assuming extern returning int [C:\temporal_tables-1.2.0\temporal_tables.vcxproj]
Done Building Project "C:\temporal_tables-1.2.0\temporal_tables.vcxproj" (default targets) -- FAILED.

Build FAILED.
"C:\temporal_tables-1.2.0\temporal_tables.sln" (default target) (1) ->
"C:\temporal_tables-1.2.0\temporal_tables.vcxproj" (default target) (2) ->
(ClCompile target) ->
  C:\Program Files\PostgreSQL\13\include\server\nodes\pg_list.h(314,11): warning C4244: 'return': conversion from '__int64' to 'int', possible loss of data [C:\temporal_tables-1.2.0\tempora
l_tables.vcxproj]
  C:\temporal_tables-1.2.0\versioning.c(614,32): warning C4013: 'heap_openrv' undefined; assuming extern returning int [C:\tem
poral_tables-1.2.0\temporal_tables.vcxproj]
  C:\temporal_tables-1.2.0\versioning.c(614,56): warning C4047: '=': 'Relation' differs in levels of indirection from 'int' [C:\temporal_tables-1.2.0\temporal_tables.vcxproj]
  C:\temporal_tables-1.2.0\versioning.c(727,16): warning C4013: 'relation_close' undefined; assuming extern returning int [C:\temporal_tables-1.2.0\temporal_tables.vcxproj]
  C:\temporal_tables-1.2.0\versioning.c(762,35): warning C4013: 'DatumGetRangeType' undefined; assuming extern returning int [C:\temporal_tables-1.2.0\temporal_tables.vcxproj]
  C:\temporal_tables-1.2.0\versioning.c(762,42): warning C4047: '=': 'RangeType *' differs in levels of indirection from 'int' [C:\temporal_tables-1.2.0\temporal_tables.vcxproj]
  C:\temporal_tables-1.2.0\versioning.c(880,41): warning C4013: 'RangeTypeGetDatum' undefined; assuming extern returning int [C:\temporal_tables-1.2.0\temporal_tables.vcxproj]
"C:\temporal_tables-1.2.0\temporal_tables.sln" (default target) (1) ->
"C:\temporal_tables-1.2.0\temporal_tables.vcxproj" (default target) (2) ->
(ClCompile target) ->
  C:\temporal_tables-1.2.0\versioning.c(249,49): error C2440: '=': cannot convert from 'FormData_pg_attribute' to 'Form_pg_attribute' [C:\temporal_tables-1.2.0\temporal_tables.vcxproj]
  C:\temporal_tables-1.2.0\versioning.c(502,27): error C2440: '=': cannot convert from 'FormData_pg_attribute' to 'Form_pg_attribute' [C:\temporal_tables-1.2.0\temporal_tables.vcxproj]
  C:\temporal_tables-1.2.0\versioning.c(514,60): error C2440: '=': cannot convert from 'FormData_pg_attribute' to 'Form_pg_attribute' [C:\temporal_tables-1.2.0\temporal_tables.vcxproj]

    7 Warning(s)
    3 Error(s)

And tried using verion 10 of dlls reports
CREATE EXTENSION temporal_tables;
ERROR: could not load library "C:/Program Files/PostgreSQL/13/lib/temporal_tables.dll": The specified procedure could not be found.
Any idea what we need to do ?
Thank you

I have compiled the extension successfully on a centos7 with postgres-95.

When I try to create the extension, I get the following error:

postgres=# create extension temporal_tables;
FEHLER: konnte Bibliothek „/usr/pgsql-9.5/lib/temporal_tables.so“ nicht laden: /usr/pgsql-9.5/lib/temporal_tables.so: undefined symbol: oid_hash

OS:
Linux localhost.localdomain 3.10.0-327.el7.x86_64 #1 SMP Thu Nov 19 22:10:57 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

postgres:
PostgreSQL 9.5.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit

Best Regards,

jan

I suggest to use relation OID instead of a name while calling versioning function from a trigger. This way one can rename history table without a need to recreate a trigger with an updated call to versioning.

Maybe implement versioning2 that takes oid, casts it to regclass and calls standard versioning? Or, actually, other way around replacing heap_openrv with heap_open and alike?

P.S. I do some nasty things with PG and I deal with a bunch of versioned tables that might get renamed.

This is a clarification request (perhaps for readme or something) for best management practice on how to add archival note. For example, this employee was moved to employees_history because he was fired (or resigned?), or salary was changed because of rough days, etc.

Are there any plans for releasing of the 1.3.0 version with PG 11, 12 support, please?

I tripped up a number of times trying to install this on my AWS instance and thought this might help:
Environment: Centos 6 - AWS EC2 - Postgres 95 - Python 2.7
Installing this PG Extension w/o pgxn
//no extra sudos, nothing different than this:

$ cd /tmp
$ mkdir /tmp/temp_tables
$ cd /tmp/temp_tables
$ git clone https://github.com/arkhipov/temporal_tables.git
$ make //don't do in sudo)
$ sudo make install
$ make installcheck //don't do in sudo) //http://stackoverflow.com/questions/37661605/how-to-set-user-for-installcheck-in-the-makefile-of-a-postgres-extension
$ PGUSER=postgres make installcheck
$ su - postgres //login as postgres (pg root)
$ psql postgresql //connect to db named postgres
$ CREATE EXTENSION temporal_tables; //create extension
for usage see: //https://github.com/arkhipov/temporal_tables

When using different set_system_time within one transaction, no rows are written to the history table.
Example:

=> begin transaction
=*> select set_system_time('2022-01-01');
=*> insert into test (id) values (1);
=*> select set_system_time('2022-01-02');
=*> delete from test where id=1;
=*> select * from test;
(0 rows)
=*> select * from histtest;
(0 rows)

I am trying to use this in pg10 beta3 from Docker, but I cannot insert any row into the temporal table that had trigger inserted.

Back to PostgreSQL 9.6 and it works fine.

Any possible reason?

Hi

In the very last part of the readme one can see this:
BEFORE DELETE ON employees_history

but is it not so, that one never deletes from the history table ?

The trigger should be triggered when someone deletes a row from Employee and when that happenes, then a row should be written in the History table, where the username who deleted the row should also exist?

Hi. I'm new to this extension and PostgreSQL in general. I know a little about temporal tables from SQL Server and have been investigating how viable it would be to port my database over.

From this tutorial, I know the standard syntax isn't supported:

SELECT * FROM subscriptions AS OF SYSTEM TIME '2014-01-10' WHERE id = 1;

and instead we can use a view:

SELECT * FROM subscriptions_with_history
WHERE id = 1 AND sys_period @> '2015-01-10'::timestamptz

This is fine for a single table, but if I were joining to another temporal table I'd have to repeat the sys_period clause for every temporal view. I'm wondering if there is a more elegant way to do this? Perhaps a view like:

CREATE VIEW subscriptions_at_tqt AS
SELECT * FROM subscriptions_with_history WHERE sys_period @> get_temporal_query_time()

Where get_temporal_query_time would have an equivalent set_temporal_query_time. This would mean I could do something like:

set_temporal_query_time('2017-11-01 10:00:00');

SELECT * FROM subscriptions_at_tqt
INNER JOIN product_at_tqt ON (...) 
INNER JOIN publisher_at_tqt  ON (...)

As far as I can see, this is similar to the existing set_system_time utility, but for querying rather than versioning. Does this idea have legs?

I'd much prefer to say that CREATE TABLE employees_history () INHERITS (employees), but this produces unexpected results. Is there any reason why this doesn't work as expected?

For example:

DROP TABLE IF EXISTS "test_history";
DROP TABLE IF EXISTS "test";
DROP TRIGGER IF EXISTS "versioning_trigger" ON "test";

CREATE TABLE "test" (
  seq INT,
  validity tstzrange
);
CREATE TABLE "test_history" () INHERITS ("test");
CREATE TRIGGER "versioning_trigger"
  BEFORE INSERT OR UPDATE OR DELETE ON "test"
  FOR EACH ROW EXECUTE PROCEDURE versioning('validity', 'test_history', true);

INSERT INTO "test" VALUES (1);
UPDATE "test" SET seq = 2;
UPDATE "test" SET seq = 3;
SELECT * FROM "test_history";
 seq |                             validity
-----+-------------------------------------------------------------------
   1 | ["2015-04-15 11:18:44.971061+10","2015-04-15 11:18:44.987991+10")
   2 | ["2015-04-15 11:18:44.987991+10","2015-04-15 11:18:44.999616+10")
   1 | ["2015-04-15 11:24:12.907224+10","2015-04-15 11:24:12.908114+10")
   2 | ["2015-04-15 11:24:12.908114+10","2015-04-15 11:24:12.909926+10")
(4 rows)

If I change this to CREATE TABLE "test" () INHERITS ("test_history") then this works as expected:

SELECT * FROM "test_history";
 seq |                             validity
-----+-------------------------------------------------------------------
   1 | ["2015-04-15 11:29:12.386408+10","2015-04-15 11:29:12.387281+10")
   2 | ["2015-04-15 11:29:12.387281+10","2015-04-15 11:29:12.389392+10")
   3 | ["2015-04-15 11:29:12.389392+10",)
(3 rows)

As of SQL 2011, there is an official ISO SQL standard for temporal tables. See the description here: https://sigmodrecord.org/publications/sigmodRecord/1209/pdfs/07.industry.kulkarni.pdf

Postgres has an open commitfest for implementing system versioning https://commitfest.postgresql.org/34/2316/. However, they are struggling to decide upon the best implementation. Would the contributors/maintainers of this repo maybe be able to reach out on that CF email thread explaining the design decisions for this plugin? That might help them come up with the best way of doing things, even if there is no contribution to the code.

If this plugin is planned for continued development, I'd also suggest giving it a SQL11-compliant syntax. However, it seems development might be stalled, so maybe it's best to go all in in a PG adoption of temporal tables, as in the commitfest.

thanks.

First, a big thank you for this extension! It has simplified the concept a lot over a custom PL/pgSQL setup. Enough so that even I can successfully use it :)

Now though I have an instance where I want to load data from various existing tables (essentially snapshots/partitions in time) with an existing timestamp, which I'd like to preserve in sys_period.
I managed to do this for the initial (oldest) data-load by disabling the trigger around the COPY.

ALTER TABLE cache DISABLE TRIGGER versioning_trigger;
\copy
ALTER TABLE cache ENABLE TRIGGER versioning_trigger;

and casting the existing timestamp into the sys_period timerange:

QUERY='SELECT [...], tstzrange(my_timestamp::timestamptz, NULL) AS sys_period FROM oldcache'
PASTE='cache ([...], sys_period)'

${psql} -q -d ${SOURCEDB} -c "\timing off" -c "\copy (${QUERY}) TO STDOUT;" | \
	${psql} -d ${TARGETDB} -c "ALTER TABLE cache DISABLE TRIGGER versioning_trigger;" -c "\copy ${PASTE} FROM STDIN;" -c "ALTER TABLE cache ENABLE TRIGGER versioning_trigger;"

This worked beautifully, but now that I need to do the actual history revision UPDATEs, I can no longer use dumb COPY and need the trigger (or a workaround).

Since these are 50+GB per existing table/dump, I dislike the idea of creating an sql dump where every INSERT must be prefixed by a set_system_time call, which I then first need to extract from the table's timestamp column:

SELECT set_system_time('1985-08-08 06:42:00+08'); INSERT INTO [...];
SELECT set_system_time('1985-08-08 06:43:00+08'); INSERT INTO [...];
SELECT set_system_time('1985-08-08 06:44:00+08'); INSERT INTO [...];

Is there any possibility to accept the lower bound from a sys_period tstzrange column if given in an INSERT/UPDATE/DELETE?
E.g. if I passed sys_period as such

INSERT INTO x ([...], tstzrange(my_time::timestamptz, NULL::timestamptz) AS sys_period);
UPDATE x SET sys_perid = tstzrange(my_time::timestamptz, NULL::timestamptz) WHERE [...];

could the trigger be made to use this lower bound instead of ignoring the column and instead of the date given by set_system_time()?

This would make bulk-loading the data through a dumb COPY, like my pg dumps, much easier (with an upsert trigger), so long as I get the historical order right.
In my case it would be very hard to build up a *_history table (as clarkdave explains) where I'd have to put on my forensics hat and manually stitch up the data to get the tstzrange's right.

We observed, that every update on a row of a temporal enables tables leads to ~15KB additional memory usage of the processes for a connection.

Our researches showed that this memory usage is caused by lookup_versioning_hash_entry because already created entries are not found.

Steps we did to analyse:

• Debugging showed, that all calls of function "lookup_versioning_hash_entry" entered block “if (!*found)”, even the second call in a row for the same table.
• Uncommenting “memset” command showed:
  • All fields of “entry” (except relid) are 0/NULL.
  • Without "memset" all non first calls of function "lookup_versioning_hash_entry" skipped block “if (!*found)” (as intended).
  • Memory usage was constant (wanted behaviour)
• Reactivating “memset” command and subsequent setting entry->relid = relid;
  • Same behaviour as seen with uncommenting “memset” (s. above).

We don't really understand

• why the first call of “hash_search” for a table returns an initialized struct “entry” with "relid" set and all other fields filled with null values.
• why setting "entry->relid" to null leads to not finding the entry on subsequent calls of “hash_search”.

But ensuring "entry->relid" is set after the "memset" solves the issue for us.

diff --git a/versioning.c b/versioning.c
index 2497c2f..0599530 100644
--- a/versioning.c
+++ b/versioning.c
@@ -1080,6 +1080,7 @@ lookup_versioning_hash_entry(Oid relid,
        if (!*found)
        {
                memset(entry, 0, sizeof(VersioningHashEntry));
+               entry->relid = relid;
 
                /* Mark a newly created entry invalid. */
                entry->natts = -1;

Hey,

I'd like history table to contain all the data, not only modified rows. Would it be possible to add another parameter to temporal_tables, so even an INSERT to original table creates a row in history table?

Postgres version: 11

I tried both ways to install the temporal tables:

$ git clone https://github.com/arkhipov/temporal_tables
$ cd temporal_tables
$ make
$ make installcheck
$ make install

and

pgxn install temporal_tables

But I am getting following error:

/temporal_tables# make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -g -O2 -fdebug-prefix-map=/build/postgresql-11-f8qi7Y/postgresql-11-11~beta1=. -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -I. -I./ -I/usr/include/postgresql/11/server -I/usr/include/postgresql/internal  -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include/mit-krb5  -c -o versioning.o versioning.c
versioning.c: In function ‘versioning’:
versioning.c:249:14: error: incompatible types when assigning to type ‘Form_pg_attribute {aka struct FormData_pg_attribute *}’ from type ‘FormData_pg_attribute {aka struct FormData_pg_attribute}’
  period_attr = tupdesc->attrs[period_attnum - 1];
              ^
versioning.c: In function ‘fill_versioning_hash_entry’:
versioning.c:502:8: error: incompatible types when assigning to type ‘Form_pg_attribute {aka struct FormData_pg_attribute *}’ from type ‘FormData_pg_attribute {aka struct FormData_pg_attribute}’
   attr = tupdesc->attrs[i];
        ^
versioning.c:514:16: error: incompatible types when assigning to type ‘Form_pg_attribute {aka struct FormData_pg_attribute *}’ from type ‘FormData_pg_attribute {aka struct FormData_pg_attribute}’
   history_attr = history_tupdesc->attrs[history_attnum - 1];
                ^
versioning.c: In function ‘deserialize_system_period’:
versioning.c:762:18: warning: implicit declaration of function ‘DatumGetRangeType’ [-Wimplicit-function-declaration]
  system_period = DatumGetRangeType(datum);
                  ^~~~~~~~~~~~~~~~~
versioning.c:762:16: warning: assignment makes pointer from integer without a cast [-Wint-conversion]
  system_period = DatumGetRangeType(datum);
                ^
versioning.c: In function ‘modify_tuple’:
versioning.c:880:24: warning: implicit declaration of function ‘RangeTypeGetDatum’ [-Wimplicit-function-declaration]
  Datum   values[1] = { RangeTypeGetDatum(range) };
                        ^~~~~~~~~~~~~~~~~
<builtin>: recipe for target 'versioning.o' failed

Could you please help with this?

Installed via the graphical installer here: http://www.bigsql.org/postgresql/installers.jsp

Followed the instructions in the README, when I run CREATE EXTENSION temporal_tables I get the following error back:

ERROR: could not load library "E:/Postgres/pg95/../pg95/lib/postgresql/temporal_tables.dll": The specified procedure could not be found.

Hello. I found your extension and at first glance it seemed to be exactly what I need.

But I have a trouble when I need to add truly historical rows (that have been active decades ago). How I can set sys_period to be timestamp in the past?

Here is the proble:

nsi=# INSERT INTO subscriptions (id, sys_period) VALUES (100, tstzrange('1900-01-01', 'infinity'));                                                                                                                                           
INSERT 0 1
nsi=# select * from subscriptions where id = 100;
 id  |             sys_period             
-----+------------------------------------
 100 | ["2016-04-11 15:27:10.533796+03",)
nsi=# update subscriptions set sys_period = tstzrange('1940-01-01', 'infinity') where id = 100;
UPDATE 1
nsi=# select * from subscriptions where id = 100;
 id  |             sys_period             
-----+------------------------------------
 100 | ["2016-04-11 15:28:33.569874+03",)
(1 row)

This is my schema:

CREATE TABLE subscriptions                                                                                        
(                                          
  id SERIAL PRIMARY KEY
);
ALTER TABLE subscriptions  ADD COLUMN sys_period tstzrange NOT NULL;
CREATE TABLE subscriptions_history (LIKE subscriptions); 
CREATE TRIGGER versioning_trigger                                                                                                                                                                                
BEFORE INSERT OR UPDATE OR DELETE ON subscriptions
FOR EACH ROW EXECUTE PROCEDURE versioning(
  'sys_period', 'subscriptions_history', true
);

Does pgxn support PG 12/13 versions? I am not able add this extension. It woks fine with PG10.
root@docker-pg-master:/# git clone https://github.com/arkhipov/temporal_tables.git
Cloning into 'temporal_tables'...
remote: Enumerating objects: 271, done.
remote: Counting objects: 100% (12/12), done.
remote: Compressing objects: 100% (11/11), done.
remote: Total 271 (delta 1), reused 10 (delta 1), pack-reused 259
Receiving objects: 100% (271/271), 89.59 KiB | 1.05 MiB/s, done.
Resolving deltas: 100% (157/157), done.
root@docker-pg-master:/temporal_tables# make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -I. -I./ -I/usr/include/postgresql/13/server -I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -c -o temporal_tables.o temporal_tables.c
temporal_tables.c:9:10: fatal error: postgres.h: No such file or directory
9 | #include "postgres.h"
| ^~~~~~~~~~~~
compilation terminated.
make: *** [: temporal_tables.o] Error 1

Tried using command $pgxn install temporal_tables
But getting the same error

Using postgres:13.5-bullseye image.

Hi! I'm new to temporal_tables and it happened such that I already upgraded my PG to 9.5 that is not mentioned explicitly in README as supported. Somehow modified project builds fine with MSBuild but fails to load into MSVS 2015 IDE. So I created a new project for debugging

Anyway, I followed the example steps, got backend pid with SELECT pg_backend_pid(); , attached my Visual Studio debugger to it, then when attempting to INSERT data I'm getting

Exception thrown: read access violation.
list_head(...) returned nullptr.

Here is the call stack

>   temporal_tables.dll!get_current_temporal_context(bool will_modify) Line 165 C
    temporal_tables.dll!get_system_time(...) Line 300   C
    temporal_tables.dll!versioning_insert(TriggerData * trigdata, TypeCacheEntry * typcache, int period_attnum) Line 874    C
    temporal_tables.dll!versioning(FunctionCallInfoData * fcinfo) Line 258  C
    postgres.exe!ExecCallTriggerFunc(TriggerData * trigdata, int tgindx, FmgrInfo * finfo, Instrumentation * instr, MemoryContextData * per_tuple_context) Line 1917    C
    postgres.exe!ExecBRInsertTriggers(EState * estate, ResultRelInfo * relinfo, TupleTableSlot * slot) Line 2042    C
    postgres.exe!ExecInsert(ModifyTableState * mtstate, TupleTableSlot * slot, TupleTableSlot * planSlot, List * arbiterIndexes, OnConflictAction onconflict, EState * estate, char canSetTag) Line 272 C
    postgres.exe!ExecModifyTable(ModifyTableState * node) Line 1442 C
    postgres.exe!ExecProcNode(PlanState * node) Line 389    C
    postgres.exe!ExecutePlan(EState * estate, PlanState * planstate, CmdType operation, char sendTuples, long numberTuples, ScanDirection direction, _DestReceiver * dest) Line 1555    C
    postgres.exe!standard_ExecutorRun(QueryDesc * queryDesc, ScanDirection direction, long count) Line 348  C
    postgres.exe!ProcessQuery(PlannedStmt * plan, const char * sourceText, ParamListInfoData * params, _DestReceiver * dest, char * completionTag) Line 190 C
    postgres.exe!PortalRunMulti(PortalData * portal, char isTopLevel, _DestReceiver * dest, _DestReceiver * altdest, char * completionTag) Line 1289    C
    postgres.exe!PortalRun(PortalData * portal, long count, char isTopLevel, _DestReceiver * dest, _DestReceiver * altdest, char * completionTag) Line 816  C
    postgres.exe!exec_simple_query(const char * query_string) Line 1111 C
    postgres.exe!PostgresMain(int argc, char * * argv, const char * dbname, const char * username) Line 4032    C
    postgres.exe!BackendRun(Port * port) Line 4238  C
    postgres.exe!SubPostmasterMain(int argc, char * * argv) Line 4729   C
    postgres.exe!main(int argc, char * * argv) Line 211 C
    [External Code] 

temporal_contexts is NULL on that line.
Any idea what can I do next?
P.S. I tried later with 9.4 64bit, it also fails on INSERT. Am I missing something?
P.P.S. I'm using stock builds from EnterpriseDB on Windows 7 64 bit.
PPPS It looks like _PG_init() should have initialized temporal_contexts. Does something here rely on fork() and a copy of process' memory into children?

When installing the official 1.1.0 release, I'm receiving an error that says:
"extension could not load library" and "the specified procedure could not be found"

Pulling the artifacts from
https://ci.appveyor.com/project/arkhipov/temporal-tables/build/job/92hel2qrm40bjwat/artifacts

This seems to install fine, but I have not tested the usability yet.
Is it recommended to use these artifacts?
Can we expect an official release for postgres 9.6 support?

First, thank you for this excellent extension!
I have noticed that the versioning trigger creates new history rows even if they are the same as the previous one. I mean, if I execute UPDATE myTable SET column = 2 WHERE id = 1 5 times - I will have 5 rows in the history differing only in the sys_period column.
I tried to wrap the versioning() function with an SQL trigger but I faced the errors function versioning(text, text, text) does not exist and function "versioning" was not called by trigger manager.
I am not familiar enough with PostgreSQL extensions API and can not modify the source code myself.
Maybe someone can help with this ?
UPDATE:
I created 2 triggers instead of one - first trigger handles INSERT and DELETE, second trigger handles UPDATE WHEN old.* IS DISTINCT FROM new.* but this does not solve the problem completely - for example if the history table has fewer columns than the original table and you update a column which is not present in the history table then you get extra rows in the history.

Please add a note stating that current Windows DLL require VCRUNTIME140.DLL are installed. This is provided by the Visual C++ Redistributable for Visual Studio 2015 downloadable from Microsoft here :

https://www.microsoft.com/en-US/download/details.aspx?id=48145

Hello,

In the doc, there is no explanation to deploy the solution in a particular schema.
It's possible to precise the schema 👍 example
CREATE TRIGGER versionning_trigger
BEFORE INSERT OR UPDATE OR DELETE ON subscriptions
FOR EACH ROW EXECUTE PROCEDURE versioning(
'sys_period', 'nomenclature.subscriptions_history', false
);

Olivier

It looks like in PG12 heap_openrv was available as a compatibility macro but now it is gone entirely.

See postgres/postgres@f25968c

This means even with the PG12 fixes in head (b1b50c9 ) the compile fails with:

Undefined symbols for architecture x86_64:
  "_heap_openrv", referenced from:
      _insert_history_row in versioning.o

https://github.com/arkhipov/temporal_tables/#update-conflicts-and-time-adjustment

name | department | salary | sys_period
-- | -- | -- | --
Lenina Crowne | Hatchery and Conditioning Centre | 7000 | [T2, T1)

It seems here you should use: statement_timestamp()

thus the updated row will look like:

name | department | salary | sys_period
-- | -- | -- | --
Lenina Crowne | Hatchery and Conditioning Centre | 7000 | [T2, **T4**)

I recently installed the extension temporal_tables on our PostgreSQL database (version 15.2, installed on Microsoft Windows Server 2022 Standard - x64).

As per the extension's installation instructions, I created a new "insert/update/delete" trigger on my existing, target table (myschema.mytable):

CREATE TRIGGER versioning_trigger BEFORE INSERT OR UPDATE OR DELETE ON myschema.mytable FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period','myschema.mytablehistory',true);`

Now, however, whenever I execute any SQL command using pgAdmin 4 (Version 6.21) that inserts, updates or deletes a row in myschema.mytable, I receive the following message:

ERROR: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

The PostgreSQL log file contains the following messages:

2024-08-15 07:06:58.500 EDT [12380] FATAL: the database system is in recovery mode
2024-08-15 07:06:58.611 EDT [18000] LOG: database system was interrupted; last known up at 2024-08-15 07:05:48 EDT
2024-08-15 07:07:01.261 EDT [18000] LOG: database system was not properly shut down; automatic recovery in progress
2024-08-15 07:07:01.278 EDT [18000] LOG: redo starts at F/69CBB4E8
2024-08-15 07:07:01.279 EDT [18000] LOG: invalid record length at F/69CBBC28: wanted 24, got 0
2024-08-15 07:07:01.279 EDT [18000] LOG: redo done at F/69CBBBF0 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2024-08-15 07:07:01.299 EDT [10208] LOG: checkpoint starting: end-of-recovery immediate wait
2024-08-15 07:07:01.333 EDT [10208] LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.008 s, sync=0.006 s, total=0.037 s; sync files=4, longest=0.002 s, average=0.002 s; distance=1 kB, estimate=1 kB

I can't tell if the temporal_tables extension (or its trigger function) is failing or if there's a PostgreSQL setting I need to change in order to make this work.

Any and all advice much appreciated.

Hi!

I can't understand this part:

At time T4 the trigger must set the start of sys_period column of the row to T1 and insert the following row into the history table:

As I see it, at time T4, employee record about Lenina should have sys_period set to [T4,) and employee_history record about Lenina should appear with sys_period [T2, T4).

Where this T1 in Lenina's record come from?

Why there is said that "set the start of sys_period column of the row to T1" but you are setting end not start?

What am I missing?

Regards
Sebastian

CONFIGS :
PostgreSQL 9.4
Temporal_tables 1.0.2

Hi,

I have an issue when I use a versioned table contains an array column like this :

CREATE TABLE example (
id serial PRIMARY KEY,
name text,
url text,
enabled boolean DEFAULT true,
tags text[],
sys_period tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, null)
);

CREATE TABLE example_history (LIKE example);

CREATE TRIGGER versioning_example_trigger
BEFORE INSERT OR UPDATE OR DELETE ON example
FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period', 'example_history', true);

INSERT INTO example VALUES (DEFAULT, 'john', 'http://www.example.com', true, ARRAY['doe', 'John'], DEFAULT);

DELETE FROM example WHERE id = 1;

When I DELETE on this table I got this error :
column "tags" of relation "example" is of type text[] but column "tags" of history relation "example_history" is of type text[]

regards,

Guillaume STOFFER

When I run pgxn install temporal_tables this is the error I receive:

pkg_resources.DistributionNotFound: The 'six' distribution was not found and is required by pgxnclient

Hey there 👋
We're currently using this extension with Postgres 9.6.14, installed on Alpine via pgxnclient install temporal_tables.

Now, we're migrating to Postgres 12 on RHEL 7.7. I've tried to install your extension from master source code, pointing PG_CONFIG to my Postgres 12 installation.

make failed. See attached log. Any pointers are appreciated.

temporal_tables_extension_postgres_12_0.log

make worked when pointing to my Postgres 10 installation on the same server.

Can I help somehow with support for Postgres versions above 10?

Any plan to support other range types, like int4range?

http://dba.stackexchange.com/questions/163340/temporal-tables-extension-error-with-array-columns/163459#163459do not

CREATE EXTENSION IF NOT EXISTS temporal_tables;

DROP TABLE IF EXISTS test;
DROP TABLE IF EXISTS test_history;

CREATE TABLE test
(
id SERIAL PRIMARY KEY,
a integer,
directories text[],
sys_period tstzrange NOT NULL
);

CREATE TABLE test_history (LIKE test);
CREATE TRIGGER versioning_trigger BEFORE INSERT OR UPDATE OR DELETE ON test FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period', 'test_history', true);
And in separate transactions the following two commands:

INSERT INTO test(a) VALUES (1);
UPDATE test SET a = 5 WHERE id = 1;
I get the following error:

ERROR: column "directories" of relation "test" is of type text[] but column "directories" of history relation "test_history" is of type text[]

I found than problem is in check_attr where equal attndims. Main problem than heap_openrv do not correctly fill it for tables created by LIKE. Workaround - create the same table without LIKE

It seems to me that PK constraint should apply to current actual relation and not its (combined) history where that PK is violated. I mean shall history table inherit employees in the readme?