Обсуждение: Perl + Determine active connections on Pg 8.0.x and Pg 8.2.x

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.

Does anyone has an idea what could be the issue? Is there any other way to determine active connections in the database?

Thanks for all the help!

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

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