[Bug]: Index on aggregation matview ignore schema namespace

[stalkerg]

What type of bug is this?

Incorrect result, Other

What subsystems and features are affected?

Continuous aggregate

What happened?

You can't create two indexes with same name for two different continuous aggregation in two different schemas.

TimescaleDB version affected

2.15.3

PostgreSQL version used

16.3

What operating system did you use?

Cloud

What installation method did you use?

Not applicable

What platform did you run on?

Timescale Cloud

Relevant log output and stack trace

No response

How can we reproduce the bug?

Just create 2 continuous aggregations in two schemas and try to add indexes with the same name. 
It's happened because actual schema name for a such indexes is `_timescaledb_internal`.

[stalkerg]

It's basically bug in #4430 implementation.

[mkindahl]

@stalkerg Thanks for the bug report. Trivial to reproduce:

create table conditions(
    time timestamptz not null,
    location_id integer,
    device_id integer,
    temperature numeric,
    humidity numeric
);

select * from create_hypertable('conditions', 'time', migrate_data => true);

insert into conditions
select time, (random()*10 + 1)::int, (random()*10 + 1)::int, random()*80 - 40, random()*100
from generate_series(now() - interval '28 days', now(), '1 hour') as time;

create materialized view hourly.conditions_summary
with (timescaledb.continuous) as
select device_id,
       time_bucket('1 hour'::interval, "time") as bucket,
       avg(temperature),
       max(temperature),
       min(temperature)
from conditions
group by device_id, bucket;

create materialized view daily.conditions_summary
with (timescaledb.continuous) as
select device_id,
       time_bucket(interval '1 day', "time") as bucket,
       avg(temperature),
       max(temperature),
       min(temperature)
from conditions
group by device_id, bucket;

create index my_index on hourly.conditions_summary (device_id);
create index my_index on daily.conditions_summary (device_id);

[fabriziomello]

It is not a but, it's a design decision because the underlying materialization hypertable is always created in the internal _timescaledb_internal schema and Postgres always create the index in the same index of the table.

But you can change the underlying materialization schema to another by doing something like:

ALTER TABLE _timescaledb_internal._materialized_hypertable_2 SET SCHEMA hourly;
ALTER TABLE _timescaledb_internal._materialized_hypertable_3 SET SCHEMA daily;

Then you can safely create the indexes with the same name but in different schemas.

[stalkerg]

Firstly, thank you for the extra workaround. Second, I understand the current design and why it's happened, but it still has bad UX and unexpected/undocumented behavior. I suppose a materialization hypertable should be created in the specific schema or should be a way to set it during creation. I have an idea why it was difficult to do it as a PG extension, but still.