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. |
On Monday, February 15, 2021, PG Bug reporting form <[hidden email]> wrote: The following bug has been logged on the website: 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 <[hidden email]> wrote:
|
On Tuesday, February 16, 2021, bipsy Nair <[hidden email]> wrote:
See \errverbose in the docs, and the various error messages related variables and meta commands mentioned there and in the variables section. David J.
|
In reply to this post by bipsy Nair
On Tue, Feb 16, 2021, at 12:21 PM, bipsy Nair wrote:
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. |
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 Wednesday, February 17, 2021, bipsy Nair <[hidden email]> wrote:
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).
Which proves the server is doing its job of sending back that data as specified in the protocol.
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 <[hidden email]> wrote: On Wednesday, February 17, 2021, bipsy Nair <[hidden email]> wrote: |
On Wednesday, February 17, 2021, bipsy Nair <[hidden email]> wrote:
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 <[hidden email]> wrote: On Wednesday, February 17, 2021, bipsy Nair <[hidden email]> wrote: |
On Wed, Feb 17, 2021 at 4:55 PM bipsy Nair <[hidden email]> wrote:
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. |
Free forum by Nabble | Edit this page |