[Pgpool-hackers] Admin interface, statistics views, etc

[Tatsuo Ishii]

> I've been thinking about the new release. I've always wanted to add
> something in pgAdmin that could discover the "pgpool" server. There are
> two ways to do it: with a "SHOW pool_status", and with the
> inet_server_addr() stored function. There should be a third in 9.1 with
> the pg_stat_get_backend_server_port() stored function.
> 
> I would like to see two kinds of informations: configuration (which we
> already have with the "SHOW pool_status" statement), and statistics
> (which, AFAICT, aren't available). It would be great to have something
> like pg_stat_activity but at the pgpool level. I mean, the
> pg_stat_activity stat view only display the IP address of the pgpool
> server. So, it would be great to know where the real client is.
> Something like:
> 
> SELECT * FROM pool_stat_activity;
> 
> -[ RECORD 1 ]----+--------------------------------
> pool_pid         |
> pool_id          |
> pool_start       |
> pgsql_hostname   | 127.0.0.1
> pgsql_port       | 5432
> pgsql_pid        |
> working          | f
> 
> pool_pid would be the PID of the pgPool child connected to the real
> client. pool_id would by the pool number (connectionid could be a better
> name). pool_start would be the timestamp wrt to the start of pgPool
> child process. pgsql_hostname, pgsql_port would identify the PostgreSQL
> server it is connected to. pgsql_pid would be the PID of the postgres
> backend process (this information would help to cross the information
> with pg_stat_activity). working would be a boolean value telling which
> pool_id is really working at the moment.
> 
> There could be other interesting informations as the list of servers
> (pool_stat_servers) which could tell us how many servers are available,
> which one are really available, how many queries were executed on each, etc.
> 
> Seems it would be a great project to add to the 3.0 release, don't you
> think?
> 
> Other comments?

It seems like infomation you want is almost there if you use
pcp_proc_count and pcp_proc_info:
$ pcp_proc_count 10 localhost 9898 postgres hogehoge
3815

$ pcp_proc_info 10 localhost 9898 postgres hogehoge 3815
postgres_db postgres 1150769932 1150767351 3 0 1

The result is in the following order:
1. connected database name
2. connected username
3. process start-up timestamp
4. connection created timestamp
5. protocol major version
6. protocol minor version
7. connection-reuse counter

So only missing data is pgsql_pid.  (pgsql_hostname and pgsql_port can
be obtained from pool_status)

I'm looking forward to add pgsql_pid to pcp_proc_info. What do you
think?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp