[Bug] Cannot create multiple indexes on postgres materialized view

[rubenhelsloot]

Is this a new bug in dbt-core?

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

Current Behavior

I have a postgres materialized view with two indexes, defined as

{{
    config(
        indexes=[
            {'columns': ['parent_id']},
            {'columns': ['node_id']},
        ])
}}

When I try to run dbt build, this fails with the following error:

syntax error at or near "create"
LINE 114:   (parent_id)create  index if not exists

The cause of which is obvious when looking at the code in target/run

create materialized view if not exists "kim"."intermediate"."int_hierarchy__basis" as 

...

SELECT * FROM remove_nodes_without_type;

create  index if not exists
  "5b1c4dda378142ef585f1dc654ac67f6"
  on "kim"."intermediate"."int_hierarchy__basis" 
  (parent_id)create  index if not exists
  "c9f64e20a2a1a629eb7bd92d46e5b9b2"
  on "kim"."intermediate"."int_hierarchy__basis" 
  (node_id)

Expected Behavior

I expect to be able to create the materialized view and both indexes without an error. This should probably be solvable by adding a semicolon (;) between the index definitions.

Steps To Reproduce

1. Create a materialized view in Postgres with two indexes;
2. Run dbt build;
3. This should error

Relevant log output

No response

Environment

- OS: MacOS
- Python: 3.11
- dbt: 1.8.0

Which database adapter are you using with dbt?

postgres

Additional Context

Looking at the macro's, it looks like the code for materialized views is different from the index-creating code for table, incremental and seed. I don't know why, but maybe the fix would be to bring those methods in line.

[rubenhelsloot]

For now, I worked around this by adding a macro that overrides the default logic. Note the semicolon on line 4

{% macro postgres__get_create_materialized_view_as_sql(relation, sql) %}
    create materialized view if not exists {{ relation }} as {{ sql }};

    {% for _index_dict in config.get('indexes', []) -%}
        {{- get_create_index_sql(relation, _index_dict) -}};
    {%- endfor -%}

{% endmacro %}

[jtcohen6]

Thanks @rubenhelsloot! This looks like a duplicate of:

• [Regression] Materialized view with multiple indexes errors dbt-postgres#85

We just resolved it last week, but we need to backport it for inclusion in dbt-postgres v1.7.x + 1.8.x:

• Fix the semicolon semantics for indexes while respecting other bug fix. dbt-postgres#97

[rubenhelsloot]

Thanks for the quick response @jtcohen6!