Subject: exception psycopg.Error from psycopg2 to psycopg 3

From: Paolo De Stefani <[email-masked]>
Date: Fri, 11 Feb 2022 18:33:39 +0100

Hi all

Porting my program from psycopg2 to psycopg 3 i found another different 
behaviour

This SQL code inside a function:

RAISE EXCEPTION 'Error wrong database' USING HINT = 'You need to use a 
different database', ERRCODE = 'PA002';

is catched in python code with:

try:
    <execute sql function>
except psycopg.Error as er:
     raise PyAppDBError(er.sqlstate, er)

With psycopg2 er.sqlstate is set to 'PA002' in psycopg3 is set to None

Is this an expected behavour ? Is there a way to get the error code in 
psycopg3 like it is available in psycopg2 ?
Or maybe Am I doing something wrong ?

Thanks

-- 
Paolo De Stefani

---

From: Daniele Varrazzo <[email-masked]>
Date: Fri, 11 Feb 2022 19:47:22 +0100

On Fri, 11 Feb 2022 at 18:34, Paolo De Stefani <[email-masked]> 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

---

From: Daniele Varrazzo <[email-masked]>
Date: Fri, 11 Feb 2022 20:14:43 +0100

On Fri, 11 Feb 2022 at 19:47, Daniele Varrazzo
<[email-masked]> wrote:

> improvement.

https://github.com/psycopg/psycopg/issues/225

-- Daniele

---

From: Paolo De Stefani <[email-masked]>
Date: Fri, 11 Feb 2022 20:42:34 +0100

Yes perfect solution for me
Thank your the lightspeed response..

-- 
Paolo De Stefani

---

From: Karsten Hilbert <[email-masked]>
Date: Sat, 12 Feb 2022 10:39:44 +0100

> makes more sense that Error.sqlstate is whatever state received, if

+1

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B