[ADAP-939] [Bug] `cannot reference permanent table from temporary table constraint` when running incremental model with a foreign key

[aranke]

Most of the content below is copy-pasted from @amardatar within https://github.com/dbt-labs/dbt-core/issues/8022

Is this a new bug in dbt-redshift?

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

Current Behavior

When running an incremental model with contract enforced and a foreign key, the model will fail on subsequent updates.

Expected Behavior

The update should succeed without an error.

Steps To Reproduce

First run a statement like:

create table if not exists numbers (n int not null primary key)

Then, given a schema.yml file:

version: 2

models:
  - name: test
    config:
      contract:
        enforced: true
      materialized: incremental
      on_schema_change: append_new_columns
      unique_key: n
    columns:
      - name: n
        data_type: integer
        constraints:
          - type: foreign_key
            expression: "numbers (n)"

And a SQL file:

select 1 as n

dbt will succeed on the first run, and subsequently fail with the error:

cannot reference permanent table from temporary table constraint

As per the error returned, the issue appears to be that while rendering a temporary table as part of the update, dbt will also render the foreign key constraint (which is not valid in the context of a temporary table). This leads to the above failure.

Relevant log output

No response

Environment

- OS: macOS Ventura 13.4.1
- Python: 3.11.4
- dbt: 1.5.2

[dbeatty10]

@aranke verified a fix for Postgres in https://github.com/dbt-labs/dbt-core/pull/8768 and then created this backlog ticket for Redshift.

[jtcohen6]

I think the fix for dbt-postgres was too large in scope, leading to other issues:

• https://github.com/dbt-labs/dbt-core/issues/8857#issuecomment-1762261405

[emmyoop]

The original fix (https://github.com/dbt-labs/dbt-core/issues/8022) was modified slightly in https://github.com/dbt-labs/dbt-core/pull/8889 to still enforce the contract.

[bdgeise]

Ran into this issue today and created a PR to address https://github.com/dbt-labs/dbt-redshift/pull/927