Re: Exhaustive list of what takes what locks

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