Re: AS OF queries

On 20 Dec 2017, at 13:48, Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>
> 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 <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
> autovacuum and track_commit_timestamp parameters).
>

I seem to recall that Oracle handles this by requiring tables that want the capability to live within a tablespace that supports flashback. That tablespace is obviously configured to retain redo/undo logs. It would be nice if the vacuuming process could be configured in a similar manner. I have no idea if it would make sense on a tablespace basis or not, though — I’m not entirely sure how analogous they are between Postgres & Oracle as I’ve never used tablespaces in Postgres.

-Joe