Re: Table modifications with dependent views - best practices?

So I have a scenario in which account creation at the application layer
generates a set of tables and indexes. The tables created have foreign
keys to tables in use (and frequently read from and written to) by the
rest of the application. Occasionally I was getting deadlocks, and this
definitely explains why, if creating foreign keys requires an
AccessExclusiveLock on the table to which the key refers.

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.