[Regression] Materialized view with multiple indexes errors

[bdewilde]

Is this a new bug in dbt-core?

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

Current Behavior

Hi! Upon updating to dbt v1.7.14, all models materialized as "materialized_view" and including more than one index in its config fail with a syntax error. This did not happen in v1.7.13. The error looks like this:

15:36:06    Database Error in model int_test (models/intermediate/int_test.sql)
  syntax error at or near "create"
  LINE 13:   (foo)create  index if not exists

It looks like the multiple "create index" statements are being concatenated together incorrectly. I'm wondering if this is related.

Expected Behavior

I would expect materialized views with multiple configured indexes to build and run without error.

Steps To Reproduce

Model (int_test.sql):

{{
    config(
        materialized="materialized_view",
        indexes=[
            {"columns": ["foo"], "type": "btree"},
            {"columns": ["bar"], "type": "btree"},
        ],
    )
}}

SELECT 1 AS foo, 2 AS bar

Command: dbt run --select int_test Output:

15:41:31  1 of 1 START sql materialized_view model intermediate.int_test ............. [RUN]
15:41:31  1 of 1 ERROR creating sql materialized_view model intermediate.int_test .... [ERROR in 0.06s]
15:41:31
15:41:31  Finished running 1 materialized_view model in 0 hours 0 minutes and 0.37 seconds (0.37s).
15:41:31
15:41:31  Completed with 1 error and 0 warnings:
15:41:31
15:41:31    Database Error in model int_test (models/intermediate/int_test.sql)
  syntax error at or near "create"
  LINE 13:   (foo)create  index if not exists
                  ^
  compiled Code at /opt/wg/.meltano/transformers/dbt/target/run/db/models/intermediate/int_test.sql
15:41:31
15:41:31  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Removing one or both of those indexes works just fine:

{{ config(materialized="materialized_view") }}

SELECT 1 AS foo, 2 AS bar

=>

15:43:57  1 of 1 START sql materialized_view model intermediate.int_test ............. [RUN]
15:43:57  1 of 1 OK created sql materialized_view model intermediate.int_test ........ [SELECT 1 in 0.17s]
15:43:57
15:43:57  Finished running 1 materialized_view model in 0 hours 0 minutes and 0.39 seconds (0.39s).
15:43:57
15:43:57  Completed successfully
15:43:57
15:43:57  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

Relevant log output

No response

Environment

- OS: Ubuntu 20.04
- Python: 3.9.19
- dbt: 1.7.14

Which database adapter are you using with dbt?

postgres

Additional Context

No response

[jtcohen6]

@bdewilde Thanks for the report!

I can confirm that this change likely caused the regression, because adding that ; back does seem to restore the previous behavior.

Following the reproduction case:

    create materialized view if not exists "jerco"."dbt_jcohen"."int_test" as 

SELECT 1 AS foo, 2 AS bar;

    create  index if not exists
  "13008d29068d3f93577844cc147dd809"
  on "jerco"."dbt_jcohen"."int_test" using btree
  (foo);create  index if not exists
  "d9594d6bdc8ad05fa90f86119dd485f3"
  on "jerco"."dbt_jcohen"."int_test" using btree
  (bar);

I don't know if adding it back in exactly the same spot is right way to resolve this, versus somewhere else in the MV creation/update flow. Either way, we should:

1. Restore support for MVs with multiple indexes
2. Add a test to ensure it doesn't regress again

[VersusFacit]

Note we have no tests for verifying this behavior one way or another in existence currently. The multiple index thing should cover this ; case.

[mikealfare]

Re-opening for backports