Updating to 2.0.0 on hypertable where chunks have been dropped fails

[brian-from-quantrocket]

Does TimescaleDB support upgrading to 2.0.0 on a database where chunks have previously been dropped with drop_chunks?

Using Timescale's Docker images, I am upgrading from timescale/timescaledb:1.7.3-pg12 to timescale/timescaledb:2.0.0-pg12.

I have two databases, each of which contains a similarly structured hypertable.

quantrocket_v2_realtime_us_stk_tick=# \d+ md.last_price
                                           Table "md.last_price"
 Column |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
 sid    | character varying(30)    |           | not null |         | extended |              | 
 dt     | timestamp with time zone |           | not null |         | plain    |              | 
 val    | real                     |           | not null |         | plain    |              | 
Indexes:
    "last_price_dt_idx" btree (dt)
    "last_price_sid_dt_idx" btree (sid, dt)
Triggers:
    ts_cagg_invalidation_trigger AFTER INSERT OR DELETE OR UPDATE ON md.last_price FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.continuous_agg_invalidation_trigger('1')
    ts_insert_blocker BEFORE INSERT ON md.last_price FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Child tables: _timescaledb_internal._hyper_1_92_chunk
Access method: heap

Both hypertables have compression enabled and both have continuous aggregates. The only difference between the two databases that I can see is that I have previously dropped chunks on one of the hypertables but not on the other. When I run ALTER EXTENSION timescaledb UPDATE on the database where no chunks have been dropped, it successfully updates to 2.0.0. But when I run the same command on the database where chunks have previously been dropped, it fails with the following error:

$ psql -X -d quantrocket_v2_realtime_us_stk_tick -c 'ALTER EXTENSION timescaledb UPDATE'
ERROR:  relation "_timescaledb_internal._hyper_1_1_chunk" does not exist
CONTEXT:  PL/pgSQL function inline_code_block line 5 at FOR over SELECT rows

Indeed, that relation does not exist:

quantrocket_v2_realtime_us_stk_tick=# \dt _timescaledb_internal.*
                           List of relations
        Schema         |            Name            | Type  |  Owner   
-----------------------+----------------------------+-------+----------
 _timescaledb_internal | _compressed_hypertable_2   | table | postgres
 _timescaledb_internal | _compressed_hypertable_4   | table | postgres
 _timescaledb_internal | _hyper_1_92_chunk          | table | postgres
 _timescaledb_internal | _hyper_3_91_chunk          | table | postgres
 _timescaledb_internal | _hyper_5_22_chunk          | table | postgres
 _timescaledb_internal | _hyper_5_33_chunk          | table | postgres
 _timescaledb_internal | _hyper_5_4_chunk           | table | postgres
 _timescaledb_internal | _hyper_5_55_chunk          | table | postgres
 _timescaledb_internal | _hyper_5_79_chunk          | table | postgres
 _timescaledb_internal | _hyper_6_21_chunk          | table | postgres
 _timescaledb_internal | _hyper_6_34_chunk          | table | postgres
 _timescaledb_internal | _hyper_6_3_chunk           | table | postgres
 _timescaledb_internal | _hyper_6_56_chunk          | table | postgres
 _timescaledb_internal | _hyper_6_80_chunk          | table | postgres
 _timescaledb_internal | _materialized_hypertable_5 | table | postgres
 _timescaledb_internal | _materialized_hypertable_6 | table | postgres
 _timescaledb_internal | bgw_job_stat               | table | postgres
 _timescaledb_internal | bgw_policy_chunk_stats     | table | postgres
 _timescaledb_internal | compress_hyper_2_94_chunk  | table | postgres
 _timescaledb_internal | compress_hyper_4_93_chunk  | table | postgres
(20 rows)

I believe _timescaledb_internal._hyper_1_1_chunk was previously dropped with drop_chunks. Is it possible that timescale is mistakenly looking for it anyway?

[mkindahl]

Hi @brian-from-quantrocket!

Thank you for the bug report. It does indeed look like it TimescaleDB is looking for the chunk that was dropped during the upgrade, but could you check the contents of _timescaledb_catalog.chunk and see if you find the chunk there and what the metadata says? The following query should suffice:

select * from _timescaledb_catalog.chunk where table_name = '_hyper_1_1_chunk';

[brian-from-quantrocket]

Lost that database but this is on a different database where the missing chunk is _hyper_1_13_chunk:

select * from _timescaledb_catalog.chunk where table_name = '_hyper_1_13_chunk';
 id | hypertable_id |      schema_name      |    table_name     | compressed_chunk_id | dropped 
----+---------------+-----------------------+-------------------+---------------------+---------
 13 |             1 | _timescaledb_internal | _hyper_1_13_chunk |                  19 | t

[mkindahl]

@brian-from-quantrocket Thanks, it was what I suspected. If you have a continuous aggregate on top of a hypertable, the chunks are not removed, they are just marked as dropped, and the update script did not take that into account. The fix is straightforward, but we need to write a test as well to avoid the problem in the future.