Re: New feature request: FlashBack Query

"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.

I think to do this you'll need to periodically record a snapshot and then
later restore one of those saved snapshots. Not sure where would be a good
place to record them. The WAL seems like a handy place but digging through the
WAL would be annoying.

Incidentally this is one of the things that would be useful for read-only
access to PITR warm standby machines.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com