Cannot use use_original_sql_pre_aggregations on Postgres
- 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 reuse an original_sql pre-aggregation stored in Postgres in a rollup pre-aggregation. Setting `use_original_sql_pre_aggregations: true` fails with the following error message during scheduled refresh:
> "... FROM pre_agg_tenant_123.base_positions_main AS \"main__base_positions\"..."
{"message":"Refresh Scheduler Error","error":"Error: relation \"pre_agg_tenant_123.base_positions_main\" does not exist",
When I introspect the database, I can see that Cube appends certain hashes to the original_sql table name. Those hashes are missing in the sql query to the database, which causes it to fail.
**Expected behavior**
It should be possible to reuse the original_sql pre-aggregation stored in Postgres, as stated in the docs. We have a heavy sql query in the original data model.
**Minimally reproducible Cube Schema**
```yaml
cubes:
- name: base_positions
public: false
sql: SELECT "id_1" AS position_id, "test" AS position_name
measures:
- name: position_count
type: count
dimensions:
- name: position_id
sql: position_id
type: string
primary_key: true
- name: position_name
sql: position_name
type: string
- name: position_date
sql: "date"
type: time
pre_aggregations:
- name: main
type: original_sql
external: false
- name: positions_per_month
measures:
- CUBE.position_count
time_dimension: CUBE.position_date
granularity: month
use_original_sql_pre_aggregations: true
```
**Version:**
0.34.19
Contributor guide
Assessment
This issue has not been assessed yet.