BUG #16868: Cannot find sqlstat error codes.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #16868: Cannot find sqlstat error codes.

PG Bug reporting form
The following bug has been logged on the website:

Bug reference:      16868
Logged by:          bipsy Nair
Email address:      [hidden email]
PostgreSQL version: 12.4
Operating system:   RDS and EC2
Description:        

Hi,

I am not able to get any SQLSTATE Error code for Postgres on any versions on
RDS AWS or EC2 Postgres 10.

ERROR: No SQLSTATE genrated in Postgres. This was the error's which was
displayed.
=====
ERROR:  duplicate key value violates unique constraint
"pk_dml_error_logging"
DETAIL:  Key (id)=(1) already exists.

I am looking for a Error code like '23503' as per PG documentations
https://www.postgresql.org/docs/current/errcodes-appendix.html#ERRCODES-TABLE

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.

Reply | Threaded
Open this post in threaded view
|

Re: BUG #16868: Cannot find sqlstat error codes.

David G Johnston

On Monday, February 15, 2021, PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      16868
Logged by:          bipsy Nair
Email address:      [hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: BUG #16868: Cannot find sqlstat error codes.

bipsy Nair
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 <[hidden email]> wrote:

On Monday, February 15, 2021, PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      16868
Logged by:          bipsy Nair
Email address:      [hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: BUG #16868: Cannot find sqlstat error codes.

David G Johnston
On Tuesday, February 16, 2021, bipsy Nair <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: BUG #16868: Cannot find sqlstat error codes.

Euler Taveira-3
In reply to this post by bipsy Nair
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

Reply | Threaded
Open this post in threaded view
|

Re: BUG #16868: Cannot find sqlstat error codes.

bipsy Nair
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 <[hidden email]> wrote:
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

Reply | Threaded
Open this post in threaded view
|

Re: BUG #16868: Cannot find sqlstat error codes.

David G Johnston
On Wednesday, February 17, 2021, bipsy Nair <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: BUG #16868: Cannot find sqlstat error codes.

bipsy Nair
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 <[hidden email]> wrote:
On Wednesday, February 17, 2021, bipsy Nair <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: BUG #16868: Cannot find sqlstat error codes.

David G Johnston
On Wednesday, February 17, 2021, bipsy Nair <[hidden email]> 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. 
Reply | Threaded
Open this post in threaded view
|

Re: BUG #16868: Cannot find sqlstat error codes.

bipsy Nair

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 <[hidden email]> wrote:
On Wednesday, February 17, 2021, bipsy Nair <[hidden email]> 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. 
Reply | Threaded
Open this post in threaded view
|

Re: BUG #16868: Cannot find sqlstat error codes.

David G Johnston
On Wed, Feb 17, 2021 at 4:55 PM bipsy Nair <[hidden email]> 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.