pg_stat_activity howto - Mailing list pgsql-admin

Hi all,


I'm using PostgreSQL-8.3 and I have some difficulty to understand how
pg_stat_activity works.

1) there are some queries running for 14 days, I tried to delete them
without success.
    why they are there for 14 days? what are they doing? is there a way
to remove them safely?

2) some times the current_query column show: "<IDLE>", sometimes show:
"<IDLE> in transaction" and sometimes it show the query string.
   what those values mean?

3) Sometimes the xact_start column show a timestamp and sometimes no.
   what this mean?

4) whay the waiting column is all the time False even when the query
seems in transaction?

5) what's the difference between query_start and backend_start?

--------------

#> select current_query, waiting,xact_start,query_start,
current_timestamp-query_start as time_running from pg_stat_activity;

    current_query    |waiting|            xact_start
|            query_start           |   time_running

---------------------+-------+-----------------------------------+----------------------------------+----------------------------------
<IDLE> in transaction|f      |Wed 04 Nov 14:43:35.865205 2009 CET|Wed 04
Nov 14:43:35.86587 2009 CET| 4 mins 11.70 secs
<IDLE> in transaction|f      |Wed 04 Nov 14:43:35.861757 2009 CET|Wed 04
Nov 14:43:35.86230 2009 CET| 4 mins 11.70 secs
<IDLE>               |f      |                                   |Wed 04
Nov 14:43:35.85090 2009 CET| 4 mins 11.71 secs
<IDLE> in transaction|f      |Wed 04 Nov 14:43:33.603011 2009 CET|Wed 04
Nov 14:43:33.60357 2009 CET| 4 mins 13.96 secs
<IDLE> in transaction|f      |Wed 04 Nov 14:43:33.589402 2009 CET|Wed 04
Nov 14:43:33.58982 2009 CET| 4 mins 13.97 secs
<IDLE> in transaction|f      |Wed 04 Nov 14:43:30.909994 2009 CET|Wed 04
Nov 14:43:30.91038 2009 CET| 4 mins 16.65 secs
<IDLE> in transaction|f      |Wed 04 Nov 14:43:27.815111 2009 CET|Wed 04
Nov 14:43:27.81546 2009 CET| 4 mins 19.75 secs
<IDLE> in transaction|f      |Wed 04 Nov 14:43:23.878521 2009 CET|Wed 04
Nov 14:43:23.87900 2009 CET| 4 mins 23.68 secs
<IDLE>               |f      |                                   |Wed 04
Nov 13:47:40.11600 2009 CET| 1 hour 7.45 secs
<IDLE>               |f      |                                   |Wed 04
Nov 13:26:58.19252 2009 CET| 1 hour  20 mins 49.37 secs
<IDLE>               |f      |                                   |Wed 04
Nov 09:01:54.43045 2009 CET| 5 hours 45 mins 53.13 secs
<IDLE>               |f      |                                   |Wed 04
Nov 08:22:52.56998 2009 CET| 6 hours 24 mins 54.99 secs
<IDLE>               |f      |                                   |Wed 04
Nov 08:22:39.83344 2009 CET| 6 hours 25 mins 7.73 secs
<IDLE>               |f      |                                   |Tue 03
Nov 18:28:34.52738 2009 CET|20 hours 19 mins 13.03 secs
<IDLE>               |f      |                                   |Tue 03
Nov 12:07:53.78074 2009 CET| 1 day  2 hours 39 mins 53.78 secs
<IDLE>               |f      |                                   |Mon 26
Oct 05:29:50.24982 2009 CET| 9 days 9 hours 17 mins 57.31 secs
<IDLE>               |f      |                                   |Mon 26
Oct 05:29:50.06615 2009 CET| 9 days 9 hours 17 mins 57.50 secs
<IDLE>               |f      |                                   |Wed 21
Oct 15:09:18.90364 2009 CET|14 days 38 mins 28.66 secs
<IDLE>               |f      |                                   |Wed 21
Oct 15:08:53.57032 2009 CET|14 days 38 mins 53.99 secs

---------------------+-------+-----------------------------------+----------------------------------+----------------------------------

thanks for any help.

jo