search

sraoss / pgsql-ivm

IVM (Incremental View Maintenance) development for PostgreSQL
Other
127 stars 12 forks source link

A feature to switch between normal materialized view and IMMV #79

Open yugo-n opened 4 years ago

yugo-n commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 commented 3 years ago

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...