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.