Thread: Clarification of certain SQLSTATE class
I'm working with some DB2 users, converting them to Pg, and I'm a bit confused about a certain class of SQLSTATE codes, specifically 02xxx "No data" (http://www.postgresql.org/docs/9.2/static/errcodes-appendix.html)
As an example: I enable %e in log_line_prefix so I can see the SQLSTATE values. I run an UPDATE against a non-existent row, and find that my SQLSTATE value is 00000, indicating success. I understand that this could be considered a 'success' because the query didn't actually throw an error; but, based on the spec, I expected to see a SQLSTATE of 02000.
I thought that Pg always generated an appropriate SQLSTATE code, and that it was just up to $client code to pick up that value, but that doesn't seem to be the case. Is this part of the SQL standard we don't implement?
Thanks!
gabrielle
gabrielle <gorthx@gmail.com> writes: > I'm working with some DB2 users, converting them to Pg, and I'm a bit > confused about a certain class of SQLSTATE codes, specifically 02xxx "No > data" (http://www.postgresql.org/docs/9.2/static/errcodes-appendix.html) > As an example: I enable %e in log_line_prefix so I can see the SQLSTATE > values. I run an UPDATE against a non-existent row, and find that my > SQLSTATE value is 00000, indicating success. I understand that this could > be considered a 'success' because the query didn't actually throw an error; > but, based on the spec, I expected to see a SQLSTATE of 02000. > I thought that Pg always generated an appropriate SQLSTATE code, and that > it was just up to $client code to pick up that value, but that doesn't seem > to be the case. Is this part of the SQL standard we don't implement? Yup. regards, tom lane
On 01/25/2013 07:32 AM, gabrielle wrote: > I'm working with some DB2 users, converting them to Pg, and I'm a bit > confused about a certain class of SQLSTATE codes, specifically 02xxx > "No data" > (http://www.postgresql.org/docs/9.2/static/errcodes-appendix.html) > > As an example: I enable %e in log_line_prefix so I can see the > SQLSTATE values. I run an UPDATE against a non-existent row, and find > that my SQLSTATE value is 00000, indicating success. I understand > that this could be considered a 'success' because the query didn't > actually throw an error; but, based on the spec, I expected to see a > SQLSTATE of 02000. > > I thought that Pg always generated an appropriate SQLSTATE code, and > that it was just up to $client code to pick up that value, but that > doesn't seem to be the case. Is this part of the SQL standard we > don't implement? If I understand it correctly, this could be emulated in client drivers - PgJDBC, libpq, etc. Row counts are known to be somewhat broken with the current approach to PostgreSQL table partitioning anyway; you'll always get zero rows affected when you UPDATE a partitioned table, and there's currently (AFIAK) no way for a trigger/rule to aggregate the rowcounts from the commands it executes and return the total. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services