Thread: 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