[ISSUE] Can't move chunks. "there is no previously clustered index for table"

[pavanfhw]

Describe the bug I'm using timescaledb-single chart with prometheus (+promscale) I'm trying to create a data retention policy following this and this For testing pourpuses, I created a tablespace then I run the move_chunk function:

select move_chunk(chunk => '_timescaledb_internal._hyper_54_22_chunk', destination_tablespace => 'cold', index_destination_tablespace => 'cold');
ERROR:  there is no previously clustered index for table "_hyper_54_22_chunk"

Do I need to run any aditional steps? Timescaledb does not create the necessary indexes for move_chunks action? The final intention is to automate moving old chunks to a second slower storage mounted on the container.

To Reproduce Install helm chart timescaledb-single Deploy promscale and prometheus

Expected behavior Chunk to be moved

Deployment

• What is in your values.yaml
• What version of the Chart are you using? 0.11.0
• What is your Kubernetes Environment (for exampe: GKE, EKS, minikube, microk8s) k3os

Deployment Please share some details of what is in your Kubernetes environment, for example:

NAME                                  READY   STATUS      RESTARTS   AGE    ROLE
pod/timescaledb-patroni-k1--1-jrspj   0/1     Completed   0          2d4h   
pod/timescaledb-0                     1/1     Running     0          38h    master

NAME                          TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)    AGE    ROLE
service/timescaledb-config    ClusterIP   None            <none>        8008/TCP   2d4h   
service/timescaledb-replica   ClusterIP   10.43.129.227   <none>        5432/TCP   2d4h   replica
service/timescaledb           ClusterIP   10.43.21.7      <none>        5432/TCP   2d4h   master

NAME                           READY   AGE    ROLE
statefulset.apps/timescaledb   1/1     2d4h   

NAME                               COMPLETIONS   DURATION   AGE    ROLE
job.batch/timescaledb-patroni-k1   1/1           11s        2d4h   

NAME                               DATA   AGE    ROLE
configmap/timescaledb-patroni      1      2d4h   
configmap/timescaledb-pgbackrest   1      2d4h   
configmap/timescaledb-scripts      8      2d4h   

NAME                            ENDPOINTS          AGE    ROLE
endpoints/timescaledb-replica   <none>             2d4h   replica
endpoints/timescaledb-config    10.42.3.115:8008   2d4h   
endpoints/timescaledb           10.42.3.115:5432   2d4h   

NAME                                                 STATUS   VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS    AGE    ROLE
persistentvolumeclaim/wal-volume-timescaledb-0       Bound    pvc-bc20185d-5e41-423f-95a1-5e3197575f42   1Gi        RWO            piraeus-sc-r2   2d4h   
persistentvolumeclaim/storage-volume-timescaledb-0   Bound    pvc-f155b7ed-87da-4e37-8031-42b3ae79bf37   100Gi      RWO            piraeus-sc-r2   2d4h

[paulfantom]

@timescale/o11y-applications seems like this is an issue in promscale itself.

cc @VineethReddy02

[dsupru]

Having the same issue. This prevents us from using move_chunks altogether because index_destination_tablespace is a required argument. Not running it in K8s by the way, deployed on a VM

[paulfantom]

I transferred this issue to promscale repository as it seems more like the problem with the application than helm charts.

[niksajakovljevic]

Hey! The move_chunk command requires either existing hypertable index to be marked as CLUSTERED (in PostgreSQL) or hypertable index needs to be supplied in the command explicitly. So for example if we have a following metric hypertable:

tsdb=> \d+ prom_data.go_gc_duration_seconds
                                          Table "prom_data.go_gc_duration_seconds"
  Column   |           Type           | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------+--------------------------+-----------+----------+---------+---------+-------------+--------------+-------------
 time      | timestamp with time zone |           | not null |         | plain   |             |              |
 value     | double precision         |           | not null |         | plain   |             |              |
 series_id | bigint                   |           | not null |         | plain   |             |              |
Indexes:
    "data_series_id_time_25" UNIQUE, btree (series_id, "time") INCLUDE (value)
Triggers:
    ts_insert_blocker BEFORE INSERT ON prom_data.go_gc_duration_seconds FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Child tables: _timescaledb_internal._hyper_129545_191127_chunk,
              _timescaledb_internal._hyper_129545_191653_chunk,
              _timescaledb_internal._hyper_129545_192058_chunk
Access method: heap
Options: autovacuum_vacuum_insert_threshold=50000, autovacuum_vacuum_insert_scale_factor=2.0, autovacuum_analyze_threshold=50000, autovacuum_analyze_scale_factor=0.5

And we want to move chunk _timescaledb_internal._hyper_129545_192058_chunk from tablespace ts1 to ts2, we need to run:

select move_chunk('_timescaledb_internal._hyper_129545_192058_chunk', 'ts1', 'ts2', 'prom_data.data_series_id_time_25')

I hope this helps. I believe our docs should be improved to make this more obvious (https://docs.timescale.com/api/latest/hypertable/move_chunk/)