[pgpool-hackers: 3866] Proposal: add new fields to show pool_nodes

[Tatsuo Ishii]

For upcoming Pgpool-II 4.3 I would like to propose to add new fields
to "show pool_nodes" command. Comments are welcome.

There are "status" and "role" fields: "status" represents the DB node
status stored in shared memory ("up", "down" etc), "role" represents
"primary" or "standby" (in streaming replication mode), "main" or
"replica" (in native replication or snapshot isolation mode).

The actual DB node status does not necessary match the "status"
field. For example, pcp_detach_node can set the status to "down" while
the DB node is still up and running.  Similar thing can be said to
"role" field. If there are two primaries (or non standbys) nodes
exist, Pgpool-II only shows one of them as "primary" and shows others
as "standby".  As a result, admins periodically check the actual
status/role directly on DB nodes.

So my proposal is to add following new 2 fields:

pg_status: actual DB node status. Either "up" or "down". The status is
fetched by using pg_isready command while executing show
pool_nodes. Arguments for pg_isready are obtained from health check
parameters. The reason for this choice is, pgpool already checks the
availability of PostgreSQL by using health check parameters. If health
check is disabled (health_check_period = 0), the status will be
"unknown".

pg_role: actual DB node status. Only meaningful in streaming
replication mode. Either "primary" or "standby". If
pg_is_in_recovery() returns true, it will be represented as "standby",
otherwise "primary". In other clustering modes, same as "role" field.
Arguments for querying to backend are obtained from
sr_check_parameters. The reason for this choice is, pgpool already
checks the role of PostgreSQL by using sr_check parameters. If
sr_check is disabled (sr_check_period = 0), the status will be
"unknown". For other clustering mode, value of "role" field is copied
to pg_role field.

Here is the image of new fields. On node 0 and 1, new fields
"pg_status" and "pg_role" are match with "status" and "role". On node
2 pg_status is unknown since health check is disabled. pg_role shows
"primary" although "role" is "standby". Probably our admin should
check node 2 to find out what's going on.

test=# show pool_nodes;
-[ RECORD 1 ]----------+--------------------
node_id                | 0
hostname               | /tmp
port                   | 11002
status                 | up
pg_status              | up
lb_weight              | 0.333333
role                   | primary
pg_role                | primary
select_cnt             | 0
load_balance_node      | false
replication_delay      | 0
replication_state      | 
replication_sync_state | 
last_status_change     | 2021-02-09 22:47:03
-[ RECORD 2 ]----------+--------------------
node_id                | 1
hostname               | /tmp
port                   | 11003
status                 | up
pg_status              | up
lb_weight              | 0.333333
role                   | standby
pg_role                | standby
select_cnt             | 0
load_balance_node      | true
replication_delay      | 0
replication_state      | streaming
replication_sync_state | async
last_status_change     | 2021-02-09 22:47:03
-[ RECORD 3 ]----------+--------------------
node_id                | 2
hostname               | /tmp
port                   | 11004
status                 | up
pg_status              | unknown
lb_weight              | 0.333333
role                   | standby
pg_role                | primary
select_cnt             | 0
load_balance_node      | false
replication_delay      | 0
replication_state      | streaming
replication_sync_state | async
last_status_change     | 2021-02-09 22:47:03