Re: New feature request: FlashBack Query

August Zajonc wrote:
> Gregory Stark wrote:
>> "Zeugswetter Andreas ADI SD" <ZeugswetterA(at)spardat(dot)at> writes:
>>
>>>> First we must run the query in serializable mode and replace
>>>> the snapshot with a synthetic one, which defines visibility
>>>> at the start of the desired transaction
>>> We could use something that controls "global xmin".
>>> It would ensure, that global xmin does not advance bejond
>>> what still needs to be visible. This would probably be a
>>> sliding time window, or a fixed point in time that is
>>> released by the dba/user.
>> Well there's another detail you have to cover aside from rolling back your
>> xmin. You have to find the rest of the snapshot including knowing what other
>> transactions were in-progress at the time you want to flash back to.
>>
>> If you just roll back xmin and set xmax to the same value you'll get a
>> consistent view of the database but it may not match a view that was ever
>> current. That is, some of the transactions after the target xmin may have
>> committed before that xmin. So there was never a time in the database when
>> they were invisible but your new xmin was visible.
>>
>> [...]
>> Incidentally this is one of the things that would be useful for read-only
>> access to PITR warm standby machines.
>>
>
> Couldn't you define things simply to be that you get a consistent view
> including all transactions started before x transaction? This is time
> travel lite, but low overhead which I think is a key benefit of this
> approach.

I was thinking along the same line. Flashback is probably ony really
usefull on databases that are mostly read-only, but with a few users
who update data. You'd use flashback to undo catastrophic changes done
by accident, and probably will gladly accept that you undo a little
more work than strictly necessary.

On the contrary, if you're running a online shop were people buy stuff
24/7, and, say, somebody accidentally deletes some producs, than you
won't want to loose the orders happened during that last hour, but will
rather try to regenerate that products from your last backup.

So I don't think that it's too important what snapshot you get exactly,
making the xmin=xmax idea feasable.

The same holds true for PITR warm standby (readonly queries on pitr
slaves). This would be used for reporting, or load-balancing of searches
in fairly static data - all of which won't depend on the exact snapshot
you get.

greetings, Florian Pflug