Perl + Determine active connections on Pg 8.0.x and Pg 8.2.x

CAJ CAJ wrote:

Hello,

We have Pg 8.0.x and Pg 8.2.x on 2 separate Linux servers. We have a perl
script (perl 5.8.7, DBI-1.48/DBD::Pg-1.49) that connects to each
database to
determine if there are active connections.with the following query.
SELECT count(*) from pg_stat_activity;

The above query always returns 0 rows for Pg 8.0.x and 1 row for Pg 8.2.x.

When we connect to the database using psql client, the above query
returns 1
row for both the databases.

The pg_stat_activity view is never completely up-to-date, there is
always some time lag. This means whether you see your own connection
will depend on the precise order of events. Try sleep()ing for a second
before issuing the query and see if that makes it go away. If so, it's
just timing problems.

I've seen similar with "ps auxw | grep foo" from the command-line.

--
Richard Huxton
Archonet Ltd

We have Pg 8.0.x and Pg 8.2.x on 2 separate Linux servers. We have a

perl

script (perl 5.8.7, DBI-1.48/DBD::Pg-1.49) that connects to each
database to
determine if there are active connections.with the following query.
SELECT count(*) from pg_stat_activity;

The above query always returns 0 rows for Pg 8.0.x and 1 row for Pg

8.2.x.

When we connect to the database using psql client, the above query
returns 1
row for both the databases.

The pg_stat_activity view is never completely up-to-date, there is
always some time lag. This means whether you see your own connection
will depend on the precise order of events. Try sleep()ing for a second
before issuing the query and see if that makes it go away. If so, it's
just timing problems.

I've seen similar with "ps auxw | grep foo" from the command-line.

Thanks! sleep 1; works like a charm on both the databases.

Richard Huxton <dev@archonet.com> writes:

CAJ CAJ wrote:

SELECT count(*) from pg_stat_activity;

The above query always returns 0 rows for Pg 8.0.x and 1 row for Pg 8.2.x.

The pg_stat_activity view is never completely up-to-date, there is
always some time lag.

It *is* up-to-date as of 8.2, but in prior releases there can be as much
as half a second time lag.

regards, tom lane