Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Versioning data in Postgres? Testing a Git like approach

specfy.io

Post reply on HN
Print view View original

1–10 of 92 posts

Re: Versioning data in Postgres? Testing a Git like approach

#2

Post by chasil »

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 removed. I don't know if "AS OF" is still supported. In Oracle, it has been quite helpful.

Edit: Flashback came to Oracle in 9i, when "rollback segments" were replaced by the "undo tablespace" that could present any table as it appeared in the past, limited by the amount of "undo" available. The "FLASHBACK ANY TABLE" privilege is required to see this history on tables that you do not own, and that also conveys the privilege to fully revert tables to their previous contents. You must be a DBA to exercise flashback on tables owned by SYS.

Re: Versioning data in Postgres? Testing a Git like approach

#3

Post by tlarkworthy »

Postgres replicates the whole row on a change already, its somewhat accessible too

https://stackoverflow.com/questions/7118432/how-to-reveal-ol...

This work is great but it feels wasteful given the features Postgres already has but probably the only reasonable solution is to rebuild it in user-space.

Re: Versioning data in Postgres? Testing a Git like approach

#5

Post by renegade-otter »

chasil wrote: post #2

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...

Re: Versioning data in Postgres? Testing a Git like approach

#7

Post by auspiv »

chasil wrote: post #2

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…

Been a feature of SQL Server since I believe SQL Server 2008 - temporal tables. They are used (incorrectly) at my place of work quite a bit. If auditing data (who changed what and when) is important - there are two choices: 1) system versioning or 2) add a "version" column or something similar which increments with each change. There are tradeoffs for both.

"Temporal tables (also known as system-versioned temporal tables) are a database feature that brings built-in support for providing information about data stored in the table at any point in time, rather than only the data that is correct at the current moment in time."

https://learn.microsoft.com/en-us/sql/relational-databases/t...

Re: Versioning data in Postgres? Testing a Git like approach

#9

Post by refset »

chasil wrote: post #2

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 "system time" wasn't standardised until SQL:2011. This blog post is a good overview on the SQL:2011 spec + adoption in databases of (bi-)temporal versioning: https://illuminatedcomputing.com/posts/2019/08/sql2011-surve...

Temporal versioning is less sophisticated than git-like versioning (no branching etc.) but is usually more aligned with common end-user requirements. Kent Beck suggests this framing of "eventual business consistency": https://tidyfirst.substack.com/p/eventual-business-consisten...

Post reply on HN