Обсуждение: Getting more detail in plpython error messages

Hi all,

In psql a database error will print both ERROR: and DETAIL: lines.

postgres@testdb# delete from inspection where bundle_id in (select id 
from test_archive_20170401.load order by id);
ERROR:  update or delete on table "inspection" violates foreign key 
constraint "inspection_weather_inspection_id_inspection_id_fk" on table 
"inspection_weather"
DETAIL:  Key (id)=(158967) is still referenced from table 
"inspection_weather".

With plpython (both u and 3u) all I see printed is the ERROR part.

     try:
         check = plpy.execute("delete from inspection where bundle_id in 
(select id from test_archive_20170401.load order by id)")
     except plpy.SPIError as e:
         plpy.notice("Error!", e)

postgres@testdb# select * from test_delete();
NOTICE:  ('Error!', ForeignKeyViolation('update or delete on table 
"inspection" violates foreign key constraint 
"inspection_weather_inspection_id_inspection_id_fk" on table 
"inspection_weather"',))

Is there a way to get the DETAIL part as well?

Thanks,

Jeff

Jeff Ross <jross@openvistas.net> writes:
> With plpython (both u and 3u) all I see printed is the ERROR part.

>      try:
>          check = plpy.execute("delete from inspection where bundle_id in
> (select id from test_archive_20170401.load order by id)")
>      except plpy.SPIError as e:
>          plpy.notice("Error!", e)

> postgres@testdb# select * from test_delete();
> NOTICE:  ('Error!', ForeignKeyViolation('update or delete on table
> "inspection" violates foreign key constraint
> "inspection_weather_inspection_id_inspection_id_fk" on table
> "inspection_weather"',))

> Is there a way to get the DETAIL part as well?

It's not very well documented AFAICS, but a SPIError object has a
"detail" attribute, so "e.detail" should help you.  It looks like
you might prefer to print "e.spidata", which seems to contain all
the available fields.

            regards, tom lane

On 3/7/22 11:06 AM, Tom Lane wrote:
> Jeff Ross <jross@openvistas.net> writes:
>> Is there a way to get the DETAIL part as well?
> It's not very well documented AFAICS, but a SPIError object has a
> "detail" attribute, so "e.detail" should help you.  It looks like
> you might prefer to print "e.spidata", which seems to contain all
> the available fields.
>
>             regards, tom lane
>
>
Thank you, Tom!  As always, that is exactly what I need.

Jeff