Re: AS OF queries

On 27.12.2017 00:52, Jeff Janes wrote:
> On Thu, Dec 21, 2017 at 6:00 AM, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru <mailto:k(dot)knizhnik(at)postgrespro(dot)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