Re: Stucks in the middle

On Sat, Jan 9, 2016 at 1:46 PM, Yevgeny <jebjick(at)gmail(dot)com> wrote:

> This command helps VACUUM FULL ANALYZE VERBOSE;
> but in 2-3 hours it starts working slowly again
> Any ideas?
>

VACUUM FULL requires a table lock, is it getting blocked behind other locks?

Here is a sample query to view outstanding locks.

SELECT w.locktype AS waiting_locktype,w.relation::regclass AS
waiting_table,w.transactionid,
substr(w_stm.query,1,20) AS waiting_query,w.mode AS
waiting_mode,w.pid AS waiting_pid,
other.locktype AS other_locktype,other.relation::regclass AS
other_table,
other_stm.query AS other_query,other.mode AS other_mode,other.pid
AS other_pid,
other.granted AS other_granted
FROM pg_catalog.pg_locks AS w
JOIN pg_catalog.pg_stat_activity AS w_stm ON (w_stm.pid = w.pid)
JOIN pg_catalog.pg_locks AS other ON ((w."database" = other."database" AND
w.relation = other.relation) OR w.transactionid = other.transactionid)
JOIN pg_catalog.pg_stat_activity AS other_stm ON (other_stm.pid =
other.pid)
WHERE NOT w.granted
AND w.pid <> other.pid