21–30 of 92 posts
I didn’t see it in the article but that’s exactly what https://postgres.ai/ is doing already unless I’m missing some thing.
I’d be very interested in a comparison of the two.
Better idea: just install the Postgres extension that enables ISO SQL Temporal Tables, just like what MSSQL and MariaDB have had for 8 years now: https://pgxn.org/dist/temporal_tables/
This was/is a very specific solution to our very specific set of problems. So not applicable to the general problem of "versioning a database".
It is still in use and still under active development – I'm actually fixing a few bugs with it just now.
I wrote much more about it here:
Better idea: just install the Postgres extension that enables ISO SQL Temporal Tables, just like what MSSQL and MariaDB have had for 8 years now: https://pgxn.org/dist/temporal_tables/
author here: Yes I agree, and I mention it at the end. However it's not possible to install the extension everywhere, for example it's not available in GCP Cloud SQL unfortunately
I just taught one of my developers how to use a basic Oracle Flashback query to pull a historical version of a table. This is my cheat sheet: ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY/MM/DD HH24:MI:SS'; select count(*) from dual AS OF TIMESTAMP TO_TIMESTAMP('2010/01/01 00:00:00'); I was aware that Postgres had "time travel," but I don't know if it used this syntax, but I am aware that the feature has been remove…
Oracle, with all the baggage attached to the namesake company, is a remarkable database. I was working in a dual MySQL/Oracle environment 20 years ago exactly, and MySQL - still at version 3 o 4 - was a toy DB, compared. I was writing Oracle queries that could make your head spin, with optimizer hints, for example: https://renenyffenegger.ch/notes/development/databases/Oracl...
Absolutely, and some of the things Tom Kyte (Oracle's resident DB performance guru) could do with Oracle were spectacular.
As you say, its a shame about the Oracle baggage, and in particular the steep price tag. Otherwise I'm certain it would be far more widely deployed as database.
I just taught one of my developers how to use a basic Oracle Flashback query to pull a historical version of a table. This is my cheat sheet: ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY/MM/DD HH24:MI:SS'; select count(*) from dual AS OF TIMESTAMP TO_TIMESTAMP('2010/01/01 00:00:00'); I was aware that Postgres had "time travel," but I don't know if it used this syntax, but I am aware that the feature has been remove…
Expanding on the the above, > The original version of PostgreSQL from the 1980s did not remove dead tuples. The idea was that keeping all the older versions allowed applications to execute “time-travel” queries to examine the database at a particular point in time [via https://ottertune.com/blog/the-part-of-postgresql-we-hate-th... ] Postgres deprecated support for time-travel in ~1997 and the more general notion of…
I once spent some time trying to find a way to do bi-temporal versioning in Postgres.
The only thing I found was a half-dead abandonware external project and an associated presentation PDF from some conference the author once spoke at.
I was unaware that they previously had some form of temporal queries and deprecated it. That is a great shame.
I just taught one of my developers how to use a basic Oracle Flashback query to pull a historical version of a table. This is my cheat sheet: ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY/MM/DD HH24:MI:SS'; select count(*) from dual AS OF TIMESTAMP TO_TIMESTAMP('2010/01/01 00:00:00'); I was aware that Postgres had "time travel," but I don't know if it used this syntax, but I am aware that the feature has been remove…
Oracle, with all the baggage attached to the namesake company, is a remarkable database. I was working in a dual MySQL/Oracle environment 20 years ago exactly, and MySQL - still at version 3 o 4 - was a toy DB, compared. I was writing Oracle queries that could make your head spin, with optimizer hints, for example: https://renenyffenegger.ch/notes/development/databases/Oracl...
We are using a Datomic derivative at my work because Postgres doesn’t have this feature. I don’t think anyone has actually used Daromic’s capabilities for this purpose.
create table test (id int primary key, attr int, deleted_at timestamp);
alter table test add default_filter is_active where deleted_at IS NULL;
then all selects like "select * from test where attr = 42" automatically gets added the above criteria and becomes "select * from test where attr = 42 AND deleted_at is NULL"
When you want old rows, you can override the default condition using something like:
select * from test where attr = 42 filter is_active (deleted_at IS NULL or deleted_at > '2010-01-01'::datetime)
You would then be able to have multiple criterias instead of just when row was deleted.