Thread

1. Getting the SQLSTATE after a failed connection

   Daniele Varrazzo <daniele.varrazzo@gmail.com> — 2025-10-17T14:26:44Z

   Hello,
   
   in ticket <https://github.com/psycopg/psycopg/issues/1188> 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.
   
   Using `PQmakeEmptyPGresult` didn't help either: PQresultErrorField
   reports no data about the error.
   
   Browsing the code, it seems that PqMsg_CommandComplete is only handled
   in isBusy/getResult. There is a promising `last_sqlstate` in the
   structure but it seems only used internally and not exposed.
   
   Is there any way to get the last SQLSTATE from the connection object
   or is that information lost?
   
   Thank you very much
   
   -- Daniele
   
   
   
   

2. Re: Getting the SQLSTATE after a failed connection

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-10-17T14:49:28Z

   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:
   
   https://www.postgresql.org/message-id/flat/125437e5-25c8-49ad-99af-8de04b77daf6%40postgrespro.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
   
   
   
   

3. Re: Getting the SQLSTATE after a failed connection

   Daniele Varrazzo <daniele.varrazzo@gmail.com> — 2025-10-17T16:26:57Z

   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:
   >
   > 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
   
   
   
   

4. Re: Getting the SQLSTATE after a failed connection

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-10-17T16:53:45Z

   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
   
   
   
   

5. Re: Getting the SQLSTATE after a failed connection

   Ranier Vilela <ranier.vf@gmail.com> — 2025-10-17T17:28:21Z

   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> 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
   

6. Re: Getting the SQLSTATE after a failed connection

   Daniele Varrazzo <daniele.varrazzo@gmail.com> — 2025-10-17T18:01:52Z

   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