Re: AS OF queries

On Thu, Dec 21, 2017 at 6:00 AM, Konstantin Knizhnik <k.knizhnik@postgrespro.ru> wrote:

 

There is still one significant difference of my prototype implementation with SQL standard: it associates timestamp with select statement, not with particular table.
It seems to be more difficult to support and I am not sure that joining tables from different timelines has much sense.
But certainly it also can be fixed.

I think the main use I would find for this feature is something like:

select * from foo except select * from foo as old_foo as of '<some time>';

So I would be grateful if you can make that work.  Also, I think conforming to the standards is pretty important where it is feasible to do that.

Cheers,

Jeff

I attach ne version of the patch which supports "standard" syntax, where AS OF clause is associated with table reference.
So it is possible to write query like:

    select * from SomeTable as t as of timestamp '2017-12-27 14:54:40' where id=100;

Also I introduced "time_travel" GUC which implicitly assigns some others GUCs:

        track_commit_timestamp = true;
        vacuum_defer_cleanup_age = 1000000000;
        vacuum_freeze_min_age = 1000000000;
        autovacuum_freeze_max_age = 2000000000;
        autovacuum_multixact_freeze_max_age = 2000000000;
        autovacuum_start_daemon = false;

So it disables autovacuum and microvacuum and enable commit timestamps tracking.
It provides access in the past up to milliard of transactions.

There is still no way to keep all versions only for particular tables or truncate too old versions.

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company