On Fri, Oct 31, 2014 at 6:35 AM, Simon Riggs <si...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
