Re: New feature request: FlashBack Query

On Mon, 19 Feb 2007 20:30:59 +0100, "Florian G. Pflug" <fgp(at)phlo(dot)org>
said:
> August Zajonc wrote:
> > Gregory Stark wrote:
> >
> > 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.

Hopefully people doing order systems are using PITR or similar :)

For the time travel light case, it's just a matter of clear definition.
You get all transactions that were *started* before and up to x trx. If
the transaction rolled back you still won't see it, so you're still
getting a consistent view. But if it committed after your marker you
will see it. That seems ok to me. In fact, I suspect folks think of
transactions as happening more or less when they get sent to the DB, so
this may map more directly to what people expect.

The one caveat would be that if you started a long running transaction,
then did the oops trx 5 minutes later, and then started time travel
*before* the long running trx committed. In that case you wouldn't see
that long running trx, so the definition would need to be modified to be
something like all trx started before x, that were no longer running
when you time travel. Don't know if it is worth a NOTICE in the logs if
you time travel back, but there are id's of transactions from before
your xmin that are still running (and if you waited a bit might become
visable in your time travel view).

If Jan gets his way with a timestamp on trx commit, then you can do
started before x time, which may be more user friendly.

For PITR I'd imagine you might actually be able to get the visability
right no? Havn't looked deeply enough into the wal logs to understand
how the partial playback scanario works. If the wal logs are ordered on
trx commit time, then you'd get proper visability.

- August