"Aggregated Compressed Column Not Found" Error on Single id_number Aggregation Query in TimescaleDB

TimescaleDB and PostgreSQL
, , ,
1

We’re experiencing an intermittent issue with aggregation queries on compressed data in TimescaleDB (versions 2.16 and 2.17). Specifically, when querying a single id_number from a compressed hypertable and selecting a time period for more than a week, we encounter an error. However, this error does not occur when the query includes multiple id_number values. We have two seemingly identical environments, but only one of them exhibits this behavior.

Error Message:
ERROR: aggregated compressed column not found
DETAIL: Assertion ‘value_column_description != NULL’ failed.

Environment Details:

  • Operating System: Ubuntu 22.04
  • PostgreSQL Version: 16.4
  • TimescaleDB Version: Tested on 2.16 and 2.17

Steps to Reproduce:

  1. Set up compressed hypertable:
  • Create a hypertable (e.g., data_5min) with compression enabled.
  • Insert sample data and ensure the table has multiple chunks.
  1. Run Query:
  • Single ID (FAILS)
SELECT 
    id_number AS idNumber,
    AVG("value") AS "value",
    time_bucket('6 hour', ts) AS timeBucket
FROM 
    data_5min
WHERE 
    id_number IN (10) 
    AND ts >= '2024-10-15 00:00:00' 
    AND ts < '2024-10-27 00:00:00'
GROUP BY 
    id_number, timeBucket
ORDER BY 
    timeBucket;
  • Multiple ID (WORKS)
SELECT 
    id_number AS idNumber,
    AVG("value") AS "value",
    time_bucket('6 hour', ts) AS timeBucket
FROM 
    data_5min
WHERE 
    id_number IN (10, 20) 
    AND ts >= '2024-10-15 00:00:00' 
    AND ts < '2024-10-27 00:00:00'
GROUP BY 
    id_number, timeBucket
ORDER BY 
    timeBucket;

Additional Information:
This error does not appear in an identical environment running TimescaleDB 2.16.
Verified that compression was enabled and checked chunk statuses.

Expected Behavior:
The query with a single id_number should execute without errors, as it does when querying with multiple IDs.

2

Thanks for reporting @uasiddiqui.

Can you share the hypertable definition and what you have in the continuous aggregates?

3

Thanks for responding. I have tried to pull what i could find,

Hypertable definition:

 hypertable_schema | hypertable_name  |   owner    | num_dimensions | num_chunks | compression_enabled | tablespaces 
-------------------+------------------+------------+----------------+------------+---------------------+-------------
 public            | data_5min        |   admin    |              1 |         44 | t                   | 
(1 row)

Schema:

 column_name |        data_type         | is_nullable 
-------------+--------------------------+-------------
 id_number   | integer                  | NO
 value       | double precision         | NO
 ts          | timestamp with time zone | NO

For the second part, i couldn’t find continuous aggregates:

SELECT view_name, hypertable_name
FROM timescaledb_information.continuous_aggregates;

 view_name | hypertable_name 
-----------+-----------------
(0 rows)
4

Right! the data_5_min is already your hypertable. I was confused that it was a continuous aggregation but it’s just a regular aggregation. It seems a bug in this case.

Let’s double check with @sven, with is working actively in several compression improvements.

5

Thanks for highlighting it. Lets see if we get a solution. :slight_smile:

6

This looks like a bug, can you try to run the query after

set timescaledb.enable_vectorized_aggregation to false;

And additionally open an issue at Issues · timescale/timescaledb · GitHub.

1 Like
7

Thanks @sven for replying. This seems to solve the problem, the query is successful with this change.
Is this something that you could update in the code or do i need to add this in postgresql.conf so that i don’t loose it when the timescaledb container is stopped/removed ?

8

Thanks for confirming!

Yes, you can use ALTER SYSTEM SET or go to postgresql.conf.

Also, please, fill an issue for the case as you confirmed also the suggestion from Sven worked: Issues · timescale/timescaledb · GitHub

9

Thanks for all the quick help and solution. I have created the issue.