support Cursor attribute to provide ANSI SQL State Code

[dataders]

While a Cursor attribute providing SQL State Code is not officially a part of PEP 249: Python DB API 2.0 spec, there is an ANSI-standardized "SQL state code".

Many database drivers provide this as a Cursor attribute, dbt was able to depend on these drivers to provide it for ConnectionManager.get_response() method, which will report to users after successful queries the kind of operation performed (SELECT, INSERT, CREATE) and the numbers of rows affected. Originally the Redshift adapter for dbt, was supported by the psycopg2 driver, which provides this information in statusmessage.

As reported in dbt-labs/dbt-redshift#785, after migrating the driver dependency to redshift-connector, users are in a degraded state and receive less information than previously due to the SQL state not being available.

Support for SQL state amongst popular analytics database drivers

Driver	Cursor attribute (docs)
psycopg2	statusmessage
snowflake-connector-python	sqlstate

Ideal implementation

Postgres's CommandComplete message

Command	Tag	rows indicates the number of rows
INSERT	INSERT 0 rows	inserted
DELETE	DELETE rows	deleted
UPDATE	UPDATE rows	updated
MERGE	MERGE rows	inserted, updated, or deleted
SELECT / CREATE TABLE AS	SELECT rows	retrieved
MOVE	MOVE rows	ursor's position has been changed by
FETCH	FETCH rows	that have been retrieved from the cursor
COPY	COPY rows	copied, only in PostgreSQL 8.2 and later

[Brooke-white]

Hi @dataders , my apologies for the delay in response on this issue. Thank you for raising this gap and for providing an in-depth explanation of the feature request.

From a quick look at the redshift_connector handler for COMMAND COMPLETE, it appears this should be extremely straight forward i.e. modifying this method to set the value of some SQL state attribute on the Cursor.

The only place I anticipate friction is if there are commands for which Redshift server is not providing a command tag within the COMMAND COMPLETE message.

Things may have changed since I last looked into it, but last I remember SELECT commands are such a case i.e. Redshift server does not send the command tag in the COMMAND COMPLETE message following SELECT command execution.

Regardless, I will raise this feature request with the Redshift drivers team and advocate it's inclusion in our roadmap to help ease transitions to redshift-connector for DBT and all other customers coming from drivers which offer this attribute.