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(at)uw(dot)edu
> <mailto: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/
>
>
>