Обсуждение: How to determine number of established connections

When connectiong to database my application needs to determine how many
connections is currently established to Postgres server.

Any idea which query I should use ?

I can create temporary table at startup of each connection. But how to count
the number of temporary tables created by all applications ?


Using Postgres 8.1

Andrus

On Mon, Feb 20, 2006 at 07:39:55PM +0200, Andrus wrote:
> When connectiong to database my application needs to determine how many
> connections is currently established to Postgres server.

You could query pg_stat_activity or the underlying statistics
collector functions.

http://www.postgresql.org/docs/8.1/interactive/monitoring-stats.html

Statistics collector reports lag behind actual activity but they
might suffice for whatever you're doing.  Why does your application
need to know this?

--
Michael Fuhr

am  20.02.2006, um 19:39:55 +0200 mailte Andrus folgendes:
> When connectiong to database my application needs to determine how many
> connections is currently established to Postgres server.

select * from pg_stat_activity ;


> I can create temporary table at startup of each connection. But how to count
> the number of temporary tables created by all applications ?

Why do you need this information?


HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

> You could query pg_stat_activity or the underlying statistics
> collector functions.
>
> http://www.postgresql.org/docs/8.1/interactive/monitoring-stats.html
>
> Statistics collector reports lag behind actual activity but they
> might suffice for whatever you're doing.  Why does your application
> need to know this?

I want to licence my application per-connection basic.
I can assume that Postgres 8.1 database or even a whole cluster is accessed
only by my application or sometimes by pgAdmin for administration.
pgAdmin creates 3 rows in pg_stat_activity  table. My application uses only
a single connection so it creates single row in pg_stat_activity table.

So I have a parameter stored in a database called "maximum number of
simultaneous connections".
This determines how many users can simultaneously work with my application


Is the query

select count(distinct client_addr)
from pg_stat_activity

best way do obtain this parameter from Postgres 8.1 ?

or should it be better to check only single database:

select count(distinct client_addr)
from pg_stat_activity
where datname='mycurrentdatabase'

Will those queries work with any postgres.conf and other postgres config
settings and with any user access rights ?

Andrus.

On Mon, Feb 20, 2006 at 09:33:57PM +0200, Andrus wrote:
> I want to licence my application per-connection basic.

Ugh.  A lot of people here aren't going to be interested in helping
with that.

> I can assume that Postgres 8.1 database or even a whole cluster is accessed
> only by my application or sometimes by pgAdmin for administration.
> pgAdmin creates 3 rows in pg_stat_activity  table. My application uses only
> a single connection so it creates single row in pg_stat_activity table.
>
> So I have a parameter stored in a database called "maximum number of
> simultaneous connections".
> This determines how many users can simultaneously work with my application

Will the end users have control over the database (DBA privileges
or superuser/admin privileges on the server)?  If so then any
license-enforcing mechanisms that depend on the database could be
easily rendered useless.

Some things you might want to look at are role and database connection
limits (see the documentation for CREATE/ALTER ROLE/DATABASE) and
the max_connections setting in postgresql.conf.  However, as I
mentioned above, if the users have control over the database then
those settings won't enforce anything for very long.

> Is the query
>
> select count(distinct client_addr)
> from pg_stat_activity
>
> best way do obtain this parameter from Postgres 8.1 ?

Not in general.  Local connections (those made over Unix sockets)
have a null client_addr, and a single IP address could be the source
of multiple network connections from multiple users (think about
multiuser systems, address translation, etc.).  You'll have to
consider those possibilities when deciding what meaning the above
query has.

Something else to consider is the stats collector's lag time.
Several connections made at the same time might all see zero relevant
records in pg_stat_activity, so they'd get an inaccurate count.

--
Michael Fuhr