The refactor of conninfo.py broke the service parameter.

[xbarra]

Since we updated from psycopg-3.1.8 to psycopg-3.1.[13,14] we are unable to connect by passing the service parameter in the conninfo.

Since we rely on the connection parameters present in the pgservicefile, we don't provide host, port or anything, just the service.

According to psycopg documentation, (since it points to the postgres documentation) should be an accepted method.

Could we get the support of the service parameter back?

Best regards.

[dvarrazzo]

Ouch, this might need that we will need to parse the pg_service file... 😭

Looking into it to assess the problem.

[xbarra]

Ouch, this might need that we will need to parse the pg_service file... 😭

Looking into it to assess the problem.

I had to do that parsing when trying to use other libraries and was not fun.

Personally, I would treat the case I get a service file as a different case where I don't know a port and host until I am connected. But I haven't read the reasons to always specify the port.

Let me know if you need extra information regarding the issue or testing.

Thanks for your time.

[dpprdan]

Ouch, this might need that we will need to parse the pg_service file...

Really? It worked fine with psycopg <= 3.1.12 and pg_service file is built into libpq AFAICT.

(Somewhat) reproducible example:

With this .pg_service file (password set in pgpass.conf or env var)

# https://www.postgresql.org/docs/current/libpq-pgservice.html

[mydb]
host=myhost
dbname=postgres
user=postgres

and (run in a mcr.microsoft.com/devcontainers/python:1-3.11-bookworm devcontainer)

import psycopg
Connection.connect("postgresql://?service=mydb")

I get

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
Cell In[10], [line 2](vscode-notebook-cell:?execution_count=10&line=2)
      [1](vscode-notebook-cell:?execution_count=10&line=1) import psycopg
----> [2](vscode-notebook-cell:?execution_count=10&line=2) Connection.connect("postgresql://?service=mydb")

File ~/.local/lib/python3.11/site-packages/psycopg/connection.py:745, in Connection.connect(cls, conninfo, autocommit, prepare_threshold, row_factory, cursor_factory, context, **kwargs)
    [743](file:///home/vscode/.local/lib/python3.11/site-packages/psycopg/connection.py?line=742) if not rv:
    [744](file:///home/vscode/.local/lib/python3.11/site-packages/psycopg/connection.py?line=743)     assert last_ex
--> [745](file:///home/vscode/.local/lib/python3.11/site-packages/psycopg/connection.py?line=744)     raise last_ex.with_traceback(None)
    [747](file:///home/vscode/.local/lib/python3.11/site-packages/psycopg/connection.py?line=746) rv._autocommit = bool(autocommit)
    [748](file:///home/vscode/.local/lib/python3.11/site-packages/psycopg/connection.py?line=747) if row_factory:

OperationalError: connection is bad: No such file or directory
	Is the server running locally and accepting connections on that socket?

psycopg.__version__
# '3.1.14'

As said, the above works fine with psycopg <= 3.1.12

The regression might be due to #674?

[dvarrazzo]

We need to check if the libpq supports the pg_service file in async mode (i.e. when connecting via PQconnectStart rather than PQconnectdb.

A few features are not supported in async mode: timeout is not supported and, as a consequence, connection retries (#674) and load_balance_hosts.

Note: we use async libpq connection in both sync and async psycopg connections. But, in async psycopg connections we also do further manipulation, by querying the DNS using async functions and populate the connection string hostaddr to avoid a libpq blocking call (see #259).

So, before looking at the code, the thing that I see happening is that, even if we can adjust something simple to avoid service to break, if the service is configured with multiple hosts, it will manifest the same problem of #674, and, if connecting with an async connection, it will block on DNS resolution as per #259.

[vacarme]

Don't know yet if related, but > 3.1.12 does break the Gitlab Postgres Service, which is not able to established connection whereas locally there is no issue.

[dvarrazzo]

@vacarme that's bad to hear. Could you please give more details? What is the connection string that used to work on 3.1.12 and broke afterwards? What is the error?

[dvarrazzo]

I think I will operate this way:

First part: we will fix the "connection attempts" algorithm to avoid touching anything unless it detects that multiple hosts have been really specified. At the moment it takes the freedom of replacing the unspecified parameters with values taken from their defaults or env vars, and this breaks an eventual pg_service specification.

This should result in fixing the regression introduced in 3.1.13, but will not allow to specify multiple hosts in the pg_service file, and would block on DNS resolution if the host is taken from the services file in an async connection. These problems are not regressions, they were present before 3.1.13 too.

This would be released in psycopg 3.1.15.

Second part: we will introduce pg_service parsing (which seems easy to parse using the stdlib configparser) and we will consider the definitions included there to solve the problem mentioned above.

This be probably released in a following psycopg 3.1.x release.

[xbarra]

Second part: we will introduce pg_service parsing (which seems easy to parse using the stdlib configparser) and we will consider the definitions included there to solve the problem mentioned above.

That's what I used when I had to parse it:

    config = configparser.ConfigParser()
    config.read(os.getenv('PGSERVICEFILE'))
    pgparams = {k:v for k,v in config['main'].items()}
```

[vacarme]

@vacarme that's bad to hear. Could you please give more details? What is the connection string that used to work on 3.1.12 and broke afterwards? What is the error?

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

[dvarrazzo]

@vacarme this seems a somewhat unrelated issue we can look into separately. Thank you for the info. Opened #695 to address it. I will add some details there; please let's move there this conversation.