Re: Turn dead tuples into temporal data?

Hi Jean,

> I love PostgreSQL and its community and I have also followed Datomic (BI temporal database) since its beginning.

Datomic certainly is interesting - a very different data model!

BTW, it's Bi-Temporal (or bi-temporal or whatever), but not BI - it's not Business Intelligence - the normal meaning of
BIin ICT, here "Bi" refers to two! 


> Would it be possible,(within 1 or 2 releases of PostgreSQL), without having to change many of other things that
PostgreSQLalready does pretty well, to use the DEAD tuples as historical information for native BI-temporal capability
inPostgreSQL? 

This should be of interest to you:

https://blog.dbi-services.com/temporal-tables-for-postgresql-15/

But, there are no guarantees.


> The main idea (without me knowing much about the internals of PostgreSQL) is to keep the dead tuples as a mean to
answerBITemporal queries... 

There is Vlad Arkhipov's extension, which is v. limited.
https://pgxn.org/dist/temporal_tables/

Near Form have an interesting approach (never used):
https://www.nearform.com/blog/time-travel-with-postgresql-on-amazon-rds/

and

https://github.com/xocolatl/periods (never tried).

Have fun! :-)


> Specially for financial application BITemporality is an important feature, and if that is "native" to a DB like
PostgreSQLit would put PG ahead of the game. 
> Sorry for the "dream" question here, I just want someone experienced to explain to me why this is a terrible ideal in
PostgreSQL..só that I can learn more! 


I agree with you - it's a great feature to have at one's fingertips without having to set up triggers &c.

It appeared in SQL 2011!

rgs,

SQLP!