Get index name of a continuous aggregate table

1

i suddenly create a wrong index i need to drop. how get its name?

2

Hi @sarv_sarv !

I’m not sure what tools you’re using but here is some query that can help you. First, find the name of your continuous aggregates as it’s a hypertable:

select materialization_hypertable_schema || '.' || materialization_hypertable_name
 from  timescaledb_information.continuous_aggregates
where view_name = '<your continuous aggregates name>';

Example from a random database here:

select materialization_hypertable_schema || '.' || materialization_hypertable_name
 from  timescaledb_information.continuous_aggregates
where view_name = 'one_day_candle';
-- _timescaledb_internal._materialized_hypertable_11

Then I can use the output to build my next query:

\d+ _timescaledb_internal._materialized_hypertable_11
                           Table "_timescaledb_internal._materialized_hypertable_11"
┌──────────┬──────────────────────────┬───────────┬──────────┬─────────┬──────────┬──────────────┬─────────────┐
│  Column  │           Type           │ Collation │ Nullable │ Default │ Storage  │ Stats target │ Description │
├──────────┼──────────────────────────┼───────────┼──────────┼─────────┼──────────┼──────────────┼─────────────┤
│ bucket   │ timestamp with time zone │           │ not null │         │ plain    │              │             │
│ symbol   │ text                     │           │          │         │ extended │              │             │
...
Indexes:
    "_materialized_hypertable_11_bucket_idx" btree (bucket DESC)
    "_materialized_hypertable_11_symbol_bucket_idx" btree (symbol, bucket DESC)
Triggers:

As you can see the index name in the bottom, then you can drop it.

If you want a query to run it you can get it using a query to get the oid:

SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(_materialized_hypertable_11)$' COLLATE pg_catalog.default
  AND n.nspname OPERATOR(pg_catalog.~) '^(_timescaledb_internal)$' COLLATE pg_catalog.default
ORDER BY 2, 3;

And a query to get the index:

SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),
  pg_catalog.pg_get_constraintdef(con.oid, true), contype, condeferrable, condeferred, i.indisreplident, c2.reltablespace
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
  LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))
WHERE c.oid = '34562' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, c2.relname;
3

hi @jonatasdp thank you for answer. its exactly what im looking for.

1 Like