Skip site navigation (1) Skip section navigation (2)

Re: New feature request: FlashBack Query

From: August Zajonc <augustz(at)augustz(dot)com>
To: "Florian G(dot) Pflug" <fgp(at)phlo(dot)org>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, RPK <rohitprakash123(at)indiatimes(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: New feature request: FlashBack Query
Date: 2007-03-01 17:48:31
Message-ID: 45E711EF.1010509@augustz.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

pgsql-hackers by date

Next:From: Darcy BuskermolenDate: 2007-03-01 18:07:27
Subject: Re: Possible Bug: high CPU usage for stats collector in 8.2
Previous:From: Jonathan ScherDate: 2007-03-01 17:23:37
Subject: Re: CLUSTER, using SHARE UPDATE EXCLUSIVE lock?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group