Handling of hosts resolving to multiple IPs

[dvarrazzo]

Looking at libpq code, it seems that it resolves a hostname to all the ip addresses and then iterates on them.

In order to fix #674 we have to iterate on multiple hosts manually and move on after a timeout. As a consequence I think we should treat a hostname with multiple ips the same way, hence do hostname resolution in Python as we do in the async code.

We should make sure that we are able to do it properly and not having problems as reported in #695 (which hopefully was just a silly error already fixed).

I still heed to put together a test to prove that we have the issue.

[ilmarkerm]

I was about to log a new bug, but probably it falls under this issue. I see one issue with DNS name resolving to multiple IP-s should have been fixed with 3.1.13, but even with 3.1.16 I run into the following problem:

I have DNS name devmt1pgdb1.example.org that countains both PostgreSQL backends, devmt1pgdb1-1.example.org and devmt1pgdb1-2.example.org are the individual hosts.

$ host devmt1pgdb1.example.org
devmt1pgdb1.example.org has address 10.80.129.162
devmt1pgdb1.example.org has address 10.80.129.192
$ host devmt1pgdb1-1.example.org
devmt1pgdb1-1.example.org has address 10.80.129.192
$ host devmt1pgdb1-2.example.org
devmt1pgdb1-2.example.org has address 10.80.129.162

Database post 6432 (actually pgbouncer) only listens on one of the backend hosts:

$ nc -zv devmt1pgdb1-1.example.org 6432
nc: connect to devmt1pgdb1-1.example.org (10.80.129.192) port 6432 (tcp) failed: Connection refused
$ nc -zv devmt1pgdb1-2.example.org 6432
Connection to devmt1pgdb1-2.example.org (10.80.129.162) 6432 port [tcp/pgbouncer] succeeded!

When connecting using psycopg 3.1.16 using host=devmt1pgdb1.example.org connection fails intemittently, while using host=devmt1pgdb1-1.example.org,devmt1pgdb1-2.example.org the connection always succeeds.
This problem is not present when using psycopg2 or any C program.

Test code:

import psycopg, psycopg2
from time import sleep

tested_connection_strings = [
    "host=devmt1pgdb1.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require",
    "host=devmt1pgdb1-1.example.org,devmt1pgdb1-2.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require"
]


def connect_psycopg3(dsn):
    return psycopg.connect(dsn)

def connect_psycopg2(dsn):
    return psycopg2.connect(dsn)

print(f"psycopg version: {psycopg.__version__}")
print(f"psycopg2 version: {psycopg2.__version__}")
print()

for driver in ["psycopg3","psycopg2"]:
    print(f"Testing driver: {driver}")
    for dsn in tested_connection_strings:
        for i in range(5):
            print(f"Testing \"{dsn}\": ", end='')
            try:
                if driver == "psycopg3":
                    db = connect_psycopg3(dsn)
                else:
                    db = connect_psycopg2(dsn)
                db.close()
                print("ok")
            except Exception as e:
                print("failed")
                print(str(e))
            sleep(0.5)

Output:

psycopg version: 3.1.16
psycopg2 version: 2.9.9 (dt dec pq3 ext lo64)

Testing driver: psycopg3
Testing "host=devmt1pgdb1.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require": ok
Testing "host=devmt1pgdb1.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require": failed
connection failed: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
Testing "host=devmt1pgdb1.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require": ok
Testing "host=devmt1pgdb1.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require": failed
connection failed: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
Testing "host=devmt1pgdb1.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require": failed
connection failed: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
Testing "host=devmt1pgdb1-1.example.org,devmt1pgdb1-2.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require": ok
Testing "host=devmt1pgdb1-1.example.org,devmt1pgdb1-2.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require": ok
Testing "host=devmt1pgdb1-1.example.org,devmt1pgdb1-2.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require": ok
Testing "host=devmt1pgdb1-1.example.org,devmt1pgdb1-2.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require": ok
Testing "host=devmt1pgdb1-1.example.org,devmt1pgdb1-2.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require": ok
Testing driver: psycopg2
Testing "host=devmt1pgdb1.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require": ok
Testing "host=devmt1pgdb1.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require": ok
Testing "host=devmt1pgdb1.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require": ok
Testing "host=devmt1pgdb1.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require": ok
Testing "host=devmt1pgdb1.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require": ok
Testing "host=devmt1pgdb1-1.example.org,devmt1pgdb1-2.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require": ok
Testing "host=devmt1pgdb1-1.example.org,devmt1pgdb1-2.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require": ok
Testing "host=devmt1pgdb1-1.example.org,devmt1pgdb1-2.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require": ok
Testing "host=devmt1pgdb1-1.example.org,devmt1pgdb1-2.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require": ok
Testing "host=devmt1pgdb1-1.example.org,devmt1pgdb1-2.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require": ok

