On Fri, Oct 31, 2014 at 6:35 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> Recent work on parallel query has opened my eyes to exactly how
> frequently we request locks on various catalog tables. (Attached file
> is a lock analysis on a representative Pg server).
That analysis is interesting.
> Given these are catalog tables, we aren't doing much to them that
> requires a strong lock. Specifically, only CLUSTER and VACUUM FULL
> touch those tables like that. When we do that, pretty much everything
> else hangs, cos you can't get much done while fundamental tables are
> locked.
True, although it's currently the case that catalog tables are only
locked for the minimum time necessary. So, VF on pg_class will block
nearly any new query from starting up, but already-running queries may
be able to keep going, and idle transactions don't cause a problem.
If we held system catalogs until transaction commit, a VF on pg_class
would basically wait until every other transaction in the system
completed and preclude any other transaction from starting until it
finished. That's significantly more problematic in my view.
I think that the fast-path locking mechanism prevents the overwhelming
majority of lock-related pain for these kinds of things. Most system
catalog locks are "weak" within the meaning of fast-path locking, so
the main lock table is rarely touched at all, and manipulating our own
PGPROC - which generally nobody else is touching - just isn't that
expensive.
On a related note, I've previously had the thought that it would be
nice to have a "big DDL lock" - that is, a lock that prevents
concurrent DDL without preventing anything else - so that pg_dump
could get just that one lock and then not worry about the state of the
world changing under it.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company