I have a timescaledb that stores data from Zabbix, so we’ve always just used scripts from Zabbix to create and maintain hypertables.
Now I’m trying to run a migration script from zabbix, but I get this error:
ERROR: chunk _hyper_1_1503_chunk has no dimension slices
So I start up psql and run a simple SELECT * FROM history LIMIT 1, but I get the same error.
No matter what query I run against the hypertables, I get the same error.
Zabbix runs fine and doesn’t seem to have any problems inserting and reading data from the tables.
What can I do to query data in psql? The migration script I want to run exports the data from the tables to a csv file, creates a new table with new schema and imports the data again, but it fails on the \copy export query
# select * from _timescaledb_catalog.hypertable;
id | schema_name | table_name | associated_schema_name | associated_table_prefix | num_dimensions | chunk_sizing_func_schema | chunk_sizing_func_name | chunk_target_size | compression_state | compressed_hypertable_id | status
----+-----------------------+---------------------------+------------------------+-------------------------+----------------+--------------------------+--------------------------+-------------------+-------------------+--------------------------+--------
17 | _timescaledb_internal | _compressed_hypertable_17 | _timescaledb_internal | _hyper_17 | 0 | _timescaledb_functions | calculate_chunk_interval | 0 | 2 | | 0
8 | _timescaledb_internal | _compressed_hypertable_8 | _timescaledb_internal | _hyper_8 | 0 | _timescaledb_functions | calculate_chunk_interval | 0 | 2 | | 0
9 | _timescaledb_internal | _compressed_hypertable_9 | _timescaledb_internal | _hyper_9 | 0 | _timescaledb_functions | calculate_chunk_interval | 0 | 2 | | 0
10 | _timescaledb_internal | _compressed_hypertable_10 | _timescaledb_internal | _hyper_10 | 0 | _timescaledb_functions | calculate_chunk_interval | 0 | 2 | | 0
11 | _timescaledb_internal | _compressed_hypertable_11 | _timescaledb_internal | _hyper_11 | 0 | _timescaledb_functions | calculate_chunk_interval | 0 | 2 | | 0
12 | _timescaledb_internal | _compressed_hypertable_12 | _timescaledb_internal | _hyper_12 | 0 | _timescaledb_functions | calculate_chunk_interval | 0 | 2 | | 0
13 | _timescaledb_internal | _compressed_hypertable_13 | _timescaledb_internal | _hyper_13 | 0 | _timescaledb_functions | calculate_chunk_interval | 0 | 2 | | 0
14 | _timescaledb_internal | _compressed_hypertable_14 | _timescaledb_internal | _hyper_14 | 0 | _timescaledb_functions | calculate_chunk_interval | 0 | 2 | | 0
1 | public | history | _timescaledb_internal | _hyper_1 | 1 | _timescaledb_functions | calculate_chunk_interval | 0 | 1 | 8 | 0
7 | public | trends_uint | _timescaledb_internal | _hyper_7 | 1 | _timescaledb_functions | calculate_chunk_interval | 0 | 1 | 14 | 0
16 | public | auditlog | _timescaledb_internal | _hyper_16 | 1 | _timescaledb_functions | calculate_chunk_interval | 0 | 1 | 17 | 0
18 | _timescaledb_internal | _compressed_hypertable_18 | _timescaledb_internal | _hyper_18 | 0 | _timescaledb_functions | calculate_chunk_interval | 0 | 2 | | 0
15 | public | history_bin | _timescaledb_internal | _hyper_15 | 1 | _timescaledb_functions | calculate_chunk_interval | 0 | 1 | 18 | 0
2 | public | history_uint | _timescaledb_internal | _hyper_2 | 1 | _timescaledb_functions | calculate_chunk_interval | 0 | 1 | 9 | 0
5 | public | history_str | _timescaledb_internal | _hyper_5 | 1 | _timescaledb_functions | calculate_chunk_interval | 0 | 1 | 10 | 0
3 | public | history_log | _timescaledb_internal | _hyper_3 | 1 | _timescaledb_functions | calculate_chunk_interval | 0 | 1 | 12 | 0
4 | public | history_text | _timescaledb_internal | _hyper_4 | 1 | _timescaledb_functions | calculate_chunk_interval | 0 | 1 | 11 | 0
6 | public | trends | _timescaledb_internal | _hyper_6 | 1 | _timescaledb_functions | calculate_chunk_interval | 0 | 1 | 13 | 0
(18 rows)
Is the problem that the compressed hypertables don’t have any dimensions?
I should probably add that I moved the tables from schema zabbixdb to schema public using ALTER TABLE ... SET SCHEMA ...
I can select from child tables without error:
# select * from _timescaledb_internal._hyper_1_13444_chunk limit 1;
itemid | clock | value | ns
--------+------------+-------+----------
23252 | 1715817632 | 0 | 48849584
Hi @einsibjani , probably something messaged during the SET SCHEMA. Can you rollback the schema and check if it still works?
I tried that:
> ALTER TABLE history_uint SET SCHEMA zabbixdb;
ALTER TABLE
> select * from zabbixdb.history_uint limit 1;
ERROR: chunk _hyper_2_1504_chunk has no dimension slices
I discovered that if I add a constraint on the clock column to the query, then I get results
That’s great Einar!
Thanks for sharing the way to fix it 