Thread: pg_stat_activity howto
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
jo <jose.soares@sferacarta.com> wrote: > 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? Those are idle connections which have not started a query in 14 days. The safest way to close them is for the client which opened them to do so, although I'm not sure why you feel it is important. > 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? If a query is currently running, you will see the start of it. If no query is running on a connection, the connection will show '<IDLE>' or '<IDLE> in transaction' depending on whether the client has started a database transaction on that connection. While a transaction remains open it can interfere with the work of other connections, including some important maintenance activities (particularly VACUUM), so if connections linger in '<IDLE> in transaction' state for a long time, you should investigate. It is normally a client-side programming bug. > 3) Sometimes the xact_start column show a timestamp and sometimes > no. what this mean? If a transaction isn't active on a connection, transaction start time is not applicable. > 4) whay the waiting column is all the time False even when the > query seems in transaction? This flag indicates whether the connection is waiting on a lock held by some other PostgreSQL process. > 5) what's the difference between query_start and backend_start? Each connection has its own back end process, so the former indicates the last time a query started on the connection, which the latter indicates when the connection was made. -Kevin
Kevin Grittner wrote: > If a query is currently running, you will see the start of it. If no > query is running on a connection, the connection will show '<IDLE>' or > '<IDLE> in transaction' depending on whether the client has started a > database transaction on that connection. While a transaction remains > open it can interfere with the work of other connections, including > some important maintenance activities (particularly VACUUM), so if > connections linger in '<IDLE> in transaction' state for a long time, > you should investigate. It is normally a client-side programming bug. hi, that's a worry :) i have a pygresql-using user application (postgres-8.3.7, python-2.6, pygresql-3.8.1) that is always idle in transaction when it is idle. upon connecting, it calls a few database functions, committing after each one. after that, it's idle in transaction until the next bout of db activity. can anyone explain what i'm doing wrong in the attached example? cheers, raf
Attachment
raf <raf@raf.org> wrote: > can anyone explain what i'm doing wrong in the attached example? Does it get better if you close that cursor within the function? -Kevin
Kevin Grittner wrote: > raf <raf@raf.org> wrote: > > > can anyone explain what i'm doing wrong in the attached example? > > Does it get better if you close that cursor within the function? no. that must automatically happen when the cursor object is destroyed. but i tested it and there was no change. > -Kevin > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin
Christoph Zwerschke wrote: > raf schrieb: > >that's a worry :) i have a pygresql-using user application > >(postgres-8.3.7, python-2.6, pygresql-3.8.1) that is always > >idle in transaction when it is idle. upon connecting, it > >calls a few database functions, committing after each one. > >after that, it's idle in transaction until the next bout of > >db activity. > > > >can anyone explain what i'm doing wrong in the attached example? > > One thing is that you should always explicitely close cursors and > connections when you do not need them any more. the cursor should close automatically when the python object is destroyed. and i need the db connection later so closing that isn't efficient. > But the real cause of your problem is that Pygres 3.8.1 used to > implicitely start a connection after every commit(). I have improved > this already 3 years ago. Please use the current version Pygres 4.0. ah, so that's the reason. ok, now i have another reason to upgrade. > -- Christoph thanks, raf