BUG #16868: Cannot find sqlstat error codes.

On Monday, February 15, 2021, PG Bug reporting form <noreply@postgresql.org>
wrote:

The following bug has been logged on the website:

Bug reference: 16868
Logged by: bipsy Nair
Email address: nbipin29@gmail.com
PostgreSQL version: 12.4
Operating system: RDS and EC2
Description:

Our developer needs a Error code generated so that they can trap in the
applications incase of any errors.
Please advice and provide a workaround.

Might want to provide a full,example of the code involved in executing the
SQL and processing the errors. This is all very db client-specific.

David J.

Thank you for your response.

Here is the code with the required error and details. I tested this in all
Postgres versions.

create table bipin
(id bigint , val1 character varying(1000) not null , val2 int);
insert into bipin
select i , 'test' || i , i+1 from generate_series(1,1000) dt(i);
alter table bipin add constraint pk_error_logging primary key (id);

with bipin_test as
(select 1 , 'test99' , 1
union all
select 1001 , null , 1
union all
select 1002 , 'test99' , 1 )
insert into bipin
select * from bipin_test;

(Executing the query gives error duplicate keys but its not showing the
SQLSTATE error code. This is needed when the application throws error for
easy troubleshooting.
postgres=> with bipin_test as
postgres-> (select 1 , 'test99' , 1
postgres(> union all
postgres(> select 1001 , null , 1
postgres(> union all
postgres(> select 1002 , 'test99' , 1 )
postgres-> insert into bipin
postgres-> select * from bipin_test;
ERROR: duplicate key value violates unique constraint "pk_error_logging"
DETAIL: Key (id)=(1) already exists.
postgres=>

Issue:

We use aurora-data-api with postgresql. In the backend lambdas,
SQLAlchemy is used as an ORM (shouldn't matter, but pointng it out
anyway).
Right now, when I insert duplicate values for example, I get a root
error of type `botocore.errorfactory.BadRequestException` which isn't
really helpful. Our current way to deal with these is to look for some
substring of the error message (i.e. if "duplicate key value" in err:
...), however it clearly isn't proper exception handling, as it forces
us to code our own error mapping to some "arbirary" strings instead of
a well-defined error codes map.

Postgresql does have a list of error codes:
https://www.postgresql.org/docs/current/errcodes-appendix.html#ERRCODES-TABLE

How can I get that SQLSTATE code errors ? When i am manually running
from psql or pgadmin i dont get the code .I only get the ERROR. Please
advice for any workaround for such type of behaviour.

On Tue, Feb 16, 2021 at 6:11 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Tuesday, February 16, 2021, bipsy Nair <nbipin29@gmail.com> wrote:

How can I get that SQLSTATE code errors ? When i am manually running from psql

See \errverbose in the docs, and the various error messages related
variables and meta commands mentioned there and in the variables section.

David J.

On Tue, Feb 16, 2021, at 12:21 PM, bipsy Nair wrote:

Here is the code with the required error and details. I tested this in all Postgres versions.

postgres=# \i /tmp/b16868.sql
DROP TABLE
CREATE TABLE
INSERT 0 1000
ALTER TABLE
psql:/tmp/b16868.sql:19: ERROR: duplicate key value violates unique constraint "pk_error_logging"
DETAIL: Key (id)=(1) already exists.
postgres=# \errverbose
ERROR: 23505: duplicate key value violates unique constraint "pk_error_logging"
DETAIL: Key (id)=(1) already exists.
SCHEMA NAME: public
TABLE NAME: bipin
CONSTRAINT NAME: pk_error_logging
LOCATION: _bt_check_unique, nbtinsert.c:656

The sqlstate (23505) is reported accordingly. Since you are using Aurora and it
is not Postgres, it should possibly omit the sqlstate in the error message stack.
The other possibility is that aurora-data-api is not gathering the sqlstate.
I'm afraid you won't find both answers here.

--
Euler Taveira
EDB https://www.enterprisedb.com/

Thanks for the detailed explanation. I did the following test and this is
the issue in see in Postgres.
Not able to get the SQLSTATE code for Postgres flavours. Any config changes
needed at client or db side.

1. Test on Serverless Postgres with parameter log_error_verbosity=verbose
SELECT name, setting FROM pg_settings WHERE name LIKE 'log_error_verbosity';
drop table bipin;
create table bipin (id int);
insert into bipin values(1);
alter table bipin add constraint pk_error_logging primary key (id);
insert into bipin values(1);(No SQL State captured). ERROR: duplicate
key value violates unique constraint "pk_error_logging" Detail: Key
(id)=(1) already exists.

2. Test on Serverless Aurora-Mysql. same code. You see its captured.
Database error code: 1062. Message: Duplicate entry '1' for key 'PRIMARY'

3.Test on Mysql (non-serverless).
mysql> insert into bipin value(1); same code. You see its captured.
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

4. Test on Postgres with parameter ==> log_error_verbosity=verbose
SELECT name, setting FROM pg_settings WHERE name LIKE 'log_error_verbosity';

postgres=> create table bipin (id int);
ERROR: relation "bipin" already exists
postgres=> insert into bipin values(1);
ERROR: duplicate key value violates unique constraint "pk_error_logging"
==> NO SQL STATE captured.
DETAIL: Key (id)=(1) already exists.

But when i run the following it shows.
postgres=> \errverbose
ERROR: 23505: duplicate key value violates unique constraint
"pk_error_logging"
DETAIL: Key (id)=(1) already exists.
SCHEMA NAME: public
TABLE NAME: bipin
CONSTRAINT NAME: pk_error_logging
LOCATION: _bt_check_unique, nbtinsert.c:573

So this is the exact issues faced by developer :
rdsdataservice client to make "execute_statement()" call, but when we
insert duplicate values for example, boto3 client does not return valid
error message with PostgreSQL Error Codes.
I tried setting the Boto3 logs to full logging, and running the same query
showed this in the logs:

2021-02-16 15:18:11,091 botocore.parsers

[DEBUG] Response body:
b'{"message":"ERROR: duplicate key value violates unique constraint
\\"site_site_name_key\\"\\n Detail: Key (site_name)=(f) already exists."}'

2021-02-16 15:18:11,096 botocore.parsers

[DEBUG] Response headers:
{'x-amzn-RequestId': 'd0d366f8-0291-492e-aadb-58d4b1e48dfa',
'x-amzn-ErrorType': 'BadRequestException:XXXrdsdataservice/',
'Content-Type': 'application/json', 'Content-Length': '137', 'Date': 'Tue,
16 Feb 2021 20:18:10 GMT', 'Connection': 'close'}

So we need a workaround wherein it can show the SQLSTATE code and want to
know if its limitation at Postgres DB level.

thank you ,
Bipin

On Wed, Feb 17, 2021 at 8:54 AM Euler Taveira <euler@eulerto.com> wrote:

On Wednesday, February 17, 2021, bipsy Nair <nbipin29@gmail.com> wrote:

4. Test on Postgres with parameter ==> log_error_verbosity=verbose
SELECT name, setting FROM pg_settings WHERE name LIKE
'log_error_verbosity';

postgres=> create table bipin (id int);
ERROR: relation "bipin" already exists
postgres=> insert into bipin values(1);
ERROR: duplicate key value violates unique constraint "pk_error_logging"
==> NO SQL STATE captured.
DETAIL: Key (id)=(1) already exists.

That setting is for the log file, but you are showing what the client sees
(which the server doesn’t really care about or influence - beyond
client_min_message anyway).

But when i run the following it shows.
postgres=> \errverbose
ERROR: 23505: duplicate key value violates unique constraint
"pk_error_logging"

Which proves the server is doing its job of sending back that data as
specified in the protocol.

So we need a workaround wherein it can show the SQLSTATE code and want to
know if its limitation at Postgres DB level.

No, its not a server limitation, its a client limitation - in this case
boto3.

David J.

Thank you for the clarification. But why do i dont see the SQLSTATE code
for Postgres as per my test on psql clients etc.

As its not showing the errorcode , thats the reason boto3 is not able to
capture the error code.
Please advice if you have any thoughts on this as its confusing and
developer thinks its some limitations on RDS side.

On Wed, Feb 17, 2021 at 10:23 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Wednesday, February 17, 2021, bipsy Nair <nbipin29@gmail.com> wrote:

Thank you for the clarification. But why do i dont see the SQLSTATE code
for Postgres as per my test on psql clients etc.

If you put psql into verbose mode, or do \errverbose, you see the error
code. psql has, but chooses not to print, the error code in non-verbose
mode.

David J.

Please advice on this issue. It points its a issue with Postgres which is
not providing the SQLSTATE Error code.

I dug a bit deeper and found that the big library "psycopg2" interfaces
directly with the Postgresql C lib (the major header being libpq-fe.h).
Here is confirmation from that library's author:

https://github.com/psycopg/psycopg2/issues/1240

That being said, if all Botocore does is call AWS's internals to get a
response, then chances are it's not a botocore issue.

The full boto logs show that the response from the request to
http://internal.amazon.com/coral/com.amazon.rdsdataservice/ doesn't contain
the SQLSTATE. This is what leads me to think that it's potentially a
problem in the RDS Postgres internals.

Thank you,

Bipin

On Wed, Feb 17, 2021 at 10:34 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Wed, Feb 17, 2021 at 4:55 PM bipsy Nair <nbipin29@gmail.com> wrote:

This is what leads me to think that it's potentially a problem in the RDS
Postgres internals.

This is unlikely. I suspect that since Boto is intended as an abstraction
layer it simply doesn't care about trying to get verbose error details from
PostgreSQL and so ignores the SQLSTATE error code. Since the server
doesn't put the error code into the error message, as it seems MySQL does
by your examples, the code is simply unavailable without code changes to
the client database driver.

Sure, PostgreSQL could add a server option to print the SQLSTATE error code
as part of the error message. But it doesn't, nor do I suspect that is
likely to change.

The server provides the data; complain to the client software developer if
they are not making it accessible to you in the way you need when you use
their software.

David J.