Search Postgresql Archives

Re: Alter table never finishes

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux