From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Si Chen Sent: Monday, March 31, 2014 2:57 PM To: pgsql-general@postgresql.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. sic...@opensourcestrategies.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
