Re: [GENERAL] Alter table never finishes

[bricklen]

On Tue, Jul 30, 2013 at 10:07 AM, Leonardo M. Ramé <l.r...@griensu.com>wrote:

>
> select pg_cancel_backend(pid) from pg_stat_activity where pid <>
> pg_backend_pid();
>
> And it returned this:
>
> pg_cancel_backend
> -------------------
>  t
>  t
> (2 rows)
>
> But when I execute my update table command, it still never ends...Any
> hint?.
>
>
Sounds like locking issues. In another session -- other than the one you
are trying to run your update, what does the following query show?

SELECT
    waiting.locktype           AS waiting_locktype,
    waiting.relation::regclass AS waiting_table,
    waiting_stm.query          AS waiting_query,
    waiting.mode               AS waiting_mode,
    waiting.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 waiting
JOIN pg_catalog.pg_stat_activity AS waiting_stm ON (waiting_stm.pid =
waiting.pid)
JOIN pg_catalog.pg_locks AS other ON ((waiting."database" =
other."database" AND waiting.relation  = other.relation) OR
waiting.transactionid = other.transactionid)
JOIN pg_catalog.pg_stat_activity AS other_stm ON (other_stm.pid = other.pid)
WHERE NOT waiting.granted
AND waiting.pid <> other.pid;