Re: BUG #16868: Cannot find sqlstat error codes. - Mailing list pgsql-bugs

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