Re: [GENERAL] Option to undo last update on table.
On 2/15/07, RPK <[EMAIL PROTECTED]> wrote: Is there any option in PGSQL to undo last changes done on a table? Any feature similar to "FlashBack Query" in Oracle. 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 . 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/
Re: [GENERAL] Option to undo last update on table.
On Thursday 15 February 2007 10:30, RPK <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Option to undo last update on table.
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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Option to undo last update on table.
-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- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Option to undo last update on table.
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
