PostgreSQL Discussion - Re: Questions about Rollback

Author

Re: Questions about Rollback - after insert, update,

Scott Marlowe

2005-09-22, 8:24 pm

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

Emi Lu

2005-09-22, 8:24 pm

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:
>
>
>
>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)
>
>
>
>
>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

Emi Lu

2005-09-22, 8:24 pm

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:
>
>
>
>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

Sponsored Links