Re: autovacuum locks

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> 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.

I don't believe that. The trace shows the other process is waiting for
a tuple lock, which is not something that autovacuum would take.

Given the reference to prepared transactions, what seems likely is that
the UPDATE command is blocked by a prepared transaction (IOW, one that
already updated the same tuple) and VACUUM is stuck behind the UPDATE.
So the real problem is slow removal of prepared transactions, which
most likely is an application logic problem. It's certainly not
autovac's fault.

regards, tom lane