[dvarrazzo]

Yes, it seems the problem I want to solve here.

the libpq async connection path (which we use in both sync and async connections) doesn't take into account connect_timeout. As a consequence it cannot give up on the first ip address and try the second. As a consequence we have to never trigger the libpq reties code.

we take it in consideration when using a list of hosts:

psycopg/psycopg/psycopg/conninfo.py

Lines 306 to 309 in 0f538c1

	attempts = _split_attempts(params)
	if _get_param(params, "load_balance_hosts") == "random":
	shuffle(attempts)
	return attempts

But if one host resolves to more than one IP address, the libpq will split it into attempts, and will never move to the second.

In async connection, as a side effect of async name resolution we actually iterate on the different attempts:

psycopg/psycopg/psycopg/conninfo.py

Lines 326 to 343 in 0f538c1

	last_exc = None
	attempts = []
	for attempt in _split_attempts(params):
	try:
	attempts.extend(await _resolve_hostnames(attempt))
	except OSError as ex:
	logger.debug("failed to resolve host %r: %s", attempt.get("host"), str(ex))
	last_exc = ex
	if not attempts:
	assert last_exc
	# We couldn't resolve anything
	raise e.OperationalError(str(last_exc))
	if _get_param(params, "load_balance_hosts") == "random":
	shuffle(attempts)
	return attempts

So, if you try your script with a psycopg 3 AsyncConnection, it should work. It would be an useful test to perform.

[ilmarkerm]

Same test, but using psycopg.AsyncConnection.connect() works.

import psycopg, psycopg2
from time import sleep

tested_connection_strings = [
    "host=devmt1pgdb1.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require",
    "host=devmt1pgdb1-1.example.org,devmt1pgdb1-2.example.org port=6432 user=t1 password=t1 dbname=t1 connect_timeout=2 tcp_user_timeout=2000 sslmode=require"
]


def connect_psycopg3(dsn):
    return psycopg.connect(dsn)

def connect_psycopg3_async(dsn):
    return psycopg.AsyncConnection.connect(dsn)

def connect_psycopg2(dsn):
    return psycopg2.connect(dsn)

print(f"psycopg version: {psycopg.__version__}")
print(f"psycopg2 version: {psycopg2.__version__}")
print()

for driver in ["psycopg3","psycopg2","psycopg3_async"]:
    print(f"Testing driver: {driver}")
    for dsn in tested_connection_strings:
        for i in range(5):
            print(f"Testing \"{dsn}\": ", end='')
            try:
                if driver == "psycopg3":
                    db = connect_psycopg3(dsn)
                elif driver == "psycopg3_async":
                    db = connect_psycopg3_async(dsn)
                else:
                    db = connect_psycopg2(dsn)
                db.close()
                print("ok")
            except Exception as e:
                print("failed")
                print(str(e))
            sleep(0.5)

[dvarrazzo]

Same test, but using psycopg.AsyncConnection.connect() works.

Thank you for testing!

Pretty much, we need to use, in the sync code, the same algorithm used in the async code, using socket.getaddrinfo() instead of looop.getaddrinfo().

I'd like to work on it in the next couple of weeks. Would you like to try and propose a bugfix?

[ilmarkerm]

Would you like to try and propose a bugfix?

No, I'll leave it to you :)

[dvarrazzo]

No problem. Thank you for testing! I'll try to put together a solution in the coming days.