Re: Table modifications with dependent views - best practices?

On Fri, Apr 22, 2005 at 11:34:29AM +0100, David Roussel wrote:
>
> > I usually put DDL statements in a transaction, for a couple of
> > reasons: so that a mistake doesn't leave me with half-done work
> > (any error will cause the entire transaction to roll back), and to
> > make the changes atomic for the benefit of other transactions.
>
> Can you do that in postgres? Will it really make the DDL atomic?

Yes, although locking will probably prevent concurrent access and
can cause deadlock. DDL statements like DROP, CREATE, and ALTER
acquire an AccessExclusiveLock on the objects they're modifying,
so the transaction doing the DDL will block until no other transactions
hold locks on those objects, and other transactions' attempts to
use those objects will block until the DDL transaction commits or
rolls back. If the DDL transaction rolls back, then nobody else
will ever have seen the changes; if it commits then the changes all
become visible at the same time.

Try it and see what happens. You might see blocking and you might
be able to cause deadlock, but you shouldn't ever see some changes
but not others.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/