Thread: libpq async connection and multiple hosts

libpq async connection and multiple hosts

From
Daniele Varrazzo
Date:
Hello,

We are aware that, using async connection functions (`PQconnectStart`,
`PQconnectPoll`), the `connect_timeout` parameter is not supported;
this is documented at
https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PQCONNECTSTARTPARAMS

"""
The connect_timeout connection parameter is ignored when using
PQconnectPoll; it is the application's responsibility to decide
whether an excessive amount of time has elapsed. Otherwise,
PQconnectStart followed by a PQconnectPoll loop is equivalent to
PQconnectdb.
"""

However, ISTM that connecting to multiple hosts is not supported
either. I have a couple of issues I am looking into in psycopg 3:

- https://github.com/psycopg/psycopg/issues/602
- https://github.com/psycopg/psycopg/issues/674

Do we have to reimplement the connection attempts loop too?

Are there other policies that we would need to reimplement? Is
`target_session_attrs` taken care of by PQconnectPoll?

On my box (testing with psql and libpq itself),
PQconnect("host=8.8.8.8") fails after 2m10s. Is this the result of
some unspecified socket connection timeout on my Ubuntu machine?.

If we need to reimplement async connection to "host=X,Y", we will need
to use a timeout even if the user didn't specify one, otherwise we
will never stop the connection attempt to X and move to Y. What
timeout can we specify that will not upset anyone?

Thank you very much

-- Daniele



Re: libpq async connection and multiple hosts

From
Jelte Fennema
Date:
On Wed, 25 Oct 2023 at 17:03, Daniele Varrazzo
<daniele.varrazzo@gmail.com> wrote:
> However, ISTM that connecting to multiple hosts is not supported
> either. I have a couple of issues I am looking into in psycopg 3:
>
> - https://github.com/psycopg/psycopg/issues/602
> - https://github.com/psycopg/psycopg/issues/674

Another approach is to use tcp_user_timeout instead of connect_timeout
to skip non-responsive hosts. It's not completely equivalent though to
connection_timeout though, since it also applies when the connection
is actually being used. Also it only works on Linux afaik. It could be
nice to add support for BSD its TCP_CONNECTIONTIMEOUT socket option.

> Do we have to reimplement the connection attempts loop too?

If you want to support connection_timeout, it seems yes.

> Are there other policies that we would need to reimplement? Is
> `target_session_attrs` taken care of by PQconnectPoll?

Afaict from the code target_session_attrs are handled inside
PQconnectPoll, so you would not have to re-implement that.
PQconnectPoll would simply fail if target_session_attrs don't match
for the server. You should implement load_balance_hosts=random though
by randomizing your hosts list.



Re: libpq async connection and multiple hosts

From
Daniele Varrazzo
Date:
On Wed, 25 Oct 2023 at 17:35, Jelte Fennema <postgres@jeltef.nl> wrote:

> You should implement load_balance_hosts=random though
> by randomizing your hosts list.

Good catch. So it seems that, if someone wants to build an equivalent
an async version of PQconnectdb, they need to handle on their own:

- connect_timeout
- multiple host, hostaddr, port
- load_balance_hosts=random

Does this list sound complete?

-- Daniele



Re: libpq async connection and multiple hosts

From
Daniele Varrazzo
Date:
On Wed, 25 Oct 2023 at 17:35, Jelte Fennema <postgres@jeltef.nl> wrote:

> Another approach is to use tcp_user_timeout instead of connect_timeout
> to skip non-responsive hosts. It's not completely equivalent though to
> connection_timeout though, since it also applies when the connection
> is actually being used. Also it only works on Linux afaik. It could be
> nice to add support for BSD its TCP_CONNECTIONTIMEOUT socket option.

This seems brittle and platform-dependent enough that we would surely
receive less grief by hardcoding a default two minutes timeout.

-- Daniele



Re: libpq async connection and multiple hosts

From
Jelte Fennema
Date:
On Wed, 25 Oct 2023 at 18:54, Daniele Varrazzo
<daniele.varrazzo@gmail.com> wrote:
> - connect_timeout
> - multiple host, hostaddr, port
> - load_balance_hosts=random
>
> Does this list sound complete?

I think you'd also want to resolve the hostnames to IPs yourself and
iterate over those one-by-one. Otherwise if the first IP returned for
the hostname times out, you will never connect to the others.



Re: libpq async connection and multiple hosts

From
Daniele Varrazzo
Date:
On Thu, 26 Oct 2023, 00:10 Jelte Fennema, <postgres@jeltef.nl> wrote:
On Wed, 25 Oct 2023 at 18:54, Daniele Varrazzo
<daniele.varrazzo@gmail.com> wrote:
> - connect_timeout
> - multiple host, hostaddr, port
> - load_balance_hosts=random
>
> Does this list sound complete?

I think you'd also want to resolve the hostnames to IPs yourself and
iterate over those one-by-one. Otherwise if the first IP returned for
the hostname times out, you will never connect to the others.

For async connections we were already unpacking and processing the hosts list, in order to perform non-blocking resolution and populate the hostaddr. This already accounted for the possibility of one host resolving to more than one address. But then we would have packed everything back into a single conninfo and made a single connection attempt.


The goal here was only non-blocking name resolution. Ahaini understand we should do is to split on the hosts for sync connections too, shuffle if requested, and make separate  connection attempts.

-- Daniele

Re: libpq async connection and multiple hosts

From
Jelte Fennema
Date:
On Thu, 26 Oct 2023 at 03:31, Daniele Varrazzo
<daniele.varrazzo@gmail.com> wrote:
> The goal here was only non-blocking name resolution. Ahaini understand we should do is to split on the hosts for sync
connectionstoo, shuffle if requested, and make separate  connection attempts.
 

If you pack the resolved addresses in same connection string then it
should be fine. The different hostaddrs will be shuffled by libpq.