[Feature request]: support bucket_width references in time_bucket_gapfill

[Nosfistis]

What type of bug is this?

Unexpected error

What subsystems and features are affected?

Gapfill

What happened?

time_bucket_gapfill seems to be a drop-in for time_bucket, but it has some limitations that are neither straightforward nor mentioned in the documentation.

It requires a given start/finish either as arguments (deprecated) or in where. It does not allow querying the entire table, unless I predefine the known timestamp boundaries of the table in a WHERE clause. This is also addressed in #2595 (possibly).

More importantly and less intuitively, it does not allow for references in the bucket_width argument. I have a table with known important intervals with which I would like to group the data in seconds, and I use time_bucket_gapfill(make_interval(secs => polling_interval), ts) in query time. Shouldn't that work? If not, I believe that it should be mentioned in the docs, just as using the time_bucket_gapfill as a nested expression is not possible.

TimescaleDB version affected

2.20.2

PostgreSQL version used

16.9

What operating system did you use?

Windows 11 x64, Ubuntu 24

What installation method did you use?

Docker

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

[22023] ERROR: invalid time_bucket_gapfill argument: bucket_width must be a simple expression

How can we reproduce the bug?

CREATE TABLE measurements
(
    moment       TIMESTAMPTZ      NOT NULL,
    resource_id  INTEGER          NOT NULL,
    value DOUBLE PRECISION NOT NULL
);

SELECT create_hypertable('measurements', 'moment');

CREATE TABLE resources
(
    id               INTEGER NOT NULL,
    polling_interval INTEGER NOT NULL,
    CONSTRAINT resources_pkey PRIMARY KEY (id)
);

SELECT time_bucket_gapfill(MAKE_INTERVAL(secs => polling_interval), moment) AS bucket,
        locf(AVG(value))                                              AS values,
        measurements.resource_id                                             AS parameter_id
 FROM measurements
          INNER JOIN resources ON measurements.resource_id = resources.id
 WHERE moment BETWEEN '2025-01-01' AND '2025-02-01'
 GROUP BY resource_id, bucket
 ORDER BY bucket;

[svenklemm]

This is a current limitation of gapfill. The bucket width needs to be a constant that can be evaluated without values from the rest of the query and must not change over the course of the query.

[Nosfistis]

Is this going to be supported in the near future? Perhaps there should be a limitation notice in the reference page.

[natalya-aksman]

After release of #9821 subqueries will be allowed as start/finish/period arguments to time_bucket_gapfill if they are done as InitPlan.

It will also be possible to provide individual periods from another table for different groups in time_bucket_gapfill if that table is joined via LATERAL, i.e. if periods are parameters in a nested loop. Something like this will work then:

SELECT sq.bucket, sq.values, sq.parameter_id
FROM resources
INNER JOIN LATERAL (
 SELECT time_bucket_gapfill(MAKE_INTERVAL(secs => polling_interval), moment) AS bucket,
        locf(AVG(value))                                              AS values,
        measurements.resource_id                                             AS parameter_id
 FROM measurements
 WHERE measurements.resource_id = resources.id
 AND moment BETWEEN '2025-01-01' AND '2025-02-01'
 GROUP BY resource_id, bucket) sq ON (true)
 ORDER BY bucket;