On Wed, Jul 18, 2012 at 9:42 PM, Brian McNally <bmcna...@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 <bmcna...@uw.edu >> <mailto:bmcna...@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...@gmail.com Skype: gray-hemp Phone: +79160686204 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
