Questions about Rollback - after insert, update, delete ... operations?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Questions about Rollback - after insert, update, delete ... operations?

Emi Lu
greetings,

I remembered I read something in the mailing list about "*rollback*" a
while ago. People mentioned that some operations cannot rollback.
I cannot remember what kinds of perations are not be able to rollback?

For example,

begin
    ... ...
    insert
    ... ...
    delete
    ... ...
    update
    ... ...
   
    /* If any of the above operation failed, we can rollback all the
above operations? */
    rollback
    ... ...
end


Will all "Insert, delete, update" operations rollback if any of the
operations fails?

Thanks a lot!
Emi

---------------------------(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
Reply | Threaded
Open this post in threaded view
|

Re: Questions about Rollback - after insert, update,

Scott Marlowe
On Thu, 2005-09-22 at 14:20, Emi Lu wrote:
> greetings,
>
> I remembered I read something in the mailing list about "*rollback*" a
> while ago. People mentioned that some operations cannot rollback.
> I cannot remember what kinds of perations are not be able to rollback?

create database and drop database cannot be rolled back, since
transactions live within a database.  Used to be that truncate couldn't
be, but I think it can now.

I don't think there are any other commands that can't be rolled back.
Certainly simple DML (data manipulation language) stuff can all be
rolled back now.  It's always been an issue for certain DDL (data
definition language) to be roll backable.  (<-- not a word, but the only
way I can think to say it)

>
> For example,
>
> begin
>     ... ...
>     insert
>     ... ...
>     delete
>     ... ...
>     update
>     ... ...
>    
>     /* If any of the above operation failed, we can rollback all the
> above operations? */
>     rollback
>     ... ...
> end
>
>
> Will all "Insert, delete, update" operations rollback if any of the
> operations fails?

Yep.  Unless you set a savepoint, any error will result in all of a
transaction being rolled back.  You don't get a choice, without a save
point.   It will be rolled back.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: Questions about Rollback - after insert, update,

Emi Lu

We are using (struts) ibates to run the transaction. We already setup
autocommitte = false, and put insert, update, delete into one
transaction. However, we found data were not rollback successfully.
Moreover, in our atomic transaction, some operations are not finished
successfull, but the data are not rollback.

Your inputs are very welcomed!


>On Thu, 2005-09-22 at 14:20, Emi Lu wrote:
>  
>
>>greetings,
>>
>>I remembered I read something in the mailing list about "*rollback*" a
>>while ago. People mentioned that some operations cannot rollback.
>>I cannot remember what kinds of perations are not be able to rollback?
>>    
>>
>
>create database and drop database cannot be rolled back, since
>transactions live within a database.  Used to be that truncate couldn't
>be, but I think it can now.
>
>I don't think there are any other commands that can't be rolled back.
>Certainly simple DML (data manipulation language) stuff can all be
>rolled back now.  It's always been an issue for certain DDL (data
>definition language) to be roll backable.  (<-- not a word, but the only
>way I can think to say it)
>
>  
>
>>For example,
>>
>>begin
>>    ... ...
>>    insert
>>    ... ...
>>    delete
>>    ... ...
>>    update
>>    ... ...
>>  
>>    /* If any of the above operation failed, we can rollback all the
>>above operations? */
>>    rollback
>>    ... ...
>>end
>>
>>
>>Will all "Insert, delete, update" operations rollback if any of the
>>operations fails?
>>    
>>
>
>Yep.  Unless you set a savepoint, any error will result in all of a
>transaction being rolled back.  You don't get a choice, without a save
>point.   It will be rolled back.
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>  
>


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: Questions about Rollback - after insert, update,

Scott Marlowe
On Thu, 2005-09-22 at 14:59, Emi Lu wrote:
> We are using (struts) ibates to run the transaction. We already setup
> autocommitte = false, and put insert, update, delete into one
> transaction. However, we found data were not rollback successfully.
> Moreover, in our atomic transaction, some operations are not finished
> successfull, but the data are not rollback.
>
> Your inputs are very welcomed!

I would tend to think it's either a bug in struts or jdbc or you're
making some mistake somewhere.  I'm not familiar with struts and
postgresql together.

You might try logging ALL your SQL statements and seeing what is
actually being sent back and forth between struts / java and postgresql.

Not sure what else to do, as I use libpq to access postgresql, meaning
no layer between my app and pgsql, like with jdbc / struts.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: Questions about Rollback - after insert, update,

Emi Lu

If ibatis can catch the exception in the program, rollback will work
fine. Could the failure of the transaction in ibatis because of the
network lost or tomcat server shutting down during the procedure.


For instance, in java program

set autocommit = false;
startTranaction
  insert ...              // step1
  update ...            //step2
 
  /* Error */
  Network lost to DB server or Tomcat server unexceptly shutting down?

  delete          //step3
commitChanges;

Could it because of the communication interrupt between the Web Server
and DB server during the atomic transaction? As a result, step1 & step2
runs successfully in Database, while step3 failed. Also, rollback failed?



>On Thu, 2005-09-22 at 14:59, Emi Lu wrote:
>  
>
>>We are using (struts) ibates to run the transaction. We already setup
>>autocommitte = false, and put insert, update, delete into one
>>transaction. However, we found data were not rollback successfully.
>>Moreover, in our atomic transaction, some operations are not finished
>>successfull, but the data are not rollback.
>>
>>Your inputs are very welcomed!
>>    
>>
>
>I would tend to think it's either a bug in struts or jdbc or you're
>making some mistake somewhere.  I'm not familiar with struts and
>postgresql together.
>
>You might try logging ALL your SQL statements and seeing what is
>actually being sent back and forth between struts / java and postgresql.
>
>Not sure what else to do, as I use libpq to access postgresql, meaning
>no layer between my app and pgsql, like with jdbc / struts.
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
>  
>


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: Questions about Rollback - after insert, update, delete ... operations?

