Re: Server status SQL highlighted in dark orange

Hi,

On Thu, Dec 19, 2013 at 1:26 AM, Campbell, Lance <lance(at)illinois(dot)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/>
>
>
>
>
>