Re: AS OF queries

On 20.12.2017 16:12, Laurenz Albe wrote:
> Konstantin Knizhnik wrote:
>> I wonder if Postgres community is interested in supporting time travel
>> queries in PostgreSQL (something like AS OF queries in Oracle:
>> https://docs.oracle.com/cd/B14117_01/appdev.101/b10795/adfns_fl.htm)
>> As far as I know something similar is now developed for MariaDB.
> I think that would be a good thing to have that could make
> the DBA's work easier - all the requests to restore a table
> to the state from an hour ago.

Please notice that it is necessary to configure postgres in proper way
in order to be able to perform time travels.
If you do not disable autovacuum, then old versions will be just cleaned-up.
If transaction commit timestamps are not tracked, then it is not
possible to locate required timeline.

So DBA should make a decision in advance whether this feature is needed
or not.
It is not a proper instrument for restoring/auditing existed database
which was not configured to keep all versions.

May be it is better to add special configuration parameter for this
feature which should implicitly toggle
autovacuumand track_commit_timestamp parameters).

The obvious drawbacks of keeping all versions are
1. Increased size of database.
2. Decreased query execution speed because them need to traverse a lot
of not visible versions.

So may be in practice it will be useful to limit lifetime of versions.
>> I failed to support AS OF clause (as in Oracle) because of shift-reduce
>> conflicts with aliases,
>> so I have to introduce new ASOF keyword. May be yacc experts can propose
>> how to solve this conflict without introducing new keyword...
> I think it would be highly desirable to have AS OF, because that's
> the way the SQL standard has it.
Completely agree  with you: I just give up after few hours of attempts
to make bison to resolve this conflicts.

>
> Yours,
> Laurenz Albe

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