On Tue, Feb 1, 2011 at 2:18 PM, Nikolas Everett <nik9...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
