Background

With #8251 we have enabled the possibility to read invalidations from the WAL rather that using a trigger on the hypertable to collect the changes.

This is done by processing the WAL on a regular basis (typically during refresh) and reading information about all hypertables that have a continuous aggregate connected to it.

If you create a new continuous aggregate, you can decide the collection method using the option timescaledb.invalidate_using, and this will error out if there are continuous aggregates for that table that are using a different collection method than what you're requesting.

Not that it is possible to have some some hypertables using trigger-based collection and some hypertables using WAL-based collection, but you cannot mix usage between continuous aggregates.

Problem

If there is an existing continuous aggregate that is using a trigger-based invalidation collection, and the user would like to move this over to use WAL-based collection, there is no method to migrate the continuous aggregates over.

Doing an ALTER MATERIALIZED VIEW on the continuous aggregate will not have any effect at all since you cannot remove the trigger from the hypertable unless you change all continuous aggregates at the same time.

Proposal

Migrating continuous aggregates from using trigger-based to using WAL-based invalidation collection has to be done on a per-hypertable basis, but will be more efficient if done for all hypertables in one go.

Introduce the procedure set_invalidation_method(text, regclass[]) accepting the name of a invalidation collection method (either wal, trigger, or default) and an array of hypertables that should switch collection method.

For each hypertable, and the set of continuous aggregates associated with the hypertable:

1. Lock the hypertable for changes in such a manner that a refresh will not, once locks are released, try to read the hypertable invalidation table.
2. Move invalidations from the hypertable invalidation log table to the materialization invalidation log table.
3. Delete the invalidation collection trigger from the hypertable.
4. Create the invalidation collection slot for the database.
5. Release the lock from step 1.
6. Any waiting sessions will now start using the WAL-based hypertable invalidation collection.
   Since the existence of the trigger indicates whether WAL-based or trigger-based collection invalidation should be used, it is possible to delete the trigger and create the proper slot using the following function. However, that will not move hypertable invalidations from the old tables.