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 

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to