pgpool-II 4.7.2 Documentation
Prev | Up |  | Next

# pgpool_adm_pcp_proc_info

## 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. All the data types are "text".

## Arguments

`pcp_server`

  The foreign server name for pcp server.

`Other arguments`

  See pcp_common_options.

## 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).

    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;

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 for more information on each shown data.

Prev | Home | Next
pgpool_adm_pcp_detach_node | Up | Appendixes