Hi, On Thu, Dec 19, 2013 at 1:26 AM, Campbell, Lance <la...@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; > > 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. > > Thanks, > > > > Lance Campbell <http://illinois.edu/person/lance> > > Software Architect > > Web Services at Public Affairs > > 217-333-0382 > > [image: University of Illinois at Urbana-Champaign logo]<http://illinois.edu/> > > > > >
<<image003.png>>
