To: Forest Wilkinson <lyris-pg@tibit.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: create/alter/drop within transactions? 
In-reply-to: <ecmjavglbdcsnitbnil30i7kp6rrm5qi8b@4ax.com> 
References: <ecmjavglbdcsnitbnil30i7kp6rrm5qi8b@4ax.com>
Comments: In-reply-to Forest Wilkinson <lyris-pg@tibit.com>
	message dated "Fri, 25 Apr 2003 17:58:46 -0700"
Date: Fri, 25 Apr 2003 22:18:34 -0400
Message-ID: <9957.1051323514@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Forest Wilkinson <lyris-pg@tibit.com> writes:
> Sorry if this is a FAQ, but I didn't find a definitive and recent
> answer in the list archives or the postgres docs.  Do transactions
> work on create / alter / drop statements?  In other words, can I
> choose to commit or rollback after a bunch of schema changes?

Yes.

AFAIR, the only non-rollback-able command in Postgres is TRUNCATE TABLE
--- and that's fixed for 7.4 ;-)

In any case, anything you might try to do inside a transaction block
will error out if it cannot roll back.  For example, in 7.3:

regression=# create table foo(f1 int);
CREATE TABLE
regression=# begin;
BEGIN
regression=# truncate table foo;
ERROR:  TRUNCATE TABLE cannot run inside a transaction block

at which point your transaction is aborted and nothing has happened.
If Postgres lets you do it inside BEGIN, we can roll it back.

			regards, tom lane

PS: the only real disadvantage of doing tons-of-schema-changes inside
a BEGIN is that you will be grabbing exclusive locks on each table you
change the schema of.  This means you are running a nontrivial risk of
deadlock against other transactions --- in which case you probably lose
your work and have to do it over.