autovacuum holds exclusive lock on table preventing it from to be updated

From: Dmitry O Litvintsev <litvinse(at)fnal(dot)gov>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: autovacuum holds exclusive lock on table preventing it from to be updated
Date: 2017-06-13 18:04:04
Message-ID: BL2PR09MB1009FA5F4251BD7807894CE8B9C20@BL2PR09MB1009.namprd09.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I run postgresql 9.3.17. I am preparing for a major database schema upgrade.

I copied production database to test system using pg_basebackup.

Having started the database and waited for all WALs to be applied I proceeded to run
schema modifications.

Immediately I run into issue - updates on a table get stuck because I see that autovacuum is running
on that table and it holds exclusive lock:

datname | relname | transactionid | mode | granted | usename | substr | query_start | age | pid
---------+----------------------------+---------------+--------------------------+---------+---------+---------------------------------------------------------------------------+-------------------------------+-----------------+-------
chimera | t_inodes_itype_idx | | RowExclusiveLock | t | enstore | autovacuum: VACUUM public.t_inodes (to prevent wraparound) | 2017-06-13 12:31:04.870064-05 | 00:25:22.285415 | 40672
chimera | t_inodes_imtime_idx | | RowExclusiveLock | t | enstore | autovacuum: VACUUM public.t_inodes (to prevent wraparound) | 2017-06-13 12:31:04.870064-05 | 00:25:22.285415 | 40672
chimera | t_inodes_iio_idx | | RowExclusiveLock | t | enstore | autovacuum: VACUUM public.t_inodes (to prevent wraparound) | 2017-06-13 12:31:04.870064-05 | 00:25:22.285415 | 40672
chimera | t_inodes_pkey | | RowExclusiveLock | t | enstore | autovacuum: VACUUM public.t_inodes (to prevent wraparound) | 2017-06-13 12:31:04.870064-05 | 00:25:22.285415 | 40672
chimera | | | ExclusiveLock | t | enstore | autovacuum: VACUUM public.t_inodes (to prevent wraparound) | 2017-06-13 12:31:04.870064-05 | 00:25:22.285415 | 40672
chimera | t_inodes | | ShareUpdateExclusiveLock | t | enstore | autovacuum: VACUUM public.t_inodes (to prevent wraparound)

If I killed autovacuum (by running SELECT pg_cancel_backend(PID) , I get at an update going, but then another update would get stuck by autovacuum launching again).

I tried to set autovacuum to off (together w/ track_counts) and conf file. After restart , autovacuum still runs !
chimera=# show autovacuum;
autovacuum
------------
off
(1 row)

checking activity :
chimera=# select pg_stat_activity.datname,pg_class.relname,pg_locks.transactionid,
pg_locks.mode, pg_locks.granted,pg_stat_activity.usename,
substr(pg_stat_activity.query,1,256),
pg_stat_activity.query_start, age(now(),pg_stat_activity.query_start) as "age",
pg_stat_activity.pid from pg_stat_activity,pg_locks
left outer join pg_class on (pg_locks.relation = pg_class.oid)
where pg_locks.pid=pg_stat_activity.pid order by query_start;

shows autovacuum. Seems like setting it to off does not take any effect.

datname | relname | transactionid | mode | granted | usename | substr | query_start | age | pid
---------+----------------------------+---------------+--------------------------+---------+---------+---------------------------------------------------------------------------+-------------------------------+-----------------+-------
chimera | t_inodes_itype_idx | | RowExclusiveLock | t | enstore | autovacuum: VACUUM public.t_inodes (to prevent wraparound) | 2017-06-13 12:31:04.870064-05 | 00:28:50.276437 | 40672
chimera | t_inodes_imtime_idx | | RowExclusiveLock | t | enstore | autovacuum: VACUUM public.t_inodes (to prevent wraparound) | 2017-06-13 12:31:04.870064-05 | 00:28:50.276437 | 40672
chimera | t_inodes_iio_idx | | RowExclusiveLock | t | enstore | autovacuum: VACUUM public.t_inodes (to prevent wraparound) | 2017-06-13 12:31:04.870064-05 | 00:28:50.276437 | 40672
chimera | t_inodes_pkey | | RowExclusiveLock | t | enstore | autovacuum: VACUUM public.t_inodes (to prevent wraparound) | 2017-06-13 12:31:04.870064-05 | 00:28:50.276437 | 40672
chimera | | | ExclusiveLock | t | enstore | autovacuum: VACUUM public.t_inodes (to prevent wraparound) | 2017-06-13 12:31:04.870064-05 | 00:28:50.276437 | 40672
chimera | t_inodes | | ShareUpdateExclusiveLock | t | enstore | autovacuum: VACUUM public.t_inodes (to prevent wraparound) | 2017-06-13 12:31:04.870064-05 | 00:28:50.276437 | 40672

Anything I am doing wrong or is this a bug (or feature). The issue - autovacuum blocks table updates and I cannot turn the autovacuum off.


Dmitry

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andreas Kretschmer 2017-06-13 18:54:18 Re: autovacuum holds exclusive lock on table preventing it from to be updated
Previous Message Tom Lane 2017-06-13 13:44:54 Re: ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100