Обсуждение: Server status SQL highlighted in dark orange
Hi,
On Thu, Dec 19, 2013 at 1:26 AM, Campbell, Lance <lance@illinois.edu> wrote:
pgAdmin 3 1.18.1
PostgreSQL 9.2
I am having issues with a query getting “stuck” while doing updates. So I went to Server Status in pgAdmin. I found the query was highlighted in dark orange. When a query appears to get hung up and won’t finish what steps should I take to figure out what is happening?
I believe, pgAdmin support of blocked/waiting queries. If a query got stuck, then use the tool to check whether it is waiting on other transaction or not.
OR
Use this query to get that information.
SELECT now()::timestamp,waiting.locktype AS waiting_locktype,waiting.relation::regclass::VARCHAR AS
waiting_table,waiting_stm.Datname as WDatabase,waiting_stm.current_query AS waiting_query,(extract(epoch
from now())extract(epoch from waiting_stm.query_start))::VARCHAR AS Waiting_Stmt_Total_Time,waiting.mode AS waiting_mode,waiting.pid::VARCHAR AS waiting_pid,other.locktype::VARCHAR AS
other_locktype,other.relation::regclass::VARCHAR AS other_table,other_stm.Datname::VARCHAR as ODatabase,other_stm.current_query AS other_query,(extract(epoch from
now())extract(epoch from other_stm.query_start))::VARCHAR AS
Other_Stmt_Total_Time,other.mode AS other_mode,other.pid::VARCHAR AS
other_pid,other.granted::VARCHAR 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;
SELECT now()::timestamp,waiting.locktype AS waiting_locktype,waiting.relation::regclass::VARCHAR AS
waiting_table,waiting_stm.Datname as WDatabase,waiting_stm.current_query AS waiting_query,(extract(epoch
from now())extract(epoch from waiting_stm.query_start))::VARCHAR AS Waiting_Stmt_Total_Time,waiting.mode AS waiting_mode,waiting.pid::VARCHAR AS waiting_pid,other.locktype::VARCHAR AS
other_locktype,other.relation::regclass::VARCHAR AS other_table,other_stm.Datname::VARCHAR as ODatabase,other_stm.current_query AS other_query,(extract(epoch from
now())extract(epoch from other_stm.query_start))::VARCHAR AS
Other_Stmt_Total_Time,other.mode AS other_mode,other.pid::VARCHAR AS
other_pid,other.granted::VARCHAR 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;
I have checked the database logs and there does not seem to be an issue. Is there some way I could tell PostgreSQL through pgAdmin to give me more information on why the query is stuck? Is there a way to get more analytic information on the query while it is running?
By enabling this parameter "log_lock_waits" in the postgresql.conf, we will get locks information in pg_logs.
