Re: New feature request: FlashBack Query

Florian G. Pflug wrote:
> Alvaro Herrera wrote:
>> Florian G. Pflug wrote:
>>
>>> However, I just realized that doing this is much harder than I initially
>>> thought, because catalog access always happens with SnapshotNow, and
>>> e.g. "drop table" deletes datafiles at commit time, and not during
>>> vacuum.
>>
>> Not to mention the likenesses of CLUSTER and TRUNCATE, which would need
>> to be taught how to keep the old datafiles for an additional week/hour.
>>
>> What I don't understand is why people isn't working in improving
>> contrib/spi/timetravel.
>
> Because it serves different usecase I think - flashback is an
> administrative tool, not something you design your application around.
> Flashback is more similar to PITR recovery than to contrib/spi/timetravel.

Drat. I remember when truncate wasn't even transaction safe, but I think
it was since cut so that the non-rollbackable portion happened after
commit.

Ultimately, anything that changed data would need to basically deferred
into the vacuum or other cycle. Basically, super MVCC, a truncate would
basically do the tuple type action on the underlying files. Catalog
stuff too, HOT all would need those semantics. Not doable.

A lot of places that grab an AccessExclusiveLock are probably subject to
this issue.

Unless there was a bog standard way of doing this, and I don't see a
good option, no go.

So fun to think about and probably all sorts of neat things you could do
with super MVCC, TRUNCATE a table with open transactions concurrent,
but way too work for the gain of this tiny use feature...

contrib/timetravel I think has some of these same issues (ie, drop
table, can you still time travel?) along with a fair bit of trigger
based overhead...

- August