getting the current query from pg_stat_activity - Mailing list pgsql-general
| From | Si Chen |
|---|---|
| Subject | getting the current query from pg_stat_activity |
| Date | |
| Msg-id | CAAYSSjNtt0yfT6=nQYOpEji5s2oMRfJ41QP1fTwjmE2L9_HGng@mail.gmail.com Whole thread Raw |
| Responses |
Re: getting the current query from pg_stat_activity
(Igor Neyman <ineyman@perceptron.com>)
Re: getting the current query from pg_stat_activity (David Johnston <polobo@yahoo.com>) |
| List | pgsql-general |
Hello,
--
Si Chen
Open Source Strategies, Inc.
sichen@opensourcestrategies.com
http://www.OpenSourceStrategies.com
LinkedIn: http://www.linkedin.com/in/opentaps
Twitter: http://twitter.com/opentaps
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@opensourcestrategies.com
http://www.OpenSourceStrategies.com
LinkedIn: http://www.linkedin.com/in/opentaps
Twitter: http://twitter.com/opentaps
pgsql-general by date: