MIME-Version: 1.0
References: <11dedb1fce0dbbd6f125736347685fd5@paolodestefani.it>
In-Reply-To: <11dedb1fce0dbbd6f125736347685fd5@paolodestefani.it>
From: Daniele Varrazzo <daniele.varrazzo@gmail.com>
Date: Fri, 11 Feb 2022 19:47:22 +0100
Message-ID: 
 <CA+mi_8bTw162gch-C4CmO98P6RNviF7DeH-cAJ6QRq+D48dF6g@mail.gmail.com>
Subject: Re: exception psycopg.Error from psycopg2 to psycopg 3
To: Paolo De Stefani <paolo@paolodestefani.it>
Cc: Psycopg <psycopg@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2Bmi_8bTw162gch-C4CmO98P6RNviF7DeH-cAJ6QRq%2BD48dF6g%40mail.gmail.com>
Precedence: bulk

On Fri, 11 Feb 2022 at 18:34, Paolo De Stefani <paolo@paolodestefani.it> wrote:

Hi Paolo,

Yes, I can see some inconsistency there. At the moment I suggest you
use `e.diag.sqlstate`, which works as expected and is available in
psycopg2 too.

    In [8]: try: cnn.execute("""
      ...: do $$
      ...: begin
      ...: RAISE EXCEPTION 'Error wrong database' USING HINT = 'You
need to use a different database', ERRCODE = 'PA002';
      ...: end$$
      ...: language plpgsql
      ...: """)
      ...: except Exception as e: ex = e

    In [10]: ex.diag.sqlstate
    Out[10]: 'PA002'

    In [11]: ex.sqlstate
    None

What is happening is that, in psycopg 3, Error.sqlstate is a class
property and is only set for the known classes - the ones listed at
<https://www.psycopg.org/psycopg3/docs/api/errors.html#sqlstate-exceptions>.

The attribute `e.diag.sqlstate` instead comes from the error message
received from the server. When the error is received, the matching
class is looked up by sqlstate, with the basic dbapi exceptions as
fallback if the code is not known, and the server result is passed to
the exception state, so that `e.diag` is populated with all the
details (such as the message, the hint etc).

The cases I had in mind where 1) known exceptions where Error.sqlstate
and e.diag.sqlstate would match, and 2) non-server exceptions (e.g. on
connection, or raised by Python code) where Error.sqlstate and
e.diag.sqlstate are both None.

I didn't think about the case 3) where a sqlstate exists, but psycopg
doesn't know it. In this case, the result of the current
implementation is to raise an exception with the sqlstate left to None
on the class but available in diag.

ISTM that setting e.sqlstate = e.diag.sqlstate would be an
improvement. The docs describe indeed that sqlstate is expected to be
None on the DBAPI classes
(https://www.psycopg.org/psycopg3/docs/api/errors.html#psycopg.Error.sqlstate)
but that wasn't written thinking about the inconsistency above. It
makes more sense that Error.sqlstate is whatever state received, if
any.

Does it sound right?

-- Daniele