Ideally, this DDL work should occur in a transaction to avoid partial creation of the relevant objects, but it seems like it will always run the risk of generating deadlocks in a production environment. Blocking is less of an issue because the transaction shouldn't ever take terribly long, but deadlocks always strike me as a red flag, especially in a production application environment.
Is there a best practice or suitable workaround for this sort of scenario?
-tfo
-- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC
Strategic Open Source: Open Your i™
http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005
On Apr 22, 2005, at 6:11 AM, Michael Fuhr wrote:
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.
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
