A feature to switch between normal materialized view and IMMV

[yugo-n]

This feature can keep frozen data available in the materialized view during heavy operations on source tables.

Syntax ideas:

ALTER MATERIALIZED VIEW [ IF EXISTS ] name
    SET { NOINCREMENTAL }
or
    SET { NOINCREMENTAL | INCREMENTAL | INCREMENTAL CONCURRENTLY  }

 ALTER MATERIALIZED VIEW ... SET {WITH | WITHOUT} INCREMENTAL REFRESH

or others. Maybe it is better to use REFRESH. We will need more discussions.

Suggested by PAscal
https://www.postgresql.org/message-id/1581458652080-0.post%40n3.nabble.com

[yugo-n]

Implementation idea notes:

When switching IMMV -> MV, we just have to drop or disable IVM triggers on base tables. Also pg_class.relisivm flag have to be cleared.

When MV->IMMV, we will have to refresh the view data to the up-to-date state. Therefore, maybe we should use REFRESH command instead of ALTER. Or, we can use ALTER to switch its mode and set the view non-scannable, and then use REFRESH manually to populate the view.

[legrandlegrand]

When Switching IMMV -> MV, there should be a way for administrator to check if relation is IMMV or MV. Not sure that \d+ permits this (it does not, after REFRESH WITH NODATA)

When MV->IMMV, hidden columns __x__ could also be missing, and would have to be recreated.

[yugo-n]

There is the same request in pgsql-hackers.

https://www.postgresql.org/message-id/OS0PR01MB5682A90859F1B7772460A74082F29%40OS0PR01MB5682.jpnprd01.prod.outlook.com

I'll consider it again...