Re: Problem running "ALTER TABLE...", ALTER TABLE waiting - Mailing list pgsql-general

On Wed, Jul 18, 2012 at 9:42 PM, Brian McNally <bmcnally@uw.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@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/
>>
>>
>>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.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.ru@gmail.com Skype: gray-hemp Phone: +79160686204