cube-js/cube

Cannot create index for original_sql pre-aggregation on Postgres

Open

#7,420 opened on Nov 15, 2023

View on GitHub
 (3 comments) (0 reactions) (0 assignees)Rust (1,965 forks)batch import
help wantedpre-aggregations

Repository metrics

Stars
 (19,563 stars)
PR merge metrics
 (Avg merge 5d 16h) (138 merged PRs in 30d)

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

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