[SPIKE] Investigate state of transactions

[dataders]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt-redshift functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Several issues already exist that even today, transactions are not working in dbt-redshift as they should

Similar to dbt-labs/dbt-core#9980 but only for dbt-redshift. Less future looking, but rather backward looking. How can we get back to a previous state?

questions

• Will support simply query cycle and therefore multi-statement .execute() calls aws/amazon-redshift-python-driver#162 solve our problems, or is there more to do?
• how does the autocommit parameter introduced in Restore transaction semantics used by dbt-redshift prior to 1.5 #475 affect transaction behavior?
• is there a way to expose redshift_connector's .commit() method to users in jinja land instaed?

Transaction-related open issues

Beta Give feedback

1. [ADAP-671] Statements executed one by one instead of as a whole block #521
   bug regression transactions +3
2. [ADAP-1030] [Regression] Back-to-back hooks with a transaction using CTAS + DML fails in 1.5+ #663
   bug help_wanted regression transactions +4
3. [ADAP-1077] [Drops Table In Separate Transaction] - Tables are dropped then the _tmp table renamed, causing other queries referencing the table to fail #693
   High Severity backport 1.7.latest bug regression support +5
   
4. [Regression] Increased execution times with version 1.7 compared to version 1.3 #741
   performance regression transactions +3
   
5. [Regression] DBT 1.7.3 violates Redshift transactions. #766
   bug regression transactions +3
   

Describe alternatives you've considered

No response

Who will this benefit?

No response

Are you interested in contributing this feature?

No response

Anything else?

No response

[victor-frank-signet]

I want to add that is this a problem with redshift_connector AND the redshift version? Some people say it started in 1.5+. But once the bug was reported others have said if they downgraded from 1.5+ to 1.4 and 1.4 the error persists.

#766 (comment)
#501 (comment)