Re: Undo an update - Mailing list pgsql-sql

Hi, Judith,

Judith wrote:

>    Is there a way to undo an update???

I'm sorry, but his is one of those short questions that only have long,
complex answers.

If this answer is to long / complicated for you, begin from the top, and
whenever it get's worse, ask yourself how urgent your problem is, how
much you're willing to invest to recover your data, and that the simple
alternative answer is "No.".

Depending on what happened since you fired the update, and how you fired
it, you may have several possibilities to recover, some of which might
be more expensive than the worth of your data.

1) If you created an savepoint / checkpoint / subtransaction just prior
to the update, and the transaction is still open, roll back to the
savepoint.

2) If you did not create a savepoint, but the transaction is still open,
you'll have to rollback the whole transaction.

3) If you did start your update in a transaction, and then closed your
frontend without committing (IOW, having the offending transaction still
open), the transaction will usually be rolled back by the server when it
notices that the connection vanished.

4) If you did not start your update in a transaction (e. G. by starting
it in auto-commit mode), but it is still running in the backend (a very
long running update), you can abort the backend process, which will roll
back the transaction, but is very likely to disturb or abort other
connections running concurrently.
 (Explanation: If you don't know what transactions are[1], and you just  fired your query via psql or another frontend
(mostwhich use the  auto-commit mode per default), or your frontend program is already  closed, it's very likely that
thetransaction is not open any  more.)
 

5) If you used two-phase-commit, and did prepare, but not commit the
transaction, you can still reconnect to the database and roll back the
frozen transaction.

6) If your transaction is not open any more, but you saved the PG XLog
files for PITR (Point In Time Recovery), or maybe just have enough of
the most recent xlog files lying around, you should be able to roll back
your database to the point just before the transaction containing your
update committed, but all changes by other transactions after your
update will be lost, too. But PITR is only available with recent
versions of PostgreSQL.

If neither of the above points is true for you, I see the following
possibilities, which are not exactly "UNDO", but will restore your data.

7a) Try to formulate an UPDATE (or series of SQL commands) that exactly
reverts the effect of your offending update.

7b) Restore your database from a recent backup[2] / your original data
sources[3], and recreate all actions that happened between that and your
offending update.

7c) If neither you nor the autovacuum daemon did not run any VACUUM
commands yet, you might use forensics or manipulation with the
PostgreSQL transaction ID counters to recover the old row versions from
the PostgreSQL tables. You should be (or hire) an absolute expert for
PostgreSQL internas to do this, and only try this on a copy of your
PostgreSQL cluster, as the risk is high that your whole cluster gets
inconsistent. If you want to go this way, you should shut down your
database server _now_, and don't restart it unless that absolute expert
does so.


Maybe I've missed some additional option or academical corner cases, but
I'm pretty shure the most important ones are mentioned.

HTH,
Markus

Footnotes:
[1] In this case, it is advisable that your read appropriate beginners
documentation about RDBMS (Relational DataBase Management Systems) in
general, and the appropriate parts of the PostgreSQL documentation, to
avoid getting in the same trouble again in the future. (And, in my
opinion, MySQL based literature does not qualify as "appropriate
beginners documentation about RDBMS" as they view some things somwehat
differently than all other RDBMSes I know of.)

[2] If you don't have a recent backup at hands, some experts may regard
this as a proof that your data was not important.

[3] For read-only / look-up data that you get from an upstream source,
like phonebooks, street network data, or ISBN/ISSN/EAN databases, etc.

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org