[GENERAL] autovacuum holds exclusive lock on table preventing it from to beupdated - Mailing list 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:
VACUUMpublic.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:
VACUUMpublic.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:
VACUUMpublic.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:
VACUUMpublic.t_inodes (to prevent wraparound)                | 2017-06-13 12:31:04.870064-05 | 00:25:22.285415 | 40672 
 chimera |                            |               | ExclusiveLock            | t       | enstore | autovacuum:
VACUUMpublic.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:
VACUUMpublic.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
wouldget 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:
VACUUMpublic.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:
VACUUMpublic.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:
VACUUMpublic.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:
VACUUMpublic.t_inodes (to prevent wraparound)                | 2017-06-13 12:31:04.870064-05 | 00:28:50.276437 | 40672 
 chimera |                            |               | ExclusiveLock            | t       | enstore | autovacuum:
VACUUMpublic.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:
VACUUMpublic.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
theautovacuum off.  


Dmitry