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

On Wed, Jul 18, 2012 at 9:42 PM, Brian McNally <bmcnally(at)uw(dot)edu> wrote:
> 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

Try this one. Are there any COPY queries (current_query) or long
lasting transactions (age)?

SELECT
now() - pg_stat_activity.xact_start AS age, pg_stat_activity.datname,
pg_stat_activity.procpid, pg_stat_activity.usename, pg_stat_activity.waiting,
pg_stat_activity.query_start, pg_stat_activity.client_addr,
pg_stat_activity.client_port, pg_stat_activity.current_query
FROM pg_stat_activity
WHERE pg_stat_activity.xact_start IS NOT NULL
ORDER BY pg_stat_activity.xact_start;

Ensure that your ALTER still hangs before executing this query.

> 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/
>>
>>
>>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +79160686204