[Bug] Materialized View Documentation fails in PostgreSQL

[ocastaneda-aily]

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

When deploying to PostgreSQL, running a model for a Materialized View that has documentation in a schema.yml associated, it tries to run the command "comment on materialized_view ...." and PostgreSQL expects "comment on materialized view ... " (without the underscore character) otherwise it throws Syntax Error - which is happening now.

The problem is that DBT model config type for MV materialization is defined as "materialized_view" and this is not translated as "comment on materialized view ... " when deploying the comment on PostgreSQL.

Expected Behavior

DBT generates the correct comment command for PostgreSQL for Materialzied Views, which is "comment on materialized view ..." (without the underscore)

Steps To Reproduce

1- Create a model with materialization of materialized_view 2- Add description with a schema.yml 3- Execute dbt run -s configured to deploy on a PostgreSQL server

Relevant log output

0m17:20:21.111539 [error] [MainThread]:   Database Error in model mv_test (models/mv_test.sql)
  syntax error at or near "materialized_view"
  LINE 5:   comment on materialized_view "my_database"."...
                       ^
  compiled Code at target/run/dbt_proj/models/mv_test.sql
17:20:21.111773 [info ] [MainThread]: 
17:20:21.112015 [info ] [MainThread]: Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Environment

- OS: macOS Sonoma v14.3
- Python: 3.10.6
- dbt: 1.7.7

Which database adapter are you using with dbt?

postgres

Additional Context

No response

[dbeatty10]

Thanks for reporting this @ocastaneda-aily !

### Reprex

Enable [`persist_docs`](https://docs.getdbt.com/reference/resource-configs/persist_docs) within `dbt_project.yml`: `dbt_project.yml` ```yaml name: "my_project" version: "1.0.0" config-version: 2 profile: "postgres" # ... models: my_project: +persist_docs: relation: true ``` `models/my_model.sql` ```sql {{ config(materialized="table") }} select 1 as id ``` Running this command works just fine: ```shell dbt run -s my_model ``` But then change it to be a materialized view: ```sql {{ config(materialized="materialized_view") }} select 1 as id ``` Re-running this command will give the following error: ```shell dbt run -s my_model ``` Error: ``` 21:15:59 Database Error in model my_model (models/my_model.sql) syntax error at or near "materialized_view" LINE 5: comment on materialized_view "postgres"."dbt_dbeatty"."my_... ```

Root cause

The root cause is the combination of the code within the following locations:

https://github.com/dbt-labs/dbt-postgres/blob/cb1bdd578a556e39eea5f7b847c3aaeec70629c5/dbt/include/postgres/macros/adapters.sql#L199

https://github.com/dbt-labs/dbt-adapters/blob/d6075056d80660b877b14f57dd4792fa7136e914/dbt/adapters/contracts/relation.py#L16

Summary

Since this looks like an issue that is specific to dbt-postgres, I'm going to transfer it to that repo, but it may end up moving to dbt-adapters.