On Tue, Feb 1, 2011 at 2:18 PM, Nikolas Everett <nik9000@gmail.com> wrote:
> This isn't exactly how our workload actually works. Ours is more deadlock
> prone. We have many connections all querying account and we do the
> migration in a transaction. It looks as though the AccessExclusiveLock is
> held until the transaction terminates.
Unfortunately, that's necessary for correctness. :-(
I'd really like to figure out some way to make these cases work with
less locking. 9.1 will have some improvements in this area, as
regards ALTER TABLE, but dropping a constraint will still require
AccessExclusiveLock.
There are even workloads where competition for AccessShareLock on the
target table is a performance bottleneck (try pgbench -S -c 36 -j 36
or so). I've been idly mulling over whether there's any way to
eliminate that locking or at least make it uncontended in the common
case, but so far haven't thought of a solution that I'm entirely happy
with.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company