Re: Autovacuum stuck for hours, blocking queries

On 02/15/2017 09:30 AM, Tim Bellis wrote:
> I have a postgres 9.3.4 database table which (intermittently but reliably) gets into a state where queries get blocked indefinitely (at least for many hours) behind an automatic vacuum. I was under the impression that vacuum should never take any blocking locks for any significant period of time, and so would like help resolving the issue.
>
> The process blocking the query is:
> postgres 21985 11304 98 Feb13 ? 1-14:20:52 postgres: autovacuum worker process <db_name>
> which is running the query
> autovacuum: VACUUM public.<table_name>
>
> The query being blocked is:
> ALTER TABLE <table_name> ALTER COLUMN <column_name> DROP DEFAULT
> (But I have seen this previously with other queries being blocked. I used the SQL in https://wiki.postgresql.org/wiki/Lock_Monitoring to determine which queries were blocked)
>

Other ALTER TABLE queries?

If so I believe this might apply:

https://www.postgresql.org/docs/9.5/static/explicit-locking.html

SHARE UPDATE EXCLUSIVE

Conflicts with the SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW
EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode
protects a table against concurrent schema changes and VACUUM runs.

Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX
CONCURRENTLY, and ALTER TABLE VALIDATE and other ALTER TABLE variants
(for full details see ALTER TABLE).

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com