Re: Getting the SQLSTATE after a failed connection

On Fri, 17 Oct 2025 at 16:49, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)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:
>
> https://www.postgresql.org/message-id/flat/125437e5-25c8-49ad-99af-8de04b77daf6%40postgrespro.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] [2]).

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