Обсуждение: Option to undo last update on table.

Is there any option in PGSQL to undo last changes done on a table? Any
feature similar to "FlashBack Query" in Oracle.

--
View this message in context: http://www.nabble.com/Option-to-undo-last-update-on-table.-tf3232456.html#a8981518
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

RPK wrote:
>
> Is there any option in PGSQL to undo last changes done on a table?

Sure -- you just issue ROLLBACK to end your transaction instead of
COMMIT.

> Any feature similar to "FlashBack Query" in Oracle.

I don't know about that.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/15/07 02:29, RPK wrote:
> Is there any option in PGSQL to undo last changes done on a table?

Presumably, this is *after* you committed them?

> Any
> feature similar to "FlashBack Query" in Oracle.
>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF1G+/S9HxQb37XmcRAlcTAJ9iBZOJEAUJzMuCaXtSzL8+zYMqQgCeIfCF
3MhJ7OI1P5ZurrgNZC7NZhc=
=3IUs
-----END PGP SIGNATURE-----

In my case, I did an accidental update on a large table where different
column values have changed. I continued working and found next day that
something wrong happened.

Rollback will just rollback to last step (if I am right), but is there a way
to bring the table to a certain TimeStamp.

Just in case you have not heard of "FlashBack" facility in Oracle, check
this link:
http://www.oracle.com/technology/deploy/availability/htdocs/Flashback_Overview.htm
Oracle FlashBack Technology

I think PGSQL future versions must have something like this.


--
View this message in context: http://www.nabble.com/Option-to-undo-last-update-on-table.-tf3232456.html#a8990995
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Thursday 15 February 2007 10:30, RPK <rohitprakash123@indiatimes.com>
wrote:
> Rollback will just rollback to last step (if I am right), but is there a
> way to bring the table to a certain TimeStamp.

You can use a PITR backup to restore a fresh cluster to a time just before
your update.  If you are not running PITR backups, then no.

--
"The power of the executive to cast a man into prison without formulating
any charge known to the law, and particularly to deny him the judgment of
his peers, is in the highest degree odious, and the foundation of all
totalitarian government whether Nazi or Communist." -- Winston Churchill

On 2/15/07, RPK <rohitprakash123@indiatimes.com> wrote:
The only way I could imagine that you could implement a flashback query in PostgreSQL is if you ignored everyone's advice and didn't vacuum  <not a good idea>.


Oracle uses an undo tablespace which records all of the information to "rollback" to an older version of row xyz, but PostgreSQL stores a "new version of row xyz" in the same data file and vacuum flags the "old version of row xyz" for deletion which may quickly get overwritten (or may be unusable for other reasons???).


As for others that may be interested, the flashback feature allows you to specify essentially the "version" to use when executing the query.  And it is typically done by specifying the SCN (which should be equivalent to the xid for PostgreSQL) OR a timestamp.


--
Chad
http://www.postgresqlforums.com/