autovacuum blocking vacuum

SET  vacuum_cost_delay  = 0

pg_terminate_backend()

ALTER TABLE hd_property_instance SET (autovacuum_enabled = false,
toast.autovacuum_enabled = false);

SELECT pg_stat_activity, pg_locks.mode
FROM pg_stat_activity
JOIN pg_locks USING (pid)
JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE pg_class.relname = ‘users’
AND pg_locks.mode IN
(‘ShareUpdateExclusiveLock’, ‘ShareLock’, ‘ShareRowExclusiveLock’, ‘ExclusiveLock’, ‘AccessExclusiveLock’);

\x

SELECT pg_stat_activity.pid, pg_stat_activity.query ,pg_stat_activity.state, pg_locks.mode
FROM pg_stat_activity
JOIN pg_locks USING (pid)
JOIN pg_class ON pg_locks.relation = pg_class.oid
WHERE pg_class.relname = ‘users’
AND pg_locks.mode IN
(‘ShareUpdateExclusiveLock’, ‘ShareLock’, ‘ShareRowExclusiveLock’, ‘ExclusiveLock’, ‘AccessExclusiveLock’);

SELECT locktype, relation::regclass AS tablename
FROM pg_locks
JOIN pg_stat_activity USING (pid)
WHERE pid = 22328;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s