cube-js / cube-js/cube

Cannot create index for original_sql pre-aggregation on Postgres

Open
#7,420 3 comments 0 reactions 0 assignees View on GitHub
help wanted pre-aggregations
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**Describe the bug**
We would like to create an index on an original_sql pre-aggregation stored in Postgres.
Cube fails to create said index, because it incorrectly prepends the cube name to the specified index column. The column of the implicitly created table does not contain that cube name, which leads to an error.

**Relevant extract of the error message:**
> {"message":"Error while querying","queueId":128,"processingId":128,"queueSize":0,"duration":587,"queryKey":[["CREATE TABLE pre_agg_tenant_123.base_positions_main AS SELECT **position_id**, position_name FROM positions"
...
> "sql":
["CREATE INDEX positions_main_position_id_index ON pre_agg_tenant_123.base_positions_main (\"**base_positions__position_id**\")",[]]}],
"createTableIndexes":[{"indexName":"base_positions_main_position_id_index","type":"regular","columns ["\"**base_positions__position_id**\""]}],
"readOnly":false,"expandedPartition":true,"priority":-1},"addedToQueueTime":1700057570643,"error":"error: column \"**base_positions__position_id**\" does not exist

**Expected behavior**
It should be possible to create the index. Cube should not prepend the cube name to the index column while generating the sql command to create the index:
`CREATE INDEX positions_main_position_id_index ON pre_agg_tenant_123.base_positions_main (\"position_id\")`
instead of
`CREATE INDEX positions_main_position_id_index ON pre_agg_tenant_123.base_positions_main (\"base_positions__position_id\")`

**Minimally reproducible Cube Schema**
```yaml
cubes:
- name: base_positions
public: false
sql: SELECT "id_1" AS position_id, "test" AS position_name
dimensions:
- name: position_id
sql: position_id
type: string
primary_key: true
- name: position_name
sql: position_name
type: string

pre_aggregations:
- name: main
type: original_sql
external: false
indexes:
- name: position_id_index
columns:
- position_id
# Also tried:
# - CUBE.position_id
# - base_positions.position_id
# - base_positions_main.position_id
```

**Version:**
0.34.19

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.