Cannot create index for original_sql pre-aggregation on Postgres

[TobiasMaehl-pIX]

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

[github-actions]

If you are interested in working on this issue, please leave a comment below and we will be happy to assign the issue to you.
If this is the first time you are contributing a Pull Request to Cube.js, please check our contribution guidelines.
You can also post any questions while contributing in the #contributors channel in the Cube.js Slack.

[igorlukanin]

I was able to reproduce this with Cube v0.36.2 (latest), same behavior as reported:

[subhampasari]

I solved it using a custom trigger which defines a function to create the index.
Example for postgres

CREATE OR REPLACE FUNCTION trg_create_pre_aggregate_table_func()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
    obj record;    
BEGIN
  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag in ('CREATE TABLE','CREATE TABLE AS')
  LOOP
        if   obj.object_identity ~ 'prod_pre_aggregations.<insert cohortName_preaggregationName here'  
        THEN
			EXECUTE('CREATE INDEX ON ' || obj.object_identity ||  ' (<insert column name on which you want the index>)');
			INSERT INTO prod_pre_aggregations.logs ("table_name") VALUES (obj.object_identity); --->this is only for validation that the index creation command executed successfully, but cube deletes the table somehow, so you can use this for testing, but the table will be deleted when pre aggregation is triggered from cube
        end if;
  END LOOP;
END;
$$;

CREATE EVENT TRIGGER trg_create_cohort_table ON ddl_command_end
WHEN TAG IN ('CREATE TABLE','CREATE TABLE AS')
EXECUTE PROCEDURE trg_create_pre_aggregate_table_func();