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/ ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
