[Bug]: Wrong check constraint on continuous_agg_migrate_plan_step.type for instances upgrading from 2.7.2 to 2.8.0

[iroussos]

What type of bug is this?

Incorrect result

What subsystems and features are affected?

Continuous aggregate

What happened?

Instances upgrading from 2.7.2 to 2.8.0 are missing the OVERRIDE CAGG and DROP OLD CAGG values for the CHECK CONSTRAINT on continuous_agg_migrate_plan_step.type:

- CONSTRAINT continuous_agg_migrate_plan_step_check2 CHECK (type IN ('CREATE NEW CAGG', 'DISABLE POLICIES', 'COPY POLICIES', 'ENABLE POLICIES', 'SAVE WATERMARK', 'REFRESH NEW CAGG', 'COPY DATA'))
+ CONSTRAINT continuous_agg_migrate_plan_step_check2 CHECK (type IN ('CREATE NEW CAGG', 'DISABLE POLICIES', 'COPY POLICIES', 'ENABLE POLICIES', 'SAVE WATERMARK', 'REFRESH NEW CAGG', 'COPY DATA', 'OVERRIDE CAGG', 'DROP OLD CAGG'))

This will happen in updated instances. New instances directly installed in 2.8 will not have this problem.

Quick reference:

• Definition in tables.sql
• Definition in 2.7.2--2.8.0.sql

Related slack discussion (internal only)

Reported by @carobme

Additionally, we should check why this was not caught in our upgrade/downgrade tests (no error raised) in our CI and fix the tests.

TimescaleDB version affected

2.8.0 (upgrade from 2.7.2 only)

PostgreSQL version used

Not applicable

What operating system did you use?

Not applicable

What installation method did you use?

Not applicable

What platform did you run on?

Managed Service for TimescaleDB (MST/Aiven)

Relevant log output and stack trace

defaultdb=> CALL cagg_migrate('sensor_summary_hourly');
ERROR:  23514: new row for relation "continuous_agg_migrate_plan_step" violates check constraint "continuous_agg_migrate_plan_step_check2"
DETAIL:  Failing row contains (2, 20, NOT STARTED, null, null, OVERRIDE CAGG, {"drop_old": false, "override": false, "cagg_name_new": "sensor_...).
CONTEXT:  SQL statement "INSERT INTO
        _timescaledb_catalog.continuous_agg_migrate_plan_step (mat_hypertable_id, type, config)
    VALUES
        (_cagg_data.mat_hypertable_id, 'OVERRIDE CAGG', jsonb_build_object('cagg_name_new', _cagg_name_new, 'override', _override, 'drop_old', _drop_old)),
        (_cagg_data.mat_hypertable_id, 'DROP OLD CAGG', jsonb_build_object('cagg_name_new', _cagg_name_new, 'override', _override, 'drop_old', _drop_old)),
        (_cagg_data.mat_hypertable_id, 'COPY POLICIES', _policies || jsonb_build_object('cagg_name_new', _cagg_name_new)),
        (_cagg_data.mat_hypertable_id, 'ENABLE POLICIES', NULL)"
PL/pgSQL function _timescaledb_internal.cagg_migrate_create_plan(_timescaledb_catalog.continuous_agg,text,boolean,boolean) line 101 at SQL statement
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 21 at CALL

How can we reproduce the bug?

defaultdb=> CALL cagg_migrate('any existing cagg after upgrade to 2.8.0');

[iroussos]

@fabriziomello I created a bug report for the issue discussed on Slack.

/cc @horzsolt