Bruno Wolff III
In reply to this post by Emi Lu
On Thu, Sep 22, 2005 at 15:20:17 -0400,
  Emi Lu <[hidden email]> wrote:
> greetings,
>
> I remembered I read something in the mailing list about "*rollback*" a
> while ago. People mentioned that some operations cannot rollback.
> I cannot remember what kinds of perations are not be able to rollback?

I actually have the message saved for reference, so it is easier attach it
rather than try to figure out how to link to it in the archives.

Bruno Wolff III <[hidden email]> writes:
> I wasn't able to find where this is spelled out in the documentation,
> but I believe all DDL commands except DROP DATABASE can be rolled back now.

I don't think there's any all-in-one-place statement about it, but
anything that doesn't explicitly object to being put inside a
transaction block can be rolled back.  Grepping for
PreventTransactionChain, I see that the current suspects are

CLUSTER (only the multi-table variants)
CREATE DATABASE
DROP DATABASE
REINDEX DATABASE
CREATE TABLESPACE
DROP TABLESPACE
VACUUM

                        regards, tom lane


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: Questions about Rollback - after insert, update,

Ben-Nes Yonatan
Bruno Wolff III wrote:

> On Thu, Sep 22, 2005 at 15:20:17 -0400,
>   Emi Lu <[hidden email]> wrote:
>
>>greetings,
>>
>>I remembered I read something in the mailing list about "*rollback*" a
>>while ago. People mentioned that some operations cannot rollback.
>>I cannot remember what kinds of perations are not be able to rollback?
>
>
> I actually have the message saved for reference, so it is easier attach it
> rather than try to figure out how to link to it in the archives.
>
>
> ------------------------------------------------------------------------
>
> Subject:
> Re: [GENERAL] Table modifications with dependent views - best
> From:
> Tom Lane <[hidden email]>
> Date:
> Fri, 22 Apr 2005 11:36:43 -0400
> To:
> Bruno Wolff III <[hidden email]>
>
> To:
> Bruno Wolff III <[hidden email]>
> CC:
> David Roussel <[hidden email]>, Michael Fuhr
> <[hidden email]>, John Browne <[hidden email]>,
> [hidden email]
>
>
> Bruno Wolff III <[hidden email]> writes:
>
>>I wasn't able to find where this is spelled out in the documentation,
>>but I believe all DDL commands except DROP DATABASE can be rolled back now.
>
>
> I don't think there's any all-in-one-place statement about it, but
> anything that doesn't explicitly object to being put inside a
> transaction block can be rolled back.  Grepping for
> PreventTransactionChain, I see that the current suspects are
>
> CLUSTER (only the multi-table variants)
> CREATE DATABASE
> DROP DATABASE
> REINDEX DATABASE
> CREATE TABLESPACE
> DROP TABLESPACE
> VACUUM
>
> regards, tom lane
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org

You can add to that list the command TRUNCATE though it can be rollback
its not useful in cases where the truncated data should continue to be
accessed till the transaction be commited.

By the way I posted at the manual a comment about it (at the TRUNCATE
page) but it wasnt autorized, anyone know why? maybe im mistaken? maybe
its a bug and it should work?

Cheers,
   Yonatan Ben-Nes

---------------------------(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
Reply | Threaded
Open this post in threaded view
|

Re: Questions about Rollback - after insert, update, delete ... operations?

Tom Lane-2
In reply to this post by Bruno Wolff III
Bruno Wolff III <[hidden email]> writes:
>> I wasn't able to find where this is spelled out in the documentation,
>> but I believe all DDL commands except DROP DATABASE can be rolled back now.

> I don't think there's any all-in-one-place statement about it, but
> anything that doesn't explicitly object to being put inside a
> transaction block can be rolled back.  Grepping for
> PreventTransactionChain, I see that the current suspects are

> CLUSTER (only the multi-table variants)
> CREATE DATABASE
> DROP DATABASE
> REINDEX DATABASE
> CREATE TABLESPACE
> DROP TABLESPACE
> VACUUM

As of 8.1, REINDEX SYSTEM needs to be listed as well.

In this context, it may be worth pointing out that CLUSTER, VACUUM, and
REINDEX are all *internally* roll-back-able, as is essential for crash
safety.  The reason they object to being inside a transaction block is
that they want to start and end their own transactions internally so
that they can process each table in a separate transaction.

So, CREATE/DROP DATABASE and CREATE/DROP TABLESPACE really are the only
operations Postgres cannot roll back.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: Questions about Rollback - after insert, update,

Bruno Wolff III
In reply to this post by Ben-Nes Yonatan
On Fri, Sep 23, 2005 at 13:19:34 +0200,
  Yonatan Ben-Nes <[hidden email]> wrote:
>
> You can add to that list the command TRUNCATE though it can be rollback
> its not useful in cases where the truncated data should continue to be
> accessed till the transaction be commited.

I think that is a different issue. This is a problem in that truncate takes
an exclusive lock on the table which might block other things going on
in the database. It could still be useful to rollback a table truncated
in error.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster