Re: create/alter/drop within transactions?

Forest Wilkinson <lyris-pg(at)tibit(dot)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.