[Bug]: lower bound cannot equal upper bound when executing refresh_continuous_aggregate(...);

[H25E]

What type of bug is this?

Unexpected error

What subsystems and features are affected?

Continuous aggregate

What happened?

When executing refresh_continuous_aggregate(..); on a continuous aggregate I get a lower bound cannot equal upper bound error.

TimescaleDB version affected

2.10.1

PostgreSQL version used

15.2

What operating system did you use?

Debian 11 (bullseye)

What installation method did you use?

Deb/Apt

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

2023-03-30 11:48:45.880 UTC [3125037] pgadmin@genins ERROR:  lower bound cannot equal upper bound
2023-03-30 11:48:45.880 UTC [3125037] pgadmin@genins CONTEXT:  SQL statement "INSERT INTO _timescaledb_internal._materialized_hypertable_113 SELECT * FROM _timescaledb_internal._partial_view_113 AS I WHERE I."timestamp" >= '4714-11-24 00:00:00 BC' AND I."timestamp" < '2023-03-20 00:00:00' ;"
2023-03-30 11:48:45.880 UTC [3125037] pgadmin@genins STATEMENT:   CALL refresh_continuous_aggregate('timescale.blackpearl_week_rt',NULL,'1 week'::INTERVAL);

How can we reproduce the bug?

It's really difficult to say. I have two similar hypertables where I'm creating very similar continuous aggregates. The ones from (let's say) ht_a are working well but the ones from ht_b are returning me this error when I call refresh_continuous_aggregate() on them. The main difference between the hypertables it's that ht_a has aprox 50 columns and ht_b has aprox 100 columns. They are feed once per second and I'm making 1 minute, 1 hour, 1 day, 1 week & 1 month continuous aggregates using the time_bucket hyperfunction for the timestamp column and different aggregation functions for the "value" columns.

In ht_a all CA can be refreshed properly without any error, in ht_b any CA can't be refreshed. Nor manually nor by background policies. I have tried playing with the start and end offsets, setting big and small updating intervals, but I always get the same kind of error. Otherwise, ht_b works properly.

When I launch the manual refresh it starts to process the query during some minutes and then returns the error. The elapsed time between the query launch and the returned error it's always the same for the same update interval.

If any extra info it's needed make comment and I will add it.

[pmwkaa]

@H25E Thank you for sharing this. If there is a chance you could provide a simplest reproducible test case, that would be very helpful 🙏

[H25E]

Yeah, I understand that the post it's like I have done A and B, A works but B doesn't but A and B are the same. I think today could have some time to narrow a little bit the issue and increase the reproducibility of the problem. I will edit this message when I have more info about it.

[H25E]

Ok, this can be a little bit embarrassing but it was totally my fault. The continuous aggregate for table B has almost 1k fields inside the SELECT clause programatically generated. This and the error message giving no information where the real source of the error was made me miss that one of the SELECT fields was HISTOGRAM(some_col, 1, 1, 10) AS some_col_hg,

Sorry for the fuss!