Hi all....
This is a very basic question.....can we roll back data after we run a
query.
I know that a delete within a transaction can be rolled back. But how about
independent delete queries???
If i ran a delete statement and lost data...how do i recover. I know that
oracle has this provision of rollingback queries.
Iam surprised iam not able to find the same in postgres.

Sam

On Fri, 2008-07-11 at 11:43 -0400, samantha mahindrakar wrote:
> Hi all....
> This is a very basic question.....can we roll back data after we run a
> query.
> I know that a delete within a transaction can be rolled back. But how
> about independent delete queries???
> If i ran a delete statement and lost data...how do i recover. I know
> that oracle has this provision of rollingback queries.
> Iam surprised iam not able to find the same in postgres.
>
> Sam

Postgres certainly can roll back queries, table creations, and many
other actions. You can find more information about rollback here:
http://www.postgresql.org/docs/8.3/i...-rollback.html

Best of luck in your endeavor

-Mark



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On Fri, Jul 11, 2008 at 9:43 AM, samantha mahindrakar
<sam.mahindrakar@gmail.com> wrote:
> Hi all....
> This is a very basic question.....can we roll back data after we run a
> query.
> I know that a delete within a transaction can be rolled back. But how about
> independent delete queries???
> If i ran a delete statement and lost data...how do i recover. I know that
> oracle has this provision of rollingback queries.
> Iam surprised iam not able to find the same in postgres.

If you were not in a query, then you cannot just roll back. This is
because each statement is an individual transaction and a delete query
"outside" a transaction is actually a begin;delete...;commit; in
nature.

Oracle only supports the rollback after commit if you have the right
module installed and activated. And it uses up a fair bit of disk
space to do it. TANSTAAFL.

IF you have PITR setup in postgresql then you can recover to a
previous point in time. Otherwise, you need to restore from backups.

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On Fri, 2008-07-11 at 11:21 -0600, Scott Marlowe wrote:
> rollback after commit

Are you sure?

Personally I don't think its viable. If it really does that it will
would also need to rollback all transactions whose changes depend upon
the earlier transaction. It would also need to track transactions that
read data changed by an earlier transaction and then makes changes to
the database. It's got no way to track that without extensive and costly
additional infrastructure, since after transaction commit row locking
information can be cleaned up by read-only transactions accessing those
changed data blocks.

Flashback query allows reading data as it was at a certain point in the
past. We might one day provide that, but undoing individual transactions
isn't ever going to be feasible, without unknowable risk.

Not jumping on you, just think their marketing is ahead of the reality.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

In addition to allowing you to read old data, Flashback will allow you to rollback to a point in time, including returning a single table to a specific state. Flashback database is like PITR without the log files.

It started in 9i and improved dramatically in 10g. 11g has made additional improvements.

http://download.oracle.com/docs/cd/B...2/intro007.htm

http://download.oracle.com/docs/cd/B...0/rcmflash.htm


Lewis R Cunningham

An Expert's Guide to Oracle Technology
http://blogs.ittoolbox.com/oracle/guide/

Postgres Forums
http://postgres.enterprisedb.com/forum.do




--- On Fri, 7/11/08, Simon Riggs <simon@2ndquadrant.com> wrote:

> From: Simon Riggs <simon@2ndquadrant.com>
> Subject: Re: [SQL] Rollback in Postgres
> To: "Scott Marlowe" <scott.marlowe@gmail.com>
> Cc: "samantha mahindrakar" <sam.mahindrakar@gmail.com>, pgsql-sql@postgresql.org
> Date: Friday, July 11, 2008, 2:58 PM
> On Fri, 2008-07-11 at 11:21 -0600, Scott Marlowe wrote:
> > rollback after commit
>
> Are you sure?
>
> Personally I don't think its viable. If it really does
> that it will
> would also need to rollback all transactions whose changes
> depend upon
> the earlier transaction. It would also need to track
> transactions that
> read data changed by an earlier transaction and then makes
> changes to
> the database. It's got no way to track that without
> extensive and costly
> additional infrastructure, since after transaction commit
> row locking
> information can be cleaned up by read-only transactions
> accessing those
> changed data blocks.
>
> Flashback query allows reading data as it was at a certain
> point in the
> past. We might one day provide that, but undoing individual
> transactions
> isn't ever going to be feasible, without unknowable
> risk.
>
> Not jumping on you, just think their marketing is ahead of
> the reality.
>
> --
> Simon Riggs www.2ndQuadrant.com
> PostgreSQL Training, Services and Support
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On Fri, 2008-07-11 at 18:56 -0700, Lewis Cunningham wrote:

> In addition to allowing you to read old data, Flashback will allow you
> to rollback to a point in time, including returning a single table to
> a specific state. Flashback database is like PITR without the log
> files.

Like I said: you cannot rollback a single transaction after commit.

Please don't put links to copyrighted material on our lists.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On Sat, Jul 12, 2008 at 8:56 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
>
> Please don't put links to copyrighted material on our lists.

That's an odd thing to say, given that virtually every link on our
lists probably points to material copyrighted in some way.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On Sat, 2008-07-12 at 09:40 +0100, Dave Page wrote:
> On Sat, Jul 12, 2008 at 8:56 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> >
> > Please don't put links to copyrighted material on our lists.
>
> That's an odd thing to say, given that virtually every link on our
> lists probably points to material copyrighted in some way.

Prudence is all I ask for. We don't need to provide additional
advertising for others, nor do we wish to embroil ourselves in
accusations over copyright violations.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

>
> Please don't put links to copyrighted material on our
> lists.
>

Postgres docs are copyrighted. The oracle docs are free to access just like the postgres docs. What is the issue?

LewisC


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

--- On Sat, 7/12/08, Scott Marlowe <scott.marlowe@gmail.com> wrote:

> What I would appreciate as regards Oracle's flashback
> technology would
> have been a link to a well written review showing the warts
> as well as
> the beauty. I've found that Oracle stuff sounds good
> on paper, and
> turns into a giant maintenance nightmare upon deployment.
> But that's
> just what I've seen looking over Oracle DBA shoulders
> in the past.

Oracle-base is a site I trust and use. Tim writes very good articles and this is one he did recently covering flashback in 11g. The example on flashback transaction is the best I've seen.

http://www.oracle-base.com/articles/...ents_11gR1.php


Lewis R Cunningham

An Expert's Guide to Oracle Technology
http://blogs.ittoolbox.com/oracle/guide/

Postgres Forums
http://postgres.enterprisedb.com/forum.do






--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql