dbt-labs / dbt-labs/dbt-adapters

Composite Index column order unstable

Open
#1,341 0 comments 0 reactions 0 assignees View on GitHub
triage:product type:bug
Dominant language
Python
Stars
233
Forks
362
Avg merge
3d 22h
Merged PRs (30d)
9

Description

### Is this a new bug?

- [x] I believe this is a new bug
- [x] I have searched the existing issues, and I could not find an existing issue for this bug

### Which packages are affected?

- [ ] dbt-adapters
- [ ] dbt-tests-adapter
- [ ] dbt-athena
- [ ] dbt-athena-community
- [ ] dbt-bigquery
- [x] dbt-postgres
- [ ] dbt-redshift
- [ ] dbt-snowflake
- [ ] dbt-spark

### Current Behavior

```python
@dataclass(frozen=True, eq=True, unsafe_hash=True)
class PostgresIndexConfig(RelationConfigBase, RelationConfigValidationMixin):
"""
This config fallows the specs found here:
https://www.postgresql.org/docs/current/sql-createindex.html

The following parameters are configurable by dbt:
- name: the name of the index in the database, this isn't predictable since we apply a timestamp
- unique: checks for duplicate values when the index is created and on data updates
- method: the index method to be used
- column_names: the columns in the index

Applicable defaults for non-configurable parameters:
- concurrently: `False`
- nulls_distinct: `True`
"""

name: str = field(default="", hash=False, compare=False)
column_names: FrozenSet[str] = field(default_factory=frozenset, hash=True)
unique: bool = field(default=False, hash=True)
method: PostgresIndexMethod = field(default=PostgresIndexMethod.default(), hash=True)
```

The current implementation of PostgresIndexConfig uses a set for `column_names`, which makes sense for deduplication purposes, but is causing unstable column ordering which is crucial for composite indexes.
Downstream code like index creation is affected. Both name of the index as well as the index column ordering is unstable.

```sql
{%- macro postgres__update_indexes_on_materialized_view(relation, index_changes) -%}
{{- log("Applying UPDATE INDEXES to: " ~ relation) -}}

{%- for _index_change in index_changes -%}
{%- set _index = _index_change.context -%}

{%- if _index_change.action == "drop" -%}

{{ postgres__get_drop_index_sql(relation, _index.name) }}

{%- elif _index_change.action == "create" -%}

{{ postgres__get_create_index_sql(relation, _index.as_node_config) }}

{%- endif -%}
{{ ';' if not loop.last else "" }}

{%- endfor -%}

{%- endmacro -%}

{% macro postgres__get_create_index_sql(relation, index_dict) -%}
{%- set index_config = adapter.parse_index(index_dict) -%}
{%- set comma_separated_columns = ", ".join(index_config.columns) -%}
{%- set index_name = index_config.render(relation) -%}

create {% if index_config.unique -%}
unique
{%- endif %} index if not exists
"{{ index_name }}"
on {{ relation }} {% if index_config.type -%}
using {{ index_config.type }}
{%- endif %}
({{ comma_separated_columns }})
{%- endmacro %}

```

### Expected Behavior

Use a list instead of a set and run manual deduplication (perhaps a function [list] -> [list]) or just take the upstream list passed to the constructor.

### Steps To Reproduce

-

### Relevant log output

```shell

```

### Environment

```markdown
- OS:
- Python:
- dbt-adapters:
- :
```

### Additional Context

_No response_

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.