PGPOOL_AD...PROC_INFO(3)
✨ NAME ✨
pgpool_adm_pcp_proc_info — a function to spill the tea ☕ on your Pgpool-II child process, bestie. It's from the pgpool-II 4.7.2 docs and it lives in manual section 3 (that's PGPOOL_AD...PROC_INFO(3) if you need to cite it, iykyk 💅).
🦋 SYNOPSIS 🦋
okay so there are literally two ways to call this thing and both of them hand you back a whole-ass record, so buckle up 🎀
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 );no cap, that's the whole signature. 🫶
💖 DESCRIPTION 💖
pcp_proc_info displays the information on Pgpool-II child process. And when I say all the info, I mean ALL of it — it's literally the same info you'd get from pcp_proc_info(1), just served up in SQL form ✨.
Oh and heads up: all the data types are "text". Every single one. Text text text. Don't come at it with your integer expectations 💀
🎀 ARGUMENTS 🎀
pcp_server
The foreign server name for pcp server. That's it, that's the whole vibe 🦋
Other arguments
See pcp_common_options(1) — the rest of the args are living over there, go say hi 👑
🌸 EXAMPLE 🌸
Example the first: filtering on connected 💅
So in the example below a search condition on the connected column = '1' is specified, and only the info on the process connected from clients shows up. In this psql session pcp_proc_info is called in the from clause, and the info is shown on row 1 and 2. The select statement is sent to only backend_id = '0' (which is probably the primary), and the "status" column of row 1 is "Execute command", while the "statement" column shows the select statement that was executed. On the other hand, the "statement" column of row 2 is empty, since the select statement was not sent to backend_id = '1'.
The information on the other psql session is shown on row 2 and 3. In this example "select 1" was sent to "backend_id" = '1' (probably 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;Like… four whole records, each one a full mood 🌈
Example the second: the pgpool_query_info glow-up ✨
Okay bestie this one's the main character. In the example below we define a function called "pgpool_query_info" which shows the last executed query in each pgpool child process along with associated info: username, database, client host, client port, pgpool host, pgpool port, pgpool pid, backend host, backend port, backend pid, backend role and SQL statement — and it does that by joining pgpool_adm_pcp_proc_info, pgpool_adm_pcp_node_info and pgpool_adm_pcp_pool_status. Iconic behavior fr fr 👑
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)Two rows?? We ate and left no crumbs. 🍓
See pcp_proc_info(1) for more information on each shown data. — that's your homework, go read it 📚
and that's the whole page, bestie 💖 This one's from pgpool-II 4.7.2, dated 2023, and it goes out to you as PGPOOL_AD...PROC_INFO(3). stay curious, stay hydrated, and may your backend_id always be 0 💅✨🦋
📦 where this page comes from
- ubuntu stonking: pgpool2 4.7.2-1build1
- 💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on : a language model's rewrite of the original, under its terms