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

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