Re: Alter table never finishes

On 2013-07-30 10:26:39 -0700, bricklen wrote:
> On Tue, Jul 30, 2013 at 10:07 AM, Leonardo M. Ramé <l(dot)rame(at)griensu(dot)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;

Sorry bricklen, I've killed all idle connections with "kill -9 <PID>",
then I was able to execute the alter table.

--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292