dbt-labs / dbt-labs/dbt-adapters
Composite Index column order unstable
- 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
Assessment
This issue has not been assessed yet.