Problem resolving localhost in async mode

[dvarrazzo]

Locally I spin up a Postgres container on port 5433 for executing my tests and everything is working.
On gitlab I have a pipeline using the gitlab Postgres service, everything worked prior to 3.1.13 but since then my app is unabled to reach the Postgres db.

I do not use psypcopg directly, only via sqlalchemy so I do not know if this would be useful but here is a gist containing the logs extracted from the gitlab job:

https://gist.github.com/vacarme/90311ce419ea4f99a8b91b1136656a10

Originally posted by @vacarme in #694 (comment)

[dvarrazzo]

@vacarme from your log it seems that we resolve localhost to ::1 instead of 127.0.0.1 and most likely postgres is not listening on IPV6.

I don't know why this should be a problem with psycopg 3.1.13 but not 3.1.12: I didn't expect the async name resolution to change. I am probably wrong in some way.

Are you able to provide the content of /etc/hosts?

[dminhhoang26]

I think Async mode is having problem (psycopg==3.1.13), every of my sync connect work, but async is not (sometime it work, but rarely), i'm not a good developer so i cannot figure out the issue.

psycopg.OperationalError: connection is bad: 9000:5302:1a00::1", port 5432 failed: Network is unreachable
        Is the server running on that host and accepting TCP/IP connections?

[dvarrazzo]

@dminhhoang26 what is the content of your /etc/hosts file?

[dvarrazzo]

Aww... I think I found the problem:

psycopg/psycopg/psycopg/conninfo.py

Lines 451 to 454 in 70ef364

	attempt = params.copy()
	for item in ans:
	attempt["hostaddr"] = item[4][0]
	yield attempt

The yield attempt is outside the loop. So if the hosts file is configured to resolve first ipv4, then ipv6, will try only ipv6 resolution.

Adding a test to verify it. Can you test if with this change:

--- a/psycopg/psycopg/conninfo.py
+++ b/psycopg/psycopg/conninfo.py
@@ -451,7 +451,7 @@ async def _split_attempts_and_resolve(params: ConnDict) -> AsyncIterator[ConnDic
     attempt = params.copy()
     for item in ans:
         attempt["hostaddr"] = item[4][0]
-    yield attempt
+        yield attempt
 
 
 @cache

the problem is solved?

[dminhhoang26]

@dvarrazzo

@dminhhoang26 what is the content of your /etc/hosts file?

I'm running my FastAPI app in wsl Ubuntu locally and using psycopg3 directly to connect to neon postgres server, my /etc/hosts file in the wsl:

# This file was automatically generated by WSL. To stop automatic generation of this file, add the following entry to /etc/wsl.conf:
# [network]
# generateHosts = false
127.0.0.1       localhost
127.0.1.1       DESKTOP-HIDEN.        DESKTOP-HIDEN

192.168.1.14    host.docker.internal
192.168.1.14    gateway.docker.internal
127.0.0.1       kubernetes.docker.internal

# The following lines are desirable for IPv6 capable hosts
::1     ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

[dvarrazzo]

@dminhhoang26 I don't see from this hosts file how localhost may be resolved to 9000:5302:1a00::1. What was your connection string?

Because that's WSL, I'm unable to test it directly. Did you try if making the change above fix the problem?

[dminhhoang26]

@dvarrazzo thank you very much! I think i will try to use the async connection again in the future.

@dminhhoang26 I don't see from this hosts file how localhost may be resolved to 9000:5302:1a00::1. What was your connection string?

Because that's WSL, I'm unable to test it directly. Did you try if making the change above fix the problem?

My postgres is a live server (not localhost) from Neon tech and sync and async connection parameters are the same, i'm trying refactor my sync connection to async one but facing the issue (I dont know the issue is from psycopg or the server). this is my code snippet:

# my connection function
    PG_CONNECTIONSTRING = f"""postgres://{PyEnv.POSTGRES_USER}:{PyEnv.POSTGRES_PW}@{PyEnv.PG_HOST}:{PyEnv.PG_PORT}/{PyEnv.POSTGRES_DB}{"?sslmode=require" if PyEnv.PG_SSLMODE else ""}"""

    def get_conn(self) -> psycopg.Connection:
        conn = psycopg.connect(conninfo=self.PG_CONNECTIONSTRING)
        print(f"Successfully connected to the database {PyEnv.POSTGRES_DB}")
        return conn

    async def get_conn_async(self):
        return await psycopg.AsyncConnection.connect(conninfo=self.PG_CONNECTIONSTRING)

# this is how i use it:
      querySql = sql.SQL(
          """
          SELECT *
          FROM {tbl}
          WHERE code = %s
          ;"""
      ).format(tbl=sql.Identifier(PROVINCES_TABLE))

      async with await self.dbHelper.get_conn_async() as con:
          async with con.cursor(row_factory=rows.dict_row) as cur:
              await cur.execute(querySql, [code])
              rec = await cur.fetchone()

[dvarrazzo]

@dminhhoang26 Thank you, a fix should be on the way.

Are you able to run:

import socket
print(socket.getaddrinfo(PyEnv.PG_HOST, PyEnv.PG_PORT, proto=socket.IPPROTO_TCP, type=socket.SOCK_STREAM))

with your environment values?