[pgpool-general: 3058] Re: PGPool connection dropped when using pg_detach

[Tatsuo Ishii]

> Hey all,
> 
> This is a seemingly a pretty bad problem which I uncovered as part of my
> last post, so the start of the message will be similar.
> 
> I have two pgpool nodes which I am using a TCP load balancer to spread
> between. I am using watchdog to synchronise PostgreSQL node information
> between the two and an external HA solution (with ALLOW_TO_FAILOVER).
> 
> If I start both my pgpool nodes up I get the following initial state:
> 
> postgres=# show pool_nodes;
>  node_id |  hostname   | port | status | lb_weight |  role
> ---------+-------------+------+--------+-----------+---------
>  0       | 10.10.10.1   | 5432 | 2      | 0.500000  | standby
>  1       | 10.10.10.2   | 5432 | 2      | 0.500000  | primary
> (2 rows)
> 
> Now I open a PSQL connection and do the following:
> 
> postgres=# SELECT inet_server_addr();
>  inet_server_addr
> ------------------
>  10.10.10.2
> (1 row)
> 
> This shows I am connected to the primary.
> 
> I can run this multiple times and I will always be connected to the
> primary, as long as I don't close the psql session.
> 
> Then from another window I run the following command:
> 
>  pcp_detach_node 1 load_balancer 9898 postgres postgres 0
> 
> And in the same PSQL session run the command again:
> 
> postgres=# SELECT inet_server_addr();
> SSL SYSCALL error: EOF detected
> The connection to the server was lost. Attempting reset: Succeeded.
> 
> This is strange. Why has my master connection been severed?

It's an expected behavior of pcp_detach_node, which causes failover
and all existing sessions are disconnected.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp