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

Author

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

Emi Lu

2005-09-22, 8:24 pm

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

Bruno Wolff III

2005-09-23, 3:23 am

On Thu, Sep 22, 2005 at 15:20:17 -0400,
Emi Lu <emilu@cs.concordia.ca> 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.

Tom Lane

2005-09-23, 9:23 am

Bruno Wolff III <bruno@wolff.to> writes:
[color=darkred]
> 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
> PreventTransactionCh
ain, 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

Sponsored Links