From: | raghu ram <raghuchennuru(at)gmail(dot)com> |
---|---|
To: | Brian McNally <bmcnally(at)uw(dot)edu> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Problem running "ALTER TABLE...", ALTER TABLE waiting |
Date: | 2012-07-18 02:23:08 |
Message-ID: | CALnrrJT-M9KGJ2pL_CpW0YgKVAeEnbO8prOoe4tqrndLk240sw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Jul 18, 2012 at 1:24 AM, Brian McNally <bmcnally(at)uw(dot)edu> wrote:
> Hello,
>
> I'm running Postgres 9.0.2 on RHEL 5.6 x86_64 and trying to run the
> following statement:
>
> alter table samples add column in_esp1234 text;
>
> When I do this the command just hangs. ps output indicates that it's
> waiting:
>
> postgres: postgres exomeSNP [local] ALTER TABLE waiting
>
> When I look to see what might have that table locked I don't see anything:
>
> select * from pg_locks where relation=(select oid from pg_class where
> relname='samples');
>
> I have a nearly identical server where this command works. It seems like
> something has this table locked, but I haven't been able to find it. Any
> ideas?
>
Please find more information using below query:
\x
SELECT
waiting.locktype AS waiting_locktype,
waiting.relation::regclass AS waiting_table,
waiting_stm.current_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.current_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.procpid = 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.procpid = other.pid
)
WHERE
NOT waiting.granted
AND
waiting.pid <> other.pid;
--
Thanks & Regards,
Raghu Ram
EnterpriseDB Corporation
Blog:http://raghurc.blogspot.in/
From | Date | Subject | |
---|---|---|---|
Next Message | Keith Chen | 2012-07-18 02:42:22 | Can't reset password |
Previous Message | Scott Bailey | 2012-07-18 01:19:52 | Chicken/egg problem with range types |