select count(*) from pg_stat_activit y in V8.0.0

[Anony Mous]

Coming off of 7.4.3 I've always used

select count(*) from pg_stat_activit y

through the ODBC driver to obtain the number of connections to the db. I've
found that with v8.0 Beta 1 that this at times has returned a value of 0
(when there is obviously still a connection). It's not repeatable, and
occurs sporadically. Does anyone have any reason as to why this would be
isolated to V8? Is this an acceptable means of obtaining the number of
connection to the database in V8 (via ODBC).

Thanks so much,
Peter
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Richard Huxton]

Anony Mous wrote:

Coming off of 7.4.3 I've always used

select count(*) from pg_stat_activit y

through the ODBC driver to obtain the number of connections to the db. I've
found that with v8.0 Beta 1 that this at times has returned a value of 0
(when there is obviously still a connection). It's not repeatable, and
occurs sporadically. Does anyone have any reason as to why this would be
isolated to V8? Is this an acceptable means of obtaining the number of
connection to the database in V8 (via ODBC).

Do you have multiple live connections? My understanding of
pg_stat_activit y is that it can lag slightly behind the current state of
the system. If the only connection that is active is the one doing the
counting you could be checking before the table has been updated.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Tom Lane]

Richard Huxton <de*@archonet.c om> writes:

Anony Mous wrote:

select count(*) from pg_stat_activit y

Do you have multiple live connections? My understanding of
pg_stat_activit y is that it can lag slightly behind the current state of
the system.

If memory serves, it's up to 500 msec behind. So this technique has
never been 100% accurate. I'm not sure why A.Mous failed to observe the
inaccuracy before ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Anony Mous]

Thanks, Richard.

I've never seen this behaviour before in 7.4.3 and indeed it is the only
connection to the back end at the time when the count is occurring. However,
it would have had the connection for at least 30 seconds before requesting a
count.

Is there a better method of obtaining the number of connections to the
backend?

Thanks
Peter

-----Original Message-----
From: Richard Huxton [mailto:de*@arch onet.com]
Sent: August 19, 2004 2:21 AM
To: Anony Mous
Cc: pg***********@p ostgresql.org
Subject: Re: [GENERAL] select count(*) from pg_stat_activit y in V8.0.0

Anony Mous wrote:

Coming off of 7.4.3 I've always used

select count(*) from pg_stat_activit y

through the ODBC driver to obtain the number of connections to the db. I've found that with v8.0 Beta 1 that this at times has returned a value of 0
(when there is obviously still a connection). It's not repeatable, and
occurs sporadically. Does anyone have any reason as to why this would be
isolated to V8? Is this an acceptable means of obtaining the number of
connection to the database in V8 (via ODBC).

Do you have multiple live connections? My understanding of
pg_stat_activit y is that it can lag slightly behind the current state of
the system. If the only connection that is active is the one doing the
counting you could be checking before the table has been updated.

--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Richard Huxton]

Anony Mous wrote:

Thanks, Richard.

I've never seen this behaviour before in 7.4.3 and indeed it is the only
connection to the back end at the time when the count is occurring. However,
it would have had the connection for at least 30 seconds before requesting a
count.

Is there a better method of obtaining the number of connections to the
backend?

Other than "ps" - no I don't think so. The 30 seconds doesn't sound
right, Tom reckoned 0.5sec which sounds plausible. Might this be the
first query you've sent on this connection?

I'm wondering if the ODBC connection isn't actually established until
you run the first query? It doesn't sound right to me, but I don't have
any of my ODBC apps to hand at the moment to check.

Could you:
1. Connect
2. SELECT 1; (or similar)
3. Sleep from 10msec => 1sec
4. SELECT * FROM pg_stat_activit y;

That way we could disable step 2 and tinker with the delay in step 3 to
see what happens.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[peter]

Thanks, Richard.

I've never seen this behaviour before in 7.4.3 and indeed it is the only
connection to the back end at the time when the count is occurring.
However, it would have had the connection for at least 30 seconds before
requesting a count.

Is there a better method of obtaining the number of connections to the
backend?

Thanks
Peter

-----Original Message-----
From: Richard Huxton [mailto:de*@arch onet.com]
Sent: August 19, 2004 2:21 AM
To: Anony Mous
Cc: pg***********@p ostgresql.org
Subject: Re: [GENERAL] select count(*) from pg_stat_activit y in V8.0.0

Anony Mous wrote:

Coming off of 7.4.3 I've always used

select count(*) from pg_stat_activit y

through the ODBC driver to obtain the number of connections to the db. I've found that with v8.0 Beta 1 that this at times has returned a value of 0
(when there is obviously still a connection). It's not repeatable, and
occurs sporadically. Does anyone have any reason as to why this would be
isolated to V8? Is this an acceptable means of obtaining the number of
connection to the database in V8 (via ODBC).

Do you have multiple live connections? My understanding of
pg_stat_activit y is that it can lag slightly behind the current state of
the system. If the only connection that is active is the one doing the
counting you could be checking before the table has been updated.

--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)