current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I was thinking of a table which would automatically log all transactions made to other tables and the command to undo that modifications. So every time you issue an UNDO() command you would read the statement from the log table and execute it.

Example:

You issue a 

INSERT INTO sales (id, client_id, product, value) 
VALUES (4621, 231, 'Hamburguer', 500)

so the log-trigger would do 

INSERT INTO log (undo) VALUES ('DELETE FROM sales WHERE id = 4621')

When you issue UNDO() the function would issue something like EVAL(SELECT undo FROM log ORDER BY id DESC LIMIT 1).

Someone experienced, please tell me if this is possible.

share|improve this question

migrated from stackoverflow.com Jun 25 '13 at 18:45

This question came from our site for professional and enthusiast programmers.
1  
Typically when doing this you create a shadow table for every table you want to capture changes to. You flag the record D(delete),I(insert),U(update). Once you have that you create a trigger on the table to capture those events(What the record looks like as part of that event not the actual SQL). You can then wrap a function around the shadow table to replay log events. For example on delete you would have to find the most recent insert and run that again followed by any updates up to the delete log. Anyways triggers will add performance hit so keep that in mind. –  Bob Jun 25 '13 at 15:00

2 Answers 2

up vote 2 down vote

In theory you can, but in practice you'll find that undoing individual changes is kind of hard to get right because of foreign keys, etc.

Consider:

CREATE TABLE a (
    id integer primary key
);

CREATE TABLE b (
    id integer primary key, 
    a_id integer not null references a(id) on delete cascade
);

INSERT INTO a(id) VALUES (1),(2),(3);
INSERT INTO b(id, a_id) VALUES (10,1), (20,2), (30,3);

Now, say you do a:

DELETE FROM a WHERE id = 2;

This will cause an automatic delete of (20,2) from b. When you want to undo this change, you need to know that the delete from a also caused a delete in b.

Similarly, say you:

DELETE FROM b WHERE id = 30;

then separately and later, someone else does a:

DELETE FROM a WHERE id = 3;

If you want to undo the first delete it'll fail unless you undo the second delete first.

Basically, you can only reliably undo an operation if you undo all subsequent operations in the order that they occurred first. Even then, it's hard to get right.

That's before you even get to thinking about schema changes. If you add a new NOT NULL column to a table, then you want to undo a DELETE that happened before the column was added, what do you do? Give the new row the default value in the column? What if there's no DEFAULT set?

I'd record an audit history but wouldn't generally attempt to support undo.

You might want to investigate PostgreSQL's point-in-time recovery features for a way to do database-wide rollbacks or restores to points in the past.

share|improve this answer
up vote 0 down vote

You have a few options:

  1. programatically, you can use the Command Pattern† to implement undo

  2. in short time frames, you can use database transactions, and ROLLBACK the transaction

  3. for longer timeframes, you can use http://en.wikipedia.org/wiki/Change_data_capture or "History Tables" to track what has changed, but you'd have to undo the changes manually.

† http://mattberther.com/2004/09/16/using-the-command-pattern-for-undo-functionality

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.