Thread: getting the current query from pg_stat_activity

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@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
getthe 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
showsthem 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

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

Si Chen-2 wrote
> I have two different postgresql servers running slightly [different]
> versions.

Versions 9.0 and 9.2 are NOT slightly different.  These are two MAJOR
RELEASES (which allow for API changes) apart (i.e., one major release in
between - 9.1)

The release notes for 9.2 note this particular change explicitly:

http://www.postgresql.org/docs/9.2/interactive/release-9-2.html

Section E.9.2.6

Note the presence of the "state" column in the 9.2 schema - you use this to
determine if a connection is "idle" instead of looking for "<IDLE>" in a
query column which then allows the query column to be report the "last known
query" at all times.

David J.






--
View this message in context:
http://postgresql.1045698.n5.nabble.com/getting-the-current-query-from-pg-stat-activity-tp5798076p5798098.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.