[Bug]: counter_agg fail when inserting in a compressed chunk (out of order points)

[Alois41]

What type of bug is this?

Crash

What subsystems and features are affected?

Compression

What happened?

Issue:
When I insert data in a compressed chunk, the counter_agg request return a 'out of order points: points must be submitted in time-order' ERROR.
In my script, the error is produced only if the new data point is between the existing data point.

The error can be fixed by executing 'compress_chunk' again or waiting for the background jobs.

TimescaleDB version affected

2.20

PostgreSQL version used

17

What operating system did you use?

Ubuntu 22.04 x64

What installation method did you use?

Docker

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

DROP TABLE
CREATE TABLE
INSERT 0 1
NOTICE:  migrating data to chunks
DETAIL:  Migration might take a while depending on the amount of data.
 create_hypertable 
-------------------
 (195,t)
(1 row)

ALTER TABLE
                compress_chunk                
----------------------------------------------
 _timescaledb_internal._hyper_195_19310_chunk
(1 row)

INSERT 0 1
ERROR:  out of order points: points must be submitted in time-order

How can we reproduce the bug?

DROP TABLE IF EXISTS temp_ts;
CREATE TABLE temp_ts(device_id bigint NOT NULL,value double precision NOT NULL,datetime timestamp with time zone NOT NULL);
INSERT INTO temp_ts( device_id, value, datetime) VALUES 
(1,1,'2025-02-14 11:00:00+00'),(2,1,'2025-02-14 10:00:00+00');
SELECT create_hypertable('temp_ts', by_range('datetime'), migrate_data => true);
ALTER TABLE temp_ts SET (timescaledb.segmentby = 'device_id', timescaledb.orderby = 'datetime DESC',  timescaledb.enable_columnstore);
SELECT compress_chunk(c) FROM show_chunks('temp_ts', NULL, NULL) c;

-- NEW INSERT ON COMPRESSED CHUNK
INSERT INTO temp_ts(device_id, value, datetime) VALUES 
(1,1,'2025-02-14 10:30:00+00');
;

SELECT counter_agg(datetime, value) AS counter_agg
FROM temp_ts;

[akuzm]

Thanks for the script, I can reproduce the error on timescaledb 2.21.0-dev and timescaledb_toolkit-1.20.0-dev.

[akuzm]

As a workaround, something like this might help:

SELECT counter_agg(datetime, value) AS counter_agg
FROM (SELECT * FROM temp_ts ORDER BY datetime) t;

[Alois41]

As a workaround, something like this might help:

SELECT counter_agg(datetime, value) AS counter_agg
FROM (SELECT * FROM temp_ts ORDER BY datetime) t;

Thank you for your answer,
this workaround is indeed working, but I tried to simplify the reproduction here, In my real use case, the counter_agg is called in my Continuous Aggregate. Therefore, I cannot use subqueries.
I'm trying to make a reproduction script with everything.