[Bug] persist_docs not working properly with materialized views

[jeremyyeo]

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

For materialized views - we're generating an incorrect statement when we go to add comments to it.

Expected Behavior

Comments should be added to materialized views without errors.

Steps To Reproduce

Project setup:

-- models/my_mv.sql
{{
    config(
        materialized = 'materialized_view',
        persist_docs = {"relation": true}
    )
}}

select * from foo

# models/sch.yml
models:
  - name: my_mv
    description: Lorem ipsum

Build mv:

$ dbt --debug build -s my_mv

01:41:34  On model.my_dbt_project.my_mv: /* {"app": "dbt", "dbt_version": "1.8.1", "profile_name": "all", "target_name": "rs", "node_id": "model.my_dbt_project.my_mv"} */

        create materialized view "dev"."public"."my_mv"
        backup yes
        diststyle even
        
        
        auto refresh no
    as (
        select * from foo
    )
01:41:36  SQL status: SUCCESS in 1.0 seconds
01:41:36  Using redshift connection "model.my_dbt_project.my_mv"
01:41:36  On model.my_dbt_project.my_mv: /* {"app": "dbt", "dbt_version": "1.8.1", "profile_name": "all", "target_name": "rs", "node_id": "model.my_dbt_project.my_mv"} */

    
  
  comment on materialized_view "dev"."public"."my_mv" is $dbt_comment_literal_block$Lorem ipsum$dbt_comment_literal_block$;
01:41:36  Redshift adapter: Redshift error: syntax error at or near "materialized_view"
01:41:36  On model.my_dbt_project.my_mv: ROLLBACK
01:41:36  On model.my_dbt_project.my_mv: Close
01:41:36  Database Error in model my_mv (models/my_mv.sql)
  syntax error at or near "materialized_view"
  compiled Code at target/run/my_dbt_project/models/my_mv.sql
01:41:36  Sending event: {'category': 'dbt', 'action': 'run_model', 'label': '2a1b0735-1097-4795-9735-6c2c86159586', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x107fe42d0>]}
01:41:36  1 of 1 ERROR creating sql materialized_view model public.my_mv ................. [ERROR in 4.50s]

Relevant log output

No response

Environment

- OS: macOS
- Python: 3.11.9
- dbt-core: 1.8.1
- dbt-redshift: 1.8.0

Additional Context

Instead of:

comment on materialized_view "dev"."public"."my_mv" is $dbt_comment_literal_block$Lorem ipsum$dbt_comment_literal_block$;

The right syntax is just like the one we have for views:

comment on view "dev"."public"."my_mv" is $dbt_comment_literal_block$Lorem ipsum$dbt_comment_literal_block$;
select obj_description('public.my_mv'::regclass);
-- Lorem ipsum

Workaround

As a temp workaround, one could modify the statements generated by the default (postgres) alter_relation_comment macro like so:

-- macros/alter_relation_comment.sql
{% macro redshift__alter_relation_comment(relation, comment) %}
  {% set escaped_comment = escape_comment(comment) %}

  {% set rel_type = 'view' if relation.type == 'materialized_view' else relation.type %}

  comment on {{ rel_type }} {{ relation }} is {{ escaped_comment }};
{% endmacro %}

{% macro escape_comment(comment) -%}
  {% if comment is not string %}
    {% do exceptions.raise_compiler_error('cannot escape a non-string: ' ~ comment) %}
  {% endif %}
  {%- set magic = '$dbt_comment_literal_block$' -%}
  {%- if magic in comment -%}
    {%- do exceptions.raise_compiler_error('The string ' ~ magic ~ ' is not allowed in comments.') -%}
  {%- endif -%}
  {{ magic }}{{ comment }}{{ magic }}
{%- endmacro %}

$ dbt run

03:54:08  On model.my_dbt_project.my_mv: /* {"app": "dbt", "dbt_version": "1.8.1", "profile_name": "all", "target_name": "rs", "node_id": "model.my_dbt_project.my_mv"} */
  comment on view "dev"."public"."my_mv" is $dbt_comment_literal_block$Lorem ipsum$dbt_comment_literal_block$;
03:54:08  SQL status: SUCCESS in 0.0 seconds

[jeremyyeo]

Relevant: dbt-labs/dbt-postgres#11

[colin-rogers-dbt]

@jeremyyeo can we close this? Did we address this case as part of dbt-labs/dbt-postgres#11 ?

[jeremyyeo]

@jeremyyeo can we close this? Did we address this case as part of dbt-labs/dbt-postgres#11 ?

Unfortunately not because the right syntax for mv comments on redshift is equivalent to comments on views (but for postgres, they differ):

-- redshift views
comment on view ...
-- redshift materialized views
comment on view ...

-- postgres views
comment on view ...
-- postgres materialized view
comment on materialized view ...

I'll try to add a fix for redshift as suggested above.