Re: How to determine number of established connections

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