Friday, May 28, 2010

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;

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.

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.