Repository: dbt-labs/dbt-redshift (language: python)

Issue:
[Bug] `persist_docs` not working properly with materialized views
### 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

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:

```sql
-- models/my_mv.sql
{{
    config(
        materialized = 'materialized_view',
        persist_docs = {"relation": true}
    )
}}

select * from foo
```

```yml
# models/sch.yml
models:
  - name: my_mv
    description: Lorem ipsum
```

Build mv:

```sh
$ 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

```markdown
- OS: macOS
- Python: 3.11.9
- dbt-core: 1.8.1
- dbt-redshift: 1.8.0
```


### Additional Context

Instead of:

```sql
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:

```sql
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:

```sql
-- 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 %}
```

```sh
$ 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
```


Discussion / hints:
Relevant: https://github.com/dbt-labs/dbt-postgres/issues/11
@jeremyyeo can we close this? Did we address this case as part of https://github.com/dbt-labs/dbt-postgres/issues/11 ?
> @jeremyyeo can we close this? Did we address this case as part of [dbt-labs/dbt-postgres#11](https://github.com/dbt-labs/dbt-postgres/issues/11) ?

Unfortunately not because the right syntax for mv comments on redshift is equivalent to comments on views (but for postgres, they differ):

```sql
-- 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.

Fix patch:
diff --git a/.changes/unreleased/Fixes-20240715-143507.yaml b/.changes/unreleased/Fixes-20240715-143507.yaml
new file mode 100644
index 000000000..e3dd2f0bf
--- /dev/null
+++ b/.changes/unreleased/Fixes-20240715-143507.yaml
@@ -0,0 +1,6 @@
+kind: Fixes
+body: Fix materialized views comment syntax
+time: 2024-07-15T14:35:07.412274+12:00
+custom:
+    Author: jeremyyeo
+    Issue: "837"
diff --git a/dbt/include/redshift/macros/adapters.sql b/dbt/include/redshift/macros/adapters.sql
index 5da047f58..8b74933bd 100644
--- a/dbt/include/redshift/macros/adapters.sql
+++ b/dbt/include/redshift/macros/adapters.sql
@@ -276,9 +276,24 @@
   {% endif %}
 {% endmacro %}
 
+{#
+  Copied from the postgres-adapter.
+#}
+{% 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 %}
 
 {% macro redshift__alter_relation_comment(relation, comment) %}
-  {% do return(postgres__alter_relation_comment(relation, comment)) %}
+  {%- set escaped_comment = escape_comment(comment) -%}
+  {%- set relation_type = 'view' if relation.type == 'materialized_view' else relation.type -%}
+  comment on {{ relation_type }} {{ relation }} is {{ escaped_comment }};
 {% endmacro %}
 
 


Test patch:
diff --git a/tests/functional/adapter/test_persist_docs.py b/tests/functional/adapter/test_persist_docs.py
index 6eeaf881c..cb5e632b6 100644
--- a/tests/functional/adapter/test_persist_docs.py
+++ b/tests/functional/adapter/test_persist_docs.py
@@ -1,6 +1,7 @@
 import json
 import pytest
 
+from dbt.tests.adapter.materialized_view import files
 from dbt.tests.util import run_dbt
 
 from dbt.tests.adapter.persist_docs.test_persist_docs import (
@@ -10,6 +11,21 @@
     BasePersistDocsCommentOnQuotedColumn,
 )
 
+_MATERIALIZED_VIEW_PROPERTIES__SCHEMA_YML = """
+version: 2
+models:
+  - name: my_materialized_view
+    description: |
+      Materialized view model description "with double quotes"
+      and with 'single  quotes' as welll as other;
+      '''abc123'''
+      reserved -- characters
+      80% of statistics are made up on the spot
+      --
+      /* comment */
+      Some $lbl$ labeled $lbl$ and $$ unlabeled $$ dollar-quoting
+"""
+
 
 class TestPersistDocs(BasePersistDocs):
     @pytest.mark.flaky
@@ -62,3 +78,31 @@ def test_comment_on_late_binding_view(self, project):
 
         no_docs_node = catalog_data["nodes"]["model.test.no_docs_model"]
         self._assert_has_view_comments(no_docs_node, False, False)
+
+
+class TestPersistDocsWithMaterializedView(BasePersistDocs):
+    @pytest.fixture(scope="class", autouse=True)
+    def seeds(self):
+        return {"my_seed.csv": files.MY_SEED}
+
+    @pytest.fixture(scope="class")
+    def models(self):
+        return {
+            "my_materialized_view.sql": files.MY_MATERIALIZED_VIEW,
+        }
+
+    @pytest.fixture(scope="class")
+    def properties(self):
+        return {
+            "schema.yml": _MATERIALIZED_VIEW_PROPERTIES__SCHEMA_YML,
+        }
+
+    @pytest.mark.flaky
+    def test_has_comments_pglike(self, project):
+        run_dbt(["docs", "generate"])
+        with open("target/catalog.json") as fp:
+            catalog_data = json.load(fp)
+        assert "nodes" in catalog_data
+        assert len(catalog_data["nodes"]) == 2
+        view_node = catalog_data["nodes"]["model.test.my_materialized_view"]
+        assert view_node["metadata"]["comment"].startswith("Materialized view model description")