[ADAP-504] [Regression] cannot run inside a transaction block

[olivierdet]

Is this a new bug in dbt-redshift?

• I believe this is a new bug in dbt-redshift
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

We have created a basic macro to create database.

This does not work anymore using the version 1.5

We have the following error message

Encountered an error while running operation: Database Error
  CREATE DATABASE cannot run inside a transaction block

Expected Behavior

The macro should work as worked in the version 1.4

Steps To Reproduce

Execute the following macro with dbt redshift v1.5

{% macro create_ci_database(git_version) %}

{% set create_command %}
    CREATE DATABASE z_ci_{{ git_version }} ;
{% endset %}

{% do run_query(create_command) %}

{{ log("Created database redshift z_ci_" ~ git_version, info=True) }}

{% endmacro %}

Relevant log output

No response

Environment

- OS:
- Python:
- dbt-core: 1.5.0
- dbt-redshift: 1.5.0

Additional Context

No response

[dbeatty10]

Thanks for reporting this @olivierdet.

Can you share more about how you are calling your create_ci_database macro? e.g., run-operation or on-run-start or something else?

[olivierdet]

We're using run-operation

[dbeatty10]

Thanks again for reporting this @olivierdet.

I was able to reproduce that this fails for dbt-redshift 1.5.0 but works for 1.4.x 👍

Reprex

See below for the macro and commands I used.

macros/my_macros.sql

{% macro create_ci_database() %}

{% set database = "dbt_dbeatty" %}

{% set create_command %}
    CREATE DATABASE {{ database }}
{% endset %}

{{ log(create_command, info=True) }}

{% do run_query(create_command) %}

{{ log("Created redshift database " ~ database, info=True) }}

{% endmacro %}

Try to run the macro:

dbt --debug run-operation create_ci_database

[VersusFacit]

Looks like the answer is to sneak a connection.autocommit=True into the right place. def connect feels way too blunt, so I'm attempting a bit more finesse...

[ighii]

I have reported a related issue on the DBT forum - https://discourse.getdbt.com/t/cannot-drop-or-create-external-tables-since-upgrade-to-dbt-1-5-0/8265/2.

[VersusFacit]

The Redshift connector likely in its design philosophy does not allow CREATE DATABASE/VACUUM to be run without the autocommit feature true. But because how adapters are defined, we cannot preserve the original workflow without some extremely painful lift:

• go back to psycopg2
• completely change the MacroTask's thread allocation model
• completely change how all threads are allocated / when (i.e. the env var solution)

I'm going to merge #458 to give users a path forward. However, the hope of this issue cannot be attained without us going back to the drawing board.