Re: Reducing Catalog Locking

On 31 October 2014 13:03, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

>> 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.

No, not really. As soon as you put that VF in there, queries will
begin to block. It doesn't really matter at what point they block, so
it doesn't make the problem worse.

VFs on pg_class are very rare and not usually run while trying to make
a normal workload happen, so its a strange thing to care about how
well that is optimized.

VACUUM FULL on pg_class only ever happens because of temp tables
anyway. I have been investigating that for other purposes, see new
thread soon.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services