[CT-306] [Bug] Indexes config does not create indexes

[cleare-cl]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

The config for index creation in postgres developed in dbt-labs/dbt-core#3106 does not create indexes.

We have tried different configuration methods, including specifying different uniqueness of the indexes on the columns as well as running it only with one column.

The compiled dbt SQL did not include create index statement.

Expected Behavior

The indexes configuration in the model creates indexes of the specified columns when ran.

Steps To Reproduce

1. In a dbt model, set the indexes configuration

{{
config(
materialized = 'table',
indexes=[ {'columns': ['column1', 'column2', 'column3'] } ]
)
}}

2. Run the model

Relevant log output

No response

Environment

- OS: MacOS
- Python: 3.9.10
- dbt: 1.0.3
- Postgres: 14.2

What database are you using dbt with?

postgres

Additional Context

No response

[jtcohen6]

@cleare-cl Thanks for opening, and sorry for the delay getting back to you on this one!

The compiled dbt SQL did not include create index statement.

To clarify, did you try running the model (dbt run), and see that the create index statement was missing from the logs (logs/dbt.log), and that the index was indeed missing within Postgres?

Or was it that, after dbt compile or dbt run, you checked the "compiled SQL" for that model (such as target/compiled/.../model_with_indexes.sql), and noticed that it didn't contain any create index statements?

Today, dbt's "compiled SQL" includes just the compiled SQL written within that model. It doesn't include the "helper" statements within the materialization that persist docs, create indexes, move temp/backup tables out of the way, and so on. By a similar token, dbt's "run SQL" includes only the "main" statement run during the materialization (create table, insert into, etc). The creation of indexes happens in a separate statement afterward:

dbt-core/core/dbt/include/global_project/macros/materializations/models/table/table.sql

Line 56 in ed5df34

{% do create_indexes(target_relation) %}

There's been a long-lived discussion about supporting a "dry run" mode for materializations, with the idea that dbt should be able to print out everything it plans to do during the process of materializing a model: #4456

[olia-cl]

@jtcohen6 , thank you for your answer and sorry for the delay from my side.

I tried both: dbt compile and to run the model with dbt run. In both cases no indexes were created.

Initially I found out that the indexes configuration does not work by replacing the index macro with the new index configuration within the model. After running the model the existing indexes in postgres, that were previously created by the macro, were gone. After the second run no indexes were to be found. The create index statement was also missing in the logs/dbt.log.

After that I tried dbt compile and the index statement was also missing in the compiled SQL.

[jtcohen6]

@olia-cl Is there any chance you have custom materialization macros in your project, overriding the ones that ship within dbt-core's global project? The calls to the create_indexes macro are here in the default table and incremental materializations:

dbt-core/core/dbt/include/global_project/macros/materializations/models/table/table.sql

Line 41 in e03d35a

{% do create_indexes(target_relation) %}

dbt-core/core/dbt/include/global_project/macros/materializations/models/incremental/incremental.sql

Lines 69 to 71 in e03d35a

	{% if existing_relation is none or existing_relation.is_view or should_full_refresh() %}
	{% do create_indexes(target_relation) %}
	{% endif %}

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.

[wesleyguirra]

I'm having the same issue

[datasalaryman]

Having this issue now