pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUndo an update
6+ messages / 4 participants
[nested] [flat]
* Undo an update
@ 2006-08-11 17:26 Judith <jaltamirano@correolux.com.mx>
2006-08-11 17:41 ` Re: Undo an update Rodrigo De León <rdeleonp@gmail.com>
2006-08-12 09:08 ` Re: Undo an update Markus Schaber <schabi@logix-tt.com>
2006-08-15 12:20 ` Re: Undo an update Kis János Tamás <kjt@takarnet.hu>
0 siblings, 3 replies; 6+ messages in thread
From: Judith @ 2006-08-11 17:26 UTC (permalink / raw)
To: pgsql-sql
Hello everybody!!
Is there a way to undo an update???
Thanks!!!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Undo an update
2006-08-11 17:26 Undo an update Judith <jaltamirano@correolux.com.mx>
@ 2006-08-11 17:41 ` Rodrigo De León <rdeleonp@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Rodrigo De León @ 2006-08-11 17:41 UTC (permalink / raw)
To: pgsql-sql
If it was inside a transaction, and the transaction is still open,
then just rollback.
Otherwise, no.
I'm not sure if there's any way of doing some kind of PITR, no familiar with it.
If not, best bet is to recover from the newest backup set you have.
Regards,
Rodrigo
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Undo an update
2006-08-11 17:26 Undo an update Judith <jaltamirano@correolux.com.mx>
@ 2006-08-12 09:08 ` Markus Schaber <schabi@logix-tt.com>
2 siblings, 0 replies; 6+ messages in thread
From: Markus Schaber @ 2006-08-12 09:08 UTC (permalink / raw)
To: Judith <jaltamirano@correolux.com.mx>; +Cc: 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 (most which use the
auto-commit mode per default), or your frontend program is already
closed, it's very likely that the transaction 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Undo an update
2006-08-11 17:26 Undo an update Judith <jaltamirano@correolux.com.mx>
@ 2006-08-15 12:20 ` Kis János Tamás <kjt@takarnet.hu>
2006-08-16 08:41 ` Re: Undo an update Markus Schaber <schabi@logix-tt.com>
2 siblings, 1 reply; 6+ messages in thread
From: Kis János Tamás @ 2006-08-15 12:20 UTC (permalink / raw)
To: pgsql-sql
2006. August 11. Friday 19.26 dátummal Judith ezt írta:
>
> Is there a way to undo an update???
>
At the 1st time: I speak little english, so I sorry.
If I understand, what's your problem, maybe I can help you... Maybe...
So, if I'd like to save the inserted, deleted and/or updated rows,
then I create an extra table and some rows, triggers...
-- Function: generate_log_table()
CREATE OR REPLACE FUNCTION generate_log_table()
RETURNS "trigger" AS
$BODY$
DECLARE query text;
BEGIN
IF (TG_OP = 'INSERT') THEN query := 'INSERT INTO data_table
VALUES('||''''|| NEW.a ||''');';
ELSIF (TG_OP = 'UPDATE') THEN query := 'UPDATE data_table SET a = '
|| '''' || NEW.a || ''' WHERE id_table = '|| NEW.id_table ||';';
ELSIF (TG_OP = 'DELETE') THEN query := 'DELETE FROM data_table WHERE
id_table = '|| OLD.id_table ||';';
END IF;
INSERT INTO log_table (fecha, instruction) VALUES (now(), query);
RETURN NEW;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
-- Table: data_table
CREATE TABLE data_table
(
a text,
id_table serial NOT NULL,
CONSTRAINT table_pkey PRIMARY KEY (id_table)
)
WITH OIDS;
-- Trigger: generate_log_table on data_table
CREATE TRIGGER generate_log_table
AFTER INSERT OR UPDATE OR DELETE
ON data_table
FOR EACH ROW
EXECUTE PROCEDURE generate_log_table();
-- Table: log_table
CREATE TABLE log_table
(
fecha timestamp,
instruction text,
id_table_log serial NOT NULL,
CONSTRAINT log_table_pkey PRIMARY KEY (id_table_log)
)
WITH OIDS;
So, if you send every insert, update, delete command to a
logger-table, then you can to undo anything.
I think...
Bye,
kjt
McAfee SCM 4.1 által ellenrizve!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Undo an update
2006-08-11 17:26 Undo an update Judith <jaltamirano@correolux.com.mx>
2006-08-15 12:20 ` Re: Undo an update Kis János Tamás <kjt@takarnet.hu>
@ 2006-08-16 08:41 ` Markus Schaber <schabi@logix-tt.com>
2006-08-16 15:28 ` Re: Undo an update Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Markus Schaber @ 2006-08-16 08:41 UTC (permalink / raw)
To: Kis János Tamás <kjt@takarnet.hu>; +Cc: pgsql-sql
Hi, Kis,
Kis János Tamás wrote:
> So, if you send every insert, update, delete command to a
> logger-table, then you can to undo anything.
But this is just re-inventing the wheel, we already have Point-in-Time
recovery.
Or do I miss something?
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Undo an update
2006-08-11 17:26 Undo an update Judith <jaltamirano@correolux.com.mx>
2006-08-15 12:20 ` Re: Undo an update Kis János Tamás <kjt@takarnet.hu>
2006-08-16 08:41 ` Re: Undo an update Markus Schaber <schabi@logix-tt.com>
@ 2006-08-16 15:28 ` Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Rodrigo De León @ 2006-08-16 15:28 UTC (permalink / raw)
To: pgsql-sql
On 8/16/06, Markus Schaber <schabi@logix-tt.com> wrote:
> Hi, Kis,
>
> Kis János Tamás wrote:
>
> > So, if you send every insert, update, delete command to a
> > logger-table, then you can to undo anything.
>
> But this is just re-inventing the wheel, we already have Point-in-Time
> recovery.
>
> Or do I miss something?
That's a type of audit trail.
It's useful when you what to see what changed, when, and who did it,
and let's you revert if necessary, without doing PITR.
So, yeah, it's useful.
Regards,
Rodrigo
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-08-16 15:28 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-11 17:26 Undo an update Judith <jaltamirano@correolux.com.mx>
2006-08-11 17:41 ` Rodrigo De León <rdeleonp@gmail.com>
2006-08-12 09:08 ` Markus Schaber <schabi@logix-tt.com>
2006-08-15 12:20 ` Kis János Tamás <kjt@takarnet.hu>
2006-08-16 08:41 ` Markus Schaber <schabi@logix-tt.com>
2006-08-16 15:28 ` Rodrigo De León <rdeleonp@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox