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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
