Getting the SQLSTATE after a failed connection

Daniele Varrazzo <daniele.varrazzo@gmail.com> writes:

Is there any way to get the last SQLSTATE from the connection object
or is that information lost?

This was discussed just a few days ago:

/messages/by-id/125437e5-25c8-49ad-99af-8de04b77daf6@postgrespro.ru

I'd be in favor of adding some API here as long as it's not
myopic about the complexity of the problem.

regards, tom lane

On Fri, 17 Oct 2025 at 16:49, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Daniele Varrazzo <daniele.varrazzo@gmail.com> writes:

Is there any way to get the last SQLSTATE from the connection object
or is that information lost?

This was discussed just a few days ago:

/messages/by-id/125437e5-25c8-49ad-99af-8de04b77daf6@postgrespro.ru

Interesting, thank you for the reference.

I'd be in favor of adding some API here as long as it's not
myopic about the complexity of the problem.

If the complexity comes from having to store a sqlstate for every
attempt, I'd like to point out that the multiple attempts algorithms
only kick in for sync connections; at the moment any client wanting to
connect asynchronously needs to replicate dns results expansion,
multiple hosts/ports handling, timeout implementation, pg_serivce
management, target_session_attr, load_balance_hosts=random, etc. A lot
of work with inevitably subtle differences in behaviour.

If the content of `PGconn.last_sqlstate` is well maintained, then
providing a `PQsqlstate(PGconn *)` with limitations similar to
`PQerrorMessage(PGconn *)` (which I assume only returns the last
error) seems trivial to implement and extremely useful for 1) sync
connections making only one attempt and 2) async connections with
clients managing the multiple attempts (as psycopg does [1]https://github.com/psycopg/psycopg/blob/65fe9d73cab7b377f73384f43ae11df2f8b4b35f/psycopg/psycopg/connection.py#L97-L129 [2]https://github.com/psycopg/psycopg/blob/65fe9d73cab7b377f73384f43ae11df2f8b4b35f/psycopg/psycopg/_conninfo_attempts.py).

AFAICS there is currently no libpq api that works at the attempt
level; one returning the sqlstate wouldn't be the first one needing to
be attempt-aware. Nor are there any APIs exposing the concept of
attempt at all. So I would keep the two improvements (sqlstate on
connection, attempts-aware APIs) separate, the first being the 1% of
the complexity of the second.

The major work that should be done in the area of multiple attempts I
think should start with exposing these attempts for a start, and then
attaching info about the failure afterwards.

-- Daniele

[1]: https://github.com/psycopg/psycopg/blob/65fe9d73cab7b377f73384f43ae11df2f8b4b35f/psycopg/psycopg/connection.py#L97-L129
[2]: https://github.com/psycopg/psycopg/blob/65fe9d73cab7b377f73384f43ae11df2f8b4b35f/psycopg/psycopg/_conninfo_attempts.py

Daniele Varrazzo <daniele.varrazzo@gmail.com> writes:

On Fri, 17 Oct 2025 at 16:49, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I'd be in favor of adding some API here as long as it's not
myopic about the complexity of the problem.

If the complexity comes from having to store a sqlstate for every
attempt, I'd like to point out that the multiple attempts algorithms
only kick in for sync connections; at the moment any client wanting to
connect asynchronously needs to replicate dns results expansion,
multiple hosts/ports handling, timeout implementation, pg_serivce
management, target_session_attr, load_balance_hosts=random, etc.

I'm not sure that that's true, and even if it is, I don't think it's
relevant. The way we've extended the behavior of PQconnect* over
the last dozen or so years means that "there was only one connection
attempt" is not an assumption an application can safely make.
I don't want to add APIs that depend on that assumption to be useful.

If the content of `PGconn.last_sqlstate` is well maintained,

That's a large "if", too. We never intended it to be exposed;
it was only meant to be consulted shortly after the fact within
libpq itself. I don't think much thought has been given to
questions like exactly when to reset it.

regards, tom lane

Hi.

Em sex., 17 de out. de 2025 às 11:27, Daniele Varrazzo <
daniele.varrazzo@gmail.com> escreveu:

Hello,

in ticket <https://github.com/psycopg/psycopg/issues/1188&gt; an user
showed that, upon a failed connection attempt because of a bad
password, the server sends a SQLSTATE 28P01 message. However psycopg
cannot raise the expected `InvalidPassword` exception, because the
only way to get the SQLSTATE seems to be from a PGresult via
PQresultErrorField and I fail to see any API call to get the info from
a PGconn.

Have you seen the *pgpassfileWarning* function
(src/interfaces/libpq/fe-connect.c)?
Does this sound like what you're looking for?

best regards,
Ranier Vilela

On Fri, 17 Oct 2025 at 19:28, Ranier Vilela <ranier.vf@gmail.com> wrote:

Have you seen the *pgpassfileWarning* function (src/interfaces/libpq/fe-connect.c)?
Does this sound like what you're looking for?

Not at all, no

-- Daniele