From: | Igor Neyman <ineyman(at)perceptron(dot)com> |
---|---|
To: | Si Chen <sichen(at)opensourcestrategies(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: getting the current query from pg_stat_activity |
Date: | 2014-03-31 19:07:37 |
Message-ID: | A76B25F2823E954C9E45E32FA49D70EC7A9C3560@mail.corp.perceptron.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Si Chen
Sent: Monday, March 31, 2014 2:57 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] getting the current query from pg_stat_activity
Hello,
I have two different postgresql servers running slightly versions. On one them, if I try to use pg_stat_activity to get the current queries, I get
1$ psql
psql (9.0.13)
Type "help" for help.
postgres=# select procpid, query_start, waiting, current_query from pg_stat_activity;
procpid | query_start | waiting | current_query
---------+-------------------------------+---------+----------------------------------------------------------------------------
673 | 2014-03-31 11:45:45.38988-07 | f | <IDLE>
855 | 2014-03-31 11:45:45.478935-07 | f | <IDLE>
...
This agrees with the results of
$ ps auxw | grep postgres
postgres 673 0.3 0.3 243028 55348 ? Ss Mar30 2:25 postgres: pguser databasename 127.0.0.1(53931) idle
postgres 855 0.3 0.3 243304 57584 ? Ss Mar30 2:49 postgres: pguser databasename 127.0.0.1(53981) idle
which shows that the processes are idle.
On the other one, though,
$ psql
psql (9.2.6)
Type "help" for help.
postgres=select pid, query_start, waiting, query from pg_stat_activity;
# pid | query_start | waiting | query
12333 | 2014-03-31 14:32:30.810934-04 | f | SELECT...
12376 | 2014-03-31 14:48:08.338419-04 | f | COMMIT
12405 | 2014-03-31 14:52:22.903848-04 | f | COMMIT
12406 | 2014-03-31 14:32:48.150378-04 | f | SELECT ....
which is strange, because the processes show they are idle,
postgres 12333 0.0 1.8 3437696 279736 ? Ss 14:31 0:00 postgres: opentaps databasename 127.0.0.1(37969) idle
postgres 12376 5.0 9.7 3473184 1491196 ? Ss 14:32 1:05 postgres: opentaps databasename 127.0.0.1(38025) idle
postgres 12405 1.5 6.5 3467624 1007160 ? Ss 14:32 0:19 postgres: opentaps databasename 127.0.0.1(38085) idle
postgres 12406 0.0 0.0 3432512 13024 ? Ss 14:32 0:00 postgres: opentaps databasename 127.0.0.1(38100) idle
it seems that there is also a difference between the pg_stat_activity table of version 9.0.13:
\d pg_stat_activity;
View "pg_catalog.pg_stat_activity"
Column | Type | Modifiers
------------------+--------------------------+-----------
datid | oid |
datname | name |
procpid | integer |
usesysid | oid |
usename | name |
application_name | text |
client_addr | inet |
client_port | integer |
backend_start | timestamp with time zone |
xact_start | timestamp with time zone |
query_start | timestamp with time zone |
waiting | boolean |
current_query | text |
vs 9.2.6:
View "pg_catalog.pg_stat_activity"
Column | Type | Modifiers
------------------+--------------------------+-----------
datid | oid |
datname | name |
pid | integer |
usesysid | oid |
usename | name |
application_name | text |
client_addr | inet |
client_hostname | text |
client_port | integer |
backend_start | timestamp with time zone |
xact_start | timestamp with time zone |
query_start | timestamp with time zone |
state_change | timestamp with time zone |
waiting | boolean |
state | text |
query | text |
So which one is correct? Why does 9.0.13 show the processes as idle, and 9.2.6 show a query, even though the process shows them as idle?
--
Si Chen
Open Source Strategies, Inc.
sichen(at)opensourcestrategies(dot)com
http://www.OpenSourceStrategies.com
LinkedIn: http://www.linkedin.com/in/opentaps
Twitter: http://twitter.com/opentaps
First, 9.0 and 9.2 are not "slightly" different, there are two different major releases.
Second, both are right.
It's just that for IDLE processes (state column) 9.2 shows the last query executed before process became IDLE.
9.0 didn't have this ability.
Regards,
Igor Neyman
From | Date | Subject | |
---|---|---|---|
Next Message | Leonardo M. Ramé | 2014-03-31 19:08:22 | Re: Complex query |
Previous Message | Igor Neyman | 2014-03-31 18:57:09 | Re: Complex query |