Friday, May 28, 2010

System-Versioned Tables

After my report on the upcoming SQL:2011, some people had asked me about the system-versioned table feature that is going to be the arguably only major new feature there. Here is how it works:
CREATE TABLE tab (
    useful_data int,
    more_data varchar,
    start timestamp GENERATED ALWAYS AS SYSTEM VERSION START,
    end timestamp GENERATED ALWAYS AS SYSTEM VERSION END
) WITH SYSTEM VERSIONING;
(This hilariously verbose syntax arises because this is defined so that it fits into the more general generated columns feature, e. g., GENERATED ALWAYS AS IDENTITY, similar to PostgreSQL's serial type.)
INSERT INTO tab (useful_data, more_data) VALUES (...);
This sets the "start" column to the current transaction timestamp, and the "end" column to the highest possible timestamp value.
UPDATE tab SET useful_data = something WHERE more_data = whatever;
For each row that would normally be updated, set the "end" timestamp to the current transaction timestamp, and insert a new row with the "start" timestamp set to the current transaction timestamp. DELETE works analogously.
SELECT * FROM tab;
This only shows rows where current_timestamp is between "start" and "end". To show the non-current data, the following options are
possible:
SELECT * FROM tab AS OF SYSTEM TIME timestamp;
SELECT * FROM tab VERSIONS BEFORE SYSTEM TIME timestamp;
SELECT * FROM tab VERSIONS AFTER SYSTEM TIME timestamp;
SELECT * FROM tab VERSIONS BETWEEN SYSTEM TIME timestamp AND timestamp;
There's also the option of
CREATE TABLE tab ( ... ) WITH SYSTEM VERSIONING KEEP VERSIONS FOR interval;
to automatically delete old versions.

That's more or less it. It's pretty much xmin/xmax/vacuum on a higher level with timestamps instead of numbers. And it's a revival of the old time travel feature. Obviously, you can do most or all of this with triggers already.

8 comments:

  1. however it would be nicer to be able to do it using numbers. Timestamps are not very good for that sort of stuff.

    ReplyDelete
  2. I cringe at the multiple-column approach. They should be using range types ;)

    ReplyDelete
  3. How does this affect referential integrity. For example what happens to tables with foreign keys set with one of the following:

    ON {UPDATE|DELETE} {NO ACTION|RESTRICT|CASCADE|SET {NULL|DEFAULT}}

    ReplyDelete
    Replies
    1. Foreign keys are always based on the current time. So when you delete a primary key in a system-versioned table, the cascade action does trigger, even though the primary key value might still be visible using BEFORE SYSTEM TIME etc. The foreign key table itself could also be a system-versioned table, in which case the cascaded delete action would only mark the deleted rows as old instead of removing them.

      Delete
  4. Also, I'd be nice to extend feature so that future state can be stored that will not become valid until CURRENT_TIME finally traverses into it's data range.

    ReplyDelete
  5. It's nice to know the SQL standard is catching up to what PostgreSQL had in the mid 1990s :)

    ReplyDelete
  6. Looks like a Slowly Changing Dimension type 2 table. It would be nice to have built into the DB instead of relying on ETL tools.

    ReplyDelete
  7. Any ideas on if/when this will get into PostgreSQL? It would be an awesome feature. :)

    ReplyDelete