Re: autovacuum locks

Excerpts from Gregg Jaskiewicz's message of vie mar 02 08:22:22 -0300 2012:
>
> Looking at the system bit more now, it look like 'waiting' states are
> changing for both the query and autovacuum in pg_stat_activity.
> But very slowly. It looks like they both got into that sort of state
> that keeps them on the edge of starvation.

Ouch.

> So this isn't really a deadlock, but rather very poor performance in
> this corner case.

Right. I think I can explain how this locking works: autovacuum needs a
"cleanup" lock on the page being processed, which is a special exclusive
lock which also requires that no one is holding a "pin" on the buffer.
Any process running a query holds a pin on the buffer while inspecting
tuples on it; when it's done with tuples on that page it should move on
to the next page in the table -- same as autovac. So what seems to be
happening here is that the autovac and the scan are in sync walking the
table, stepping on each others toes.

What I don't know is why they are so closely in sync.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support