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 <alvhe...@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
