Postgres Pro
Downloads
Home   >   mailing lists

Thread: server status window - query is not clearing one SQL is finished (change in behaviour)

server status window - query is not clearing one SQL is finished (change in behaviour)

From
Doug Easterbrook
Date:
hi There:

I just updated from postgres 9.1.5 to postgres 9.2 which comes with pgAmdin 1.16.   Platform is OS-X 10.8.1 that I ran into this issue, so I don't know if it is universal or not.

Once I connect to a server and go to tools->Server Status to watch the commands executing or the look at the log files (both are very handy when debugging code), previous versions of pgAdmin would show a command executing, then when pgAdmin refreshed the lists later, the column called query would go blank when the command was done.


In this version of pgAdmin, the query does not update/clear, even though the state says idle and I know that the command is complete.  Effectively, it shows the last statement executed.

If I run a select a statement against a single row table to get my row back, the query window shows the results.
Yet the server stats window still shows the completed query and never goes blank like it used to.


If I close the server status window and re-open it, the last query is still there for that pgAdmin session




I don't know if this is a pgAdmin issue -- or Postgres 9.2.


as a workaround,  I ran some test sql against the pg_stat_activity view and manipulated it a bit to add a case statement that cleared the query  if the state was idle

SELECT s.datid, d.datname, s.pid, s.usesysid, u.rolname AS usename, 
    s.application_name, s.client_addr, s.client_hostname, s.client_port, 
    s.backend_start, s.xact_start, s.query_start, s.state_change, s.waiting, 
    s.state, case when s.state='idle' then '' else s.query end
   FROM pg_database d, 
    pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), 
    pg_authid u
  WHERE s.datid = d.oid AND s.usesysid = u.oid;



Again, I don't know if the behaviour changed in the pg_admin window or far deeper in postgres.


I would like to ask for the old behaviour back in the pg_admin server stats page that shows the currently executing query.



thanks in advance and I hope this helps.




 

Doug Easterbrook
Arts Management Systems Ltd.
Phone (403) 536-1205    Fax (403) 536-1210

Re: server status window - query is not clearing one SQL is finished (change in behaviour)

From
Doug Easterbrook
Date:
just to add to this change in behaviour

if I use pgadmin 1.16 against a 9.1.5 database, the current_query goes blank when the query finished.   So the behaviour clearly changes when it is pgadmin against a 9.2 database.


just to make sure, I had a look at http://www.postgresql.org/docs/9.2/static/release-9-2.html and there is a little comment in the release notes

Rename pg_stat_activity.current_query to query because it is not cleared when the query completes (Magnus Hagander)





which implies the change in behaviour is clearly the result of a change in postgres 9.2 and not due to a change in pgAdmin.


however, I would like to ask that you consider implementing the case statement in pgAdmin to clear the query if there is nothing running.  its so handy when programming to see something finish and clear.


thanks.


Doug Easterbrook
Arts Management Systems Ltd.
Phone (403) 536-1205    Fax (403) 536-1210

On Sep 16, 2012, at 7:06 AM, Doug Easterbrook <doug@artsman.com> wrote:

hi There:

I just updated from postgres 9.1.5 to postgres 9.2 which comes with pgAmdin 1.16.   Platform is OS-X 10.8.1 that I ran into this issue, so I don't know if it is universal or not.

Once I connect to a server and go to tools->Server Status to watch the commands executing or the look at the log files (both are very handy when debugging code), previous versions of pgAdmin would show a command executing, then when pgAdmin refreshed the lists later, the column called query would go blank when the command was done.


In this version of pgAdmin, the query does not update/clear, even though the state says idle and I know that the command is complete.  Effectively, it shows the last statement executed.

If I run a select a statement against a single row table to get my row back, the query window shows the results.
Yet the server stats window still shows the completed query and never goes blank like it used to.


If I close the server status window and re-open it, the last query is still there for that pgAdmin session




I don't know if this is a pgAdmin issue -- or Postgres 9.2.


as a workaround,  I ran some test sql against the pg_stat_activity view and manipulated it a bit to add a case statement that cleared the query  if the state was idle

SELECT s.datid, d.datname, s.pid, s.usesysid, u.rolname AS usename, 
    s.application_name, s.client_addr, s.client_hostname, s.client_port, 
    s.backend_start, s.xact_start, s.query_start, s.state_change, s.waiting, 
    s.state, case when s.state='idle' then '' else s.query end
   FROM pg_database d, 
    pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), 
    pg_authid u
  WHERE s.datid = d.oid AND s.usesysid = u.oid;



Again, I don't know if the behaviour changed in the pg_admin window or far deeper in postgres.


I would like to ask for the old behaviour back in the pg_admin server stats page that shows the currently executing query.



thanks in advance and I hope this helps.




 

Doug Easterbrook
Arts Management Systems Ltd.
Phone (403) 536-1205    Fax (403) 536-1210


Re: server status window - query is not clearing one SQL is finished (change in behaviour)

From
Guillaume Lelarge
Date:
On Sun, 2012-09-16 at 07:26 -0600, Doug Easterbrook wrote:
> just to add to this change in behaviour
> 
> if I use pgadmin 1.16 against a 9.1.5 database, the current_query goes blank when the query finished.   So the
behaviourclearly changes when it is pgadmin against a 9.2 database.
 
> 
> 
> just to make sure, I had a look at http://www.postgresql.org/docs/9.2/static/release-9-2.html and there is a little
commentin the release notes
 
> 
> E.1.2.6. Monitoring
> 
> 
> Rename pg_stat_activity.current_query to query because it is not cleared when the query completes (Magnus Hagander)
> 
> 
> 
> 
> 
> which implies the change in behaviour is clearly the result of a change in postgres 9.2 and not due to a change in
pgAdmin.
> 

True.

> however, I would like to ask that you consider implementing the case statement in pgAdmin to clear the query if there
isnothing running.  its so handy when programming to see something finish and clear.
 
> 

Won't happen though. When the query is finished, the state is set to
idle, which is enough to know that the query is done. BTW, I don't want
to change this because it's really to know which was the last executed
query (especially in a "idle in transaction" state, but not only in this
case).


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com