[pgpool-hackers: 4335] Avoiding unnecessary session disconnection

[Tatsuo Ishii]

Hi Pgpool-II developers,

In streaming replication mode, when a standby goes down (and triggers
a failover), already connected clients are supposed to not be affected
(no session disconnection) if:

- the client has already connected to pgpool
- the standby is not the load balance node in the session

This means that if backend weight for the standby is 0 or
load_balance_mode is off, the already connected clients should not be
affected by the failover. Unfortunately this is not true because
VALID_BACKEND macro (it calls pool_virtual_main_db_node_id()) exits
the process if it finds that a failover is ongoing.

https://www.pgpool.net/pipermail/pgpool-general/2023-April/008791.html

So I came up with a patch in the mail:

https://www.pgpool.net/pipermail/pgpool-general/2023-April/008795.html

With the patch pool_virtual_main_db_node_id() waits until the failover
event finishes (if it does not finish within 30 seconds, the process
exits). So unless the failover takes longer than 30 seconds,
pool_virtual_main_db_node_id() returns normally and the session is not
aborted. So far so good.

So what happens in more general use case? I mean if clients repeat
connection and disconnection to pgpool, does the patch work?
Unfortunately no. When a client connects to pgpool, followings happen:

1. When client connects to pgpool, pgpool tries to connect to standby
   PostgreSQL.

2. When client connects to pgpool, pgpool sends SET application_name
   command.

3. Pgpool detects the PostgreSQL shutdown event even if it's from
   standby, which results in session disconnection.

To make pgpool work in the scenario, we need to find a way how to deal
with the issues above.

Avoiding session disconnections is one of the most frequent requests
to pgpool for long time. So I would like to work on this for upcoming
Pgpool-II 4.5. If you have nice idea for this, please share it!

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp