From: Tom Lane <tgl@sss.pgh.pa.us>
To: Forest Wilkinson <lyris-pg@tibit.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: create/alter/drop within transactions?
Date: Fri, 25 Apr 2003 22:18:34 -0400
Message-ID: <9957.1051323514@sss.pgh.pa.us> (raw)
In-Reply-To: <ecmjavglbdcsnitbnil30i7kp6rrm5qi8b@4ax.com>
References: <ecmjavglbdcsnitbnil30i7kp6rrm5qi8b@4ax.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, lyris-pg@tibit.com
Subject: Re: create/alter/drop within transactions?
In-Reply-To: <9957.1051323514@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox