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
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 |