pcp_proc_info returns record(text host, integer port, text username, text password, out database text, out username text, out start_time text, out client_connection_count text, out major text, out minor text, out backend_connection_time text, out client_connection_time text, out client_idle_duration text, out client_disconnection_time text, out pool_counter text, out backend_pid text, out connected text, out pid text, out backend_id text, out status text, out load_balance_node text, out client_host text, out client_port text, out statement );
pcp_proc_info returns record(integer node_id, text pcp_server, out database text, out username text, out start_time text, out client_connection_count text, out major text, out minor text, out backend_connection_time text, out client_connection_time text, out client_idle_duration text, out client_disconnection_time text, out pool_counter text, out backend_pid text, out connected text, out pid text, out backend_id text, out status text, out load_balance_node text, out client_host text, out client_port text, out statement );
以下の例では、検索条件で"connected"列 = '1'を指定して、現在クライアントから接続のあるプロセス情報のみを表示しています。 このpsqlセッションでfrom pcp_proc_infoを呼び出していて、その情報は行1と2に表示されています。 select文はbackend_id = '0'（おそらくprimaryです)にのみ送信されているので、行1の"status"列は"Execute command"となっており、"statement"列には実行したselect文が表示されていますが、backend_id = '1'にはselect文が送信されていないので、行2のstatementは空白となっています。
別psqlセッションの情報は行2, 3に表示されています。 この例では"select 1"は"backend_id" = '1'（おそらくstandby)に送信されています。
test=# select * from pcp_proc_info(host => '', port => 11001, username => 't-ishii', password => 't-ishii') where connected = '1'; -[ RECORD 1 ]-------------+---------------------------------------------------------------------------------------------------------------------------- database | test username | t-ishii start_time | 2025-02-22 20:56:08 client_connection_count | 0 major | 3 minor | 0 backend_connection_time | 2025-02-22 20:58:37 client_connection_time | 2025-02-22 20:58:37 client_idle_duration | 0 client_disconnection_time | pool_counter | 1 backend_pid | 14750 connected | 1 pid | 14585 backend_id | 0 status | Execute command load_balance_node | 1 client_host | 127.0.0.1 client_port | 59120 statement | select * from pcp_proc_info(host => '', port => 11001, username => 't-ishii', password => 't-ishii') where connected = '1'; -[ RECORD 2 ]-------------+---------------------------------------------------------------------------------------------------------------------------- database | test username | t-ishii start_time | 2025-02-22 20:56:08 client_connection_count | 0 major | 3 minor | 0 backend_connection_time | 2025-02-22 20:58:37 client_connection_time | 2025-02-22 20:58:37 client_idle_duration | 0 client_disconnection_time | pool_counter | 1 backend_pid | 14751 connected | 1 pid | 14585 backend_id | 1 status | Execute command load_balance_node | 0 client_host | 127.0.0.1 client_port | 59120 statement | -[ RECORD 3 ]-------------+---------------------------------------------------------------------------------------------------------------------------- database | test username | t-ishii start_time | 2025-02-22 20:56:08 client_connection_count | 0 major | 3 minor | 0 backend_connection_time | 2025-02-22 20:58:56 client_connection_time | 2025-02-22 20:58:56 client_idle_duration | 0 client_disconnection_time | pool_counter | 1 backend_pid | 14767 connected | 1 pid | 14601 backend_id | 0 status | Idle load_balance_node | 0 client_host | 127.0.0.1 client_port | 54072 statement | -[ RECORD 4 ]-------------+---------------------------------------------------------------------------------------------------------------------------- database | test username | t-ishii start_time | 2025-02-22 20:56:08 client_connection_count | 0 major | 3 minor | 0 backend_connection_time | 2025-02-22 20:58:56 client_connection_time | 2025-02-22 20:58:56 client_idle_duration | 0 client_disconnection_time | pool_counter | 1 backend_pid | 14768 connected | 1 pid | 14601 backend_id | 1 status | Idle load_balance_node | 1 client_host | 127.0.0.1 client_port | 54072 statement | select 1;
次の例では、"pgpool_query_info"という関数を登録し、pgpool_adm_pcp_proc_info、pgpool_adm_pcp_node_info、pgpool_adm_pcp_pool_statusを結合してpgpoolの子プロセスが最後に実行したクエリを、次の情報と一緒に表示します。 usernae, database, client host, client port, pgpool host, pgpool port, pgpool pid, backend host, backend port, backend pid, backend role and SQL statement。
create function pgpool_query_info(pcp_host text, pcp_port int, pgpool_username text, password text)
returns table (username text, database text, client_host text, client_port text,
pgpool_host text, pgpool_port text, pgpool_pid text,
backend_host text, backend_port text, backend_pid text, backend_role text, statement text) as $$
select p.username, p.database, p.client_host,p.client_port,
$1 as pgpool_host, s.value as pgpool_port, p.pid as pgpool_pid,
b.host as backend_host, b.port as backend_port, p.backend_pid as backend_pid,
b.role as backend_role,
p.statement
from pcp_proc_info(host=> $1, port => $2, username => $3, password => $4) as p,
pcp_node_info(node_id=>p.backend_id::int, host => $1, port => $2,
username => $3, password => $4) as b,
pcp_pool_status(host=> $1, port => $2, username => $3, password => $4) as s
where p.connected = 1::text and p.statement != '' and
p.statement !~ 'pcp_proc_info' and p.statement !~ 'pgpool_query_info' and
s.item = 'port'
$$
language SQL;
CREATE FUNCTION
select * from pgpool_query_info('192.168.10.11', 11001, 't-ishii', 't-ishii');
username | database | client_host | client_port | pgpool_host | pgpool_port | pgpool_pid | backend_host | backend_port | backend_pid | backend_role | statement
----------+----------+---------------+-------------+---------------+-------------+------------+--------------+--------------+-------------+--------------+-----------
t-ishii | test | 192.168.10.11 | 60542 | 192.168.10.11 | 11000 | 1356156 | localhost | 11002 | 1356240 | Primary | select 2;
t-ishii | test | 192.168.10.11 | 43856 | 192.168.10.11 | 11000 | 1356167 | localhost | 11003 | 1356255 | Standby | select 1;
(2 rows)
表示データの詳細に関してはpcp_proc_infoをご覧ください。