[Bug]: cagg_migrate failed with "timestamp out of range"

[hongquan]

What type of bug is this?

Other

What subsystems and features are affected?

Continuous aggregate

What happened?

I'm migrating CAgg to new format and get "timestamp out of range" error.
Here is the copy of reproducible DB:
nanola-trungthao.zip

TimescaleDB version affected

2.10.0

PostgreSQL version used

14.6

What operating system did you use?

Ubuntu 22.04

What installation method did you use?

Deb/Apt

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

CALL cagg_migrate('farm_tscondition_10m', override => TRUE, drop_old => TRUE);
timestamp out of range: "4714-11-24 00:00:00 BC"
LINE 5:             WHERE time_start < CAST('4714-11-24 00:00:00 BC'...
                                            ^
QUERY:  
        WITH boundaries AS (
            SELECT min(time_start), max(time_start), 'time_start' AS bucket_column_name, 'timestamp with time zone' AS bucket_column_type, 'f>
            FROM public.farm_tscondition_10m
            WHERE time_start < CAST('4714-11-24 00:00:00 BC' AS timestamp with time zone)
        )
        INSERT INTO
            _timescaledb_catalog.continuous_agg_migrate_plan_step (mat_hypertable_id, type, config)
        SELECT
            '6',
            'COPY DATA',
            jsonb_build_object (
                'start_ts', start::text,
                'end_ts', (start + CAST('10:00:00' AS interval))::text,
                'bucket_column_name', bucket_column_name,
                'bucket_column_type', bucket_column_type,
                'cagg_name_new', cagg_name_new
            )
        FROM boundaries,
             LATERAL generate_series(min, max, CAST('10:00:00' AS interval)) AS start;
        
CONTEXT:  PL/pgSQL function _timescaledb_internal.cagg_migrate_create_plan(_timescaledb_catalog.continuous_agg,text,boolean,boolean) line 116>
SQL statement "CALL _timescaledb_internal.cagg_migrate_create_plan(_cagg_data, _cagg_name_new, override, drop_old)"
PL/pgSQL function cagg_migrate(regclass,boolean,boolean) line 25 at CALL

How can we reproduce the bug?

- Restore the attached DB dump.
- Connect and run this query:

  
  CALL cagg_migrate('farm_tscondition_10m', override => TRUE, drop_old => TRUE);

[kgyrtkirk]

I was not able to reproduce it at first - but it turned out that I was just in luck; because the timezone was set to C.
When I've set Europe/Budapest as timezone it failed with the same error you are experiencing; could you try:

set timezone=C;
CALL cagg_migrate('farm_tscondition_10m', override => TRUE, drop_old => TRUE);

repro: https://github.com/kgyrtkirk/reprox/tree/tsdb-8359-timestamp-out-of-range

I'll add this to the backlog for further investigation.
Thank you for reporting it!

[hongquan]

Don't know which version of Postgres you are using, but my version (v14) doesn't accept "C" value for timezone:

set timezone=C;
invalid value for parameter "TimeZone": "c"

UTC works, though:

SET TIME ZONE 'UTC';                                                                                                        
SET                                                                                                                                           
Time: 0.002s 

CALL cagg_migrate('farm_tscondition_10m', override => TRUE, drop_old => TRUE);                                              
WARNING:  refresh the continuous aggregate after the migration executing this statement: "CALL public.refresh_continuous_aggregate('public.farm_tscondition_10m', CAST('4714-11-24 00:00:00 BC' AS timestamp with time zone), NULL);"