Man page - pgpool_adm_pcp_proc_info(3)

Packages contains this manual

Package: pgpool2
apt-get install pgpool2

Manuals in package:

Documentations in package:

pgpool2

Manual

PGPOOL_ADM_PCP_PROC_INFO

NAME
SYNOPSIS
DESCRIPTION
ARGUMENTS
EXAMPLE

NAME

pgpool_adm_pcp_proc_info - a function to display the information on Pgpool-II child process

SYNOPSIS

	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 );

DESCRIPTION

pcp_proc_info displays the information on Pgpool-II child process. All the information is same as pcp_proc_info (1). All the data types are "text".

ARGUMENTS

pcp_server

The foreign server name for pcp server.

Other arguments

See pcp_common_options (1).

EXAMPLE

In the example below a search condition "connected" column = '1' is specified, and only the information on the process connected from clients. In this psql session pcp_proc_info is called in the from clause, and the information 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 the row 1 is "Execute command", the "statement" column shows the select statement which was executed. On the other hand the "statement" column of the row 2 is empty, since the select statement is was not sent to backend_id = '1'.

The information on the other psql session is shown on the row 2 and 3. In this example "select 1" was sent to "backend_id" = '1' (probably standby).

In the example below we define a function called "pgpool_query_info" which shows last executed query in each pgpool child process along with associated information: usernae, database, client host, client port, pgpool host, pgpool port, pgpool pid, backend host, backend port, backend pid, backend role and SQL statement, by joining pgpool_adm_pcp_proc_info, pgpool_adm_pcp_node_info and pgpool_adm_pcp_pool_status.

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)

See pcp_proc_info (1) for more information on each shown data.