Temporal Table Proposal

Hi,

While working on another PostgreSQL feature, I was thinking that we could
use a temporal table in PostgreSQL. Some existing databases offer this. I
searched for any discussion on the PostgreSQL mailing list, but could not
find any. Maybe my search wasn’t accurate enough: if anyone can point me
to a discussion, that would be useful.

https://www.percona.com/community-blog/2018/12/14/notes-mariadb-system-versioned-tables/
https://www.mssqltips.com/sqlservertip/3680/introduction-to-sql-server-temporal-tables/

What?
A temporal table feature has two tables “Temporal Table” and “History
Table”. The Temporal Table is where our current tuples are stored. This is
the main table, just like other PostgreSQL tables. The history table is the
other half of the feature and is where all the history of the main table is
stored. This table is created automatically. The history table is used to
query certain data at a certain time, useful for a point in time analysis.
It also offers built-in versioning.

Why?

Normally users write triggers or procedures to write a history of a table’s
data. Some time-sensitive applications will have code to write a data
history somewhere. By having this functionality, PostgreSQL would do it
automatically. For example, if we have a retail table where the price of
each product inventory item is stored. The temporal table would hold the
current price of the product. When we update the price of a product in the
temporal table, then a new row with a timestamp would be added to the
history table. That means on each update of the price, a new row containing
the previous price would be added to the history table. The same would
apply in the case of deletes. When we delete any product from our
inventory, then a row would be added to the history table storing the last
price of the product prior to delete. For any point in time, we can access
the price at which we sold the product.

How?
I was thinking about the implementation of this feature and read the
documentation on the internet. Microsoft SQL Server, for example, offers
such a feature. If we come to the conclusion we should offer the feature, I
will share the complete design.

Here are some ideas I have around this:

- Syntax.

CREATE TABLE tablename

(

…

start_time DATETIME,

end_time DATETIME,

PERIOD FOR SYSTEM_TIME (start_time, end_time)

)

WITH

(

SYSTEM_VERSIONING = ON (HISTORY_TABLE = tablename_history)

);

The tablename is the temporal table and tablename_history is be the history
table. The name of the history table is optional, in which case, PostgreSQL
will generate a table name. These two columns are a must for a temporal
table “start_time” and “end_time”. The PERIOD FOR SYSTEM_TIME is used to
identify these columns.

ALTER TABLE SET SYSTEM_VERSIONING = ON/OFF

Due to this syntax addition in CREATE/ALTER TABLE, there are some grammar
additions required in the parser.

PERIOD FOR SYSTEM TIME
SYSTEM VERSIONING

- Catalog Changes.

There are two options, one is to have another catalog pg_temporal which
will contain the information or we could have that information in the
pg_catalog

Table "public.pg_temporal"

Column | Type | Collation | Nullable | Default

-----------------+------+-----------+----------+---------

temporal_id | oid | | |

hist_id | oid | | |

start_date_name | text | | |

end_date_name | text | | |

--
Ibrar Ahmed