From what people have said before on list, one of the useful features ofPITR is to recover from rogue transactions.
I have a suggestion that I'd like some considered thought on. I've given
it a couple of weeks thought. My first inclination is that it is cool,
my second that it's pretty stupid, my third, well...?
...While recovering, it is very straightforward to simply ignore every
record associated with one (or more) transactions. That gives us the
ability to recover "all apart from txnid X".
This great because: if you can locate the transactionId you desire (a
problem in itself, but lets not dwell on those complexities for now),
then you blow it away, real smooth - as if it had never existed. Neat,
simple, fast. Dropped tables and removed tablespaces are a bit more of a
problem, but deleted rows, accidental full table updates etc can be
undone in a snap (NOT DDL, in short).
This is awful because: transactions are isolated from each other, but
they also provide changes of state that rely on previous committed
transactions. If you change the past, you could well invalidate the
future. If you blow away a transaction and a later one depends upon it,
then you will have broken the recovery chain and will not be able to
recover to present time. This is only useful IF
i) you know for certain no later transaction has taken place
ii) you know there are later transactions, but you either know they are
invalid because of the rogue, or they're just not as important as the
effects of the rogue.
...but could leave you up to your neck if misused.
undo_transactionId = X
Don't flame me, just hold you're fire, think about it and get back to
me. It's a slightly off the wall idea, so if you react quickly, you'll
just say no - and if you don't, go think some more. Anyhow, if not, then
you know who to call when it hits the fan....
This also leaves open the door for some more advanced functionality that
blows away the effects of a transaction and all of its time-ordered
descendants, just to make it 100% safe.
Best regards, Simon Riggs