On Wed, Feb 15, 2017 at 3:26 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote: > On Wed, Feb 15, 2017 at 10:30 AM, Tim Bellis <Tim.Bellis@xxxxxxxxxxxxxx> 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) > > There are also ways of making the table less likely / not likely / > will not get vacuum automatically. If you're willing to schedule ddl > and vacuum on your own you can then mix the two in relative safety. Followup: https://www.postgresql.org/docs/9.3/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
