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

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

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.

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.
mailto:doug(at)artsman(dot)com
http://www.artsman.com
Phone (403) 536-1205 Fax (403) 536-1210

On Sep 16, 2012, at 7:06 AM, Doug Easterbrook <doug(at)artsman(dot)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.
> mailto:doug(at)artsman(dot)com
> http://www.artsman.com
> Phone (403) 536-1205 Fax (403) 536-1210
>