[Bug]: Refresh and Compression policies not properly migrated into new CAgg

[fabriziomello]

What type of bug is this?

Incorrect result

What subsystems and features are affected?

Continuous aggregate

What happened?

After migrate a Continuous Aggregate from the old format to the new using cagg_migrate procedure we end up with the following problems:

1. Refresh policy is not copied from the OLD cagg to the NEW
2. Compression setting is not copied from the OLD cag to the NEW

TimescaleDB version affected

2.8.0

PostgreSQL version used

14.5

What operating system did you use?

Ubuntu 22.04 x64

What installation method did you use?

Source

What platform did you run on?

Not applicable

Relevant log output and stack trace

1) Jobs after the migration:

 job_id |              application_name              | schedule_interval | max_runtime | max_retries | retry_period |      proc_schema      |              proc_name              |  owner   | scheduled>
--------+--------------------------------------------+-------------------+-------------+-------------+--------------+-----------------------+-------------------------------------+----------+---------->
   1000 | Refresh Continuous Aggregate Policy [1000] | 01:00:00          | 00:00:00    |          -1 | 01:00:00     | _timescaledb_internal | policy_refresh_continuous_aggregate | fabrizio | t        >
   1001 | Compression Policy [1001]                  | 35 days           | 00:00:00    |          -1 | 01:00:00     | _timescaledb_internal | policy_compression                  | fabrizio | f        >
   1002 | Compression Policy [1001]                  | 35 days           | 00:00:00    |          -1 | 01:00:00     | _timescaledb_internal | policy_compression                  | fabrizio | t        >
      1 | Telemetry Reporter [1]                     | 24:00:00          | 00:01:40    |          -1 | 01:00:00     | _timescaledb_internal | policy_telemetry                    | fabrizio | t        >
(4 rows)

2) Error compressing the migrated CAGG

ERROR:  compression not enabled on "conditions_summary_hourly_new"
DETAIL:  It is not possible to compress chunks on a hypertable or continuous aggregate that does not have compression enabled.
HINT:  Enable compression using ALTER TABLE/MATERIALIZED VIEW with the timescaledb.compress option.

How can we reproduce the bug?

CREATE TABLE conditions (
  time TIMESTAMPTZ,
  temperature NUMERIC
);

SELECT create_hypertable('conditions', 'time');

INSERT INTO conditions
SELECT
  generate_series('2021-01-01 00:00'::timestamp, '2022-12-31 23:59:59'::timestamp, '1 minute'),
  random()*100;

CREATE MATERIALIZED VIEW conditions_summary_hourly
WITH (timescaledb.continuous, timescaledb.finalized=false) AS
SELECT
  time_bucket(INTERVAL '1 hour', time) AS bucket,
  SUM(temperature),
  COUNT(*),
  AVG(temperature)
FROM conditions
GROUP BY 1
WITH NO DATA;

SELECT add_continuous_aggregate_policy('conditions_summary_hourly',
  start_offset => INTERVAL '30 days',
  end_offset => INTERVAL '1 day',
  schedule_interval => INTERVAL '1 hour');

ALTER MATERIALIZED VIEW conditions_summary_hourly SET (timescaledb.compress=true);

SELECT add_compression_policy('conditions_summary_hourly', compress_after=>'45 days'::interval);

CALL refresh_continuous_aggregate('conditions_summary_hourly', NULL, NULL);

SELECT compress_chunk(c, true) FROM show_chunks('conditions_summary_hourly') c;

CALL cagg_migrate('conditions_summary_hourly');

SELECT * FROM timescaledb_information.jobs;

 job_id |              application_name              | schedule_interval | max_runtime | max_retries | retry_period |      proc_schema      |              proc_name              |  owner   | scheduled>
--------+--------------------------------------------+-------------------+-------------+-------------+--------------+-----------------------+-------------------------------------+----------+---------->
   1000 | Refresh Continuous Aggregate Policy [1000] | 01:00:00          | 00:00:00    |          -1 | 01:00:00     | _timescaledb_internal | policy_refresh_continuous_aggregate | fabrizio | t        >
   1001 | Compression Policy [1001]                  | 35 days           | 00:00:00    |          -1 | 01:00:00     | _timescaledb_internal | policy_compression                  | fabrizio | f        >
   1002 | Compression Policy [1001]                  | 35 days           | 00:00:00    |          -1 | 01:00:00     | _timescaledb_internal | policy_compression                  | fabrizio | t        >
      1 | Telemetry Reporter [1]                     | 24:00:00          | 00:01:40    |          -1 | 01:00:00     | _timescaledb_internal | policy_telemetry                    | fabrizio | t        >
(4 rows)


SELECT compress_chunk(c, true) FROM show_chunks('conditions_summary_hourly_new') c;
ERROR:  compression not enabled on "conditions_summary_hourly_new"
DETAIL:  It is not possible to compress chunks on a hypertable or continuous aggregate that does not have compression enabled.
HINT:  Enable compression using ALTER TABLE/MATERIALIZED VIEW with the timescaledb.compress option.