Support for materialized views

[kgodey]

In the database, views can either be regular views or materialized views. Materialized views store data in a similar way to tables whereas temporary views only query existing data without storing it in any new way.

We do not plan to support creating materialized views in the alpha version of Mathesar, but the user may connect an existing database with materialized views. We should display these views correctly.

Scenario

• The user navigates to a materialized view.
  • There should be an indication while looking at the view that it is a materialized view and that the data may not be up to date. We should explain what a materialized view is somewhere.
  • There should be a button or some other way to refresh data for the materialized view.
• The user navigates to a regular view.
  • There should be an indication while looking at the view that it is not a materialized view.

Design Notes

In the future, we probably want to build in more functionality around materialized views such as allowing the user to convert a regular view to a materialized view, showing the last refreshed timestamp, etc. This might be useful to keep in mind while designing.

Additional Context

• Blocked by design work on Support for opening and interacting with a View #466

[kgodey]

@dmos62 @ghislaineguerin @pavish @mathemancer @seancolsen @silentninja This issue has been updated and is ready for review. Please look through it and unassign yourself after you've added any feedback that you might have.

[silentninja]

So we don't support creating or modifying a materialized view but allow them to be read and used just like a normal view. Is that right?

[kgodey]

So we don't support creating or modifying a materialized view but allow them to be read and used just like a normal view. Is that right?

Yes, that's correct.

[mathemancer]

Note that materialized views can't be INSERTed into or UPDATEd under any circumstances (that I'm aware of; see https://www.postgresql.org/docs/13/rules-materializedviews.html). This means that some of our view logic won't work. Given that, I think we should precisely specify what we mean by "used just like a normal view". Unless we're ready to change their definitions and update them using the REFRESH MATERIALIZED VIEW command, I suggest we make them completely read-only for the moment, or at most allow changing the metadata, e.g., name, schema, owner, etc.

[pavish]

@ghislaineguerin I have a concern to keep in mind when we start work on the design for this.

I think materialized views should not be placed on the same level as normal views on the left pane, it might confuse the user since we do not support a lot of operations on materialized views for now. A clear distinction needs to be established before the user opens it, and not after.

[ghislaineguerin]

Looks good. I agree with @pavish in the need to differentiate the view type.

[kgodey]

@mathemancer Given that we won't be inserting or updating into views (see my comment here: #456 (comment)), will we actually have less functionality for materialized views? It seems like the only difference will be that they might not have the latest data, whereas regular views will always have the latest data.

[mathemancer]

@mathemancer Given that we won't be inserting or updating into views (see my comment here: #456 (comment)), will we actually have less functionality for materialized views? It seems like the only difference will be that they might not have the latest data, whereas regular views will always have the latest data.

I think that's correct. I didn't realize we were moving that direction for normal views (though I think that's a good move). We might consider adding a way to refresh materialized views at some point, and the flow would be:

• click on cell
• record editor pops up; make desired changes to underlying table data
• click "refresh materialized view" button
• see changes in view.

One advantage of this over standard views would be that the user could defer updating the view until they'd done a number of edits. In some cases, this could be a real time-saver. I'm not sure the ability to refresh the view should be prioritized, but on the other hand, the implementation is literally one function that calls already-existing PostgreSQL functionality. I'm not sure how we'd phrase it in the normal CRUD API functions, though.

[kgodey]

@mathemancer Thanks, makes sense. I've updated the description to include a refresh data action for materialized views.

@pavish @ghislaineguerin Given that we will not have reduced functionality for materialized views, I don't think we need to necessarily display them in a separate area. I'll put a note about them in #455 just in case.

[silentninja]

It would be really nice if the last refreshed timestamp is available for Materialized views. Since it isn't straightforward to get the information I wouldn't want it as a core feature but maybe as an extension or a plugin.

[kgodey]

@silentninja I agree but I don't think we should prioritize building that for the first release since our goal for the first release is just to display materialized views correctly rather than building in first-class support for working with them. I did add it to the "Design notes" section as something we might want to do in the future.