Re: AS OF queries

On 27.12.2017 17:14, PostgreSQL - Hans-Jürgen Schönig wrote:
>
> On 12/20/2017 01:45 PM, 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.
>>
>> It seems to me that it will be not so difficult to implement them in
>> Postgres - we already have versions of tuples.
>> Looks like we only need to do three things:
>> 1. Disable autovacuum (autovacuum = off)
>> 2. Enable commit timestamp (track_commit_timestamp = on)
>> 3. Add asofTimestamp to snapshot and patch XidInMVCCSnapshot to
>> compare commit timestamps when it is specified in snapshot.
>>
> that sounds really awesome ... i would love to see that.
> my question is: while MVCC is fine when a tuple is still there ...
> what are you going to do with TRUNCATE and so on?
> it is not uncommon that a table is truncated frequently. in this case
> MVCC won't help.
> what are your thoughts on this ?

You should not use drop/truncate if you want to access old versions:)
Yes, truncate is much more faster than delete but it is because it 
operates on file level.
I think that it is quite natural limitation.

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