Re: Problem running "ALTER TABLE...", ALTER TABLE waiting

Hi Raghu,

I don't get any rows returned back from that query. I'm running it while
connected to the DB in question. Am I supposed to substitute values for
any of the variables in the query?

--
Brian McNally


On 07/17/2012 07:23 PM, raghu ram wrote:
>
>
> On Wed, Jul 18, 2012 at 1:24 AM, Brian McNally <bmcnally@uw.edu
> <mailto:bmcnally@uw.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/
>
>
>