[pgpool-hackers: 3489] connecting to IPv4 or IPv6 address

[Tatsuo Ishii]

Hi Pgpool-II developers,

Recently I received an interesting issue report:
https://www.pgpool.net/mantisbt/view.php?id=576

The interesting point is he is getting following error from health
check process:

Jan 16 17:28:42 debian10 pgpool[10477]: 2020-01-16 17:28:42: pid 10523: LOG: failed to connect to PostgreSQL server on "pg2:5433", getsockopt() detected error "Connection refused"
Jan 16 17:28:42 debian10 pgpool[10477]: 2020-01-16 17:28:42: pid 10523: LOCATION: pool_connection_pool.c:680

But health check seems to run fine and no retry or failover was
performed. I dig into the source code and came to a theory:

1) make_persistent_db_connection() calls connect_inet_domain_socket_by_port().

2) connect_inet_domain_socket_by_port() calls getaddrinfo() to get
   necessary data to connect to backend.

3) getaddrinfo() happily returns multiple info if specified hostname
   is resolved to multiple IP, IPv4 and IPv6 for example.

4) connect_inet_domain_socket_by_port() calls connect_with_timeout()
   with one of the IPs.

5) For some reason backend accepts IPv4 address but does not accept
   IPv6 address.

6) connect() gets called from connect_with_timeout() and it fails to
   connect to IPv6 address.

7) The error from getsockopt() is logged.

8) connect_with_timeout() reports the failure to
   connect_inet_domain_socket_by_port() and it tries to use IPv4
   address next time.

9) connect_with_timeout() succeeds to connect to the IPv4
   address. health check succeeds.

Existing log does not help to know that above scenario actually
happens. So I would like to propose attached patch to enhance logging
to know which IP address caused the error.

In the mean time I wonder if we could avoid the annoying error. I
think:

1) let user's PostgreSQL accept both IPv4 and IPv6 address.

2) add new parameter to specify which IP (or v4 and v6) to be used for
   connecting to PostgreSQL.

3) other (leave it as it is now etc.)

I am afraid that users are not welcome #1 for various reasons
(e.g. security). #2 is doable but I am not sure it's worth the
trouble.

What do you think?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
-------------- next part --------------
A non-text attachment was scrubbed...
Name: connect_error_log.diff
Type: text/x-patch
Size: 3109 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200118/3d2050ef/attachment.bin>