[Bug]: job error "timestamp out of range" when using `time_bucket('1 month', ...)` in continuous aggregate

[tamasfe]

What type of bug is this?

Unexpected error

What subsystems and features are affected?

Background worker, Continuous aggregate

What happened?

Creating a continuous aggregate with a time bucket of 1 month or more will always cause scheduled jobs to fail with "timestamp out of range".

However calling refresh_continuous_aggregate(...) manually works as expected.

TimescaleDB version affected

2.14.2

PostgreSQL version used

16.2

What operating system did you use?

Debian x64

What installation method did you use?

Docker

What platform did you run on?

Google Cloud Platform (GCP)

Relevant log output and stack trace

No response

How can we reproduce the bug?

CREATE TABLE "testing1" (
    ts TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    val DECIMAL NOT NULL DEFAULT 0
);

SELECT create_hypertable(
    '"testing1"',
    by_range('ts', INTERVAL '7 day'),
    migrate_data => TRUE,
    create_default_indexes => TRUE
);

CREATE MATERIALIZED VIEW IF NOT EXISTS "testing1_monthly"
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 month', t."ts") AS "bucket",
    SUM(val) AS "foo"
FROM
    testing1 t
GROUP BY ("bucket")
WITH NO DATA;

-- Succeeds
CALL refresh_continuous_aggregate('"testing1_monthly"', NULL, NOW() - '1 hour'::INTERVAL);

SELECT add_continuous_aggregate_policy(
    '"testing1_monthly"',
    start_offset => NULL,
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour'
);

-- Always fails with "timestamp out of range"
CALL run_job(<job ID>);

[akuzm]

I think this was fixed here: https://github.com/timescale/timescaledb/pull/6729

Your example runs fine for me on the main branch. The fix is in 2.15 which we're going to release in a couple of weeks.

[tamasfe]

Sounds great, will try, thank you!