No, I don't believe it has anything to do with the time delay. We've done
random spot checks in the past (select count(*) from pg_stat_activity) and
it's reported zero connections (and obviously, we're connected at the time
of the query). This query was issued in a VB app and delivered via ODBC,
long after the original connection was established. Although I have no
evidence of it, I believe it's also reported zero connections when I was 99%
certain that there were at least 3 client connections.
I agree that clients come and go and therefore the number of connections
reported may be misleading, however, this is not a high volume app and most
connections often persist for an hour or so. Our app needs to make a
decision during a maintenance routine based on whether or not there are
clients connected to the database. We have a server side app that is always
connected, so when the number of connections is reported as zero, I have a
dilemma because I know that this is impossible (since the query was issued
to the postmaster in the first place).
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: May 5, 2005 11:22 AM
To: A. Mous
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Postmaster not reporting number of connections
correctly
"A. Mous" <a.mous@shaw.ca> writes:
> I've been using 8.0 ever since beta 1, and am currently on 8.0.1. I have
> noticed (in each version) that the number of records in the
pg_stat_activity
> table is not always representative of the number of connections to the
> server. That is, we have an installation at a client's site that runs a
> nightly batch and reports the results of the run back to us. One of the
> items reported is a count of the number of records in pg_stat_activity (a
> section of the batch is dependent on whether or not any client connections
> exist). Every now and then we'll see that the number of records in this
> table is zero, when there should always be at least one since our batch is
> connected when it runs. I've also seen this myself when querying the
table
> (zero reported connections).
Is this being checked within a short time after connection? The stats
views lag behind reality (by up to 500ms, if memory serves).
> Is there a better way of obtaining the number of connections to the
> postmaster?
I think it's a bad idea to imagine that you could ever know that with
certainty --- even if the number is correct when obtained, it could well
be obsolete by the time it's delivered to the client.
regards, tom lane
