-1

Main question: I have several views depending on a PostgreSQL/PostGIS table and a final materialized view created by querying the other views. I need a fast and updatable final result (i.e. MV) to use in a QGIS project.

My aim is to update the starting table by overwriting it with new (lots of) values and hopefully have update views and materialized view. I use QGIS DB Manager to overwrite existing table but I get an error because of mv depending on it. If I delete mv, overwrite table and then recreate mv everything is ok but I'd like to avoid manual operations as much as possible.

Is there a better way to reach my goal?

Another question: If I set a trigger to refresh a mv when I update/insert/delete values in a table, would it work even in case of overwriting entire table with a new one?

ocelot73
  • 41
  • 5
  • Do you want to update the materialized view or do you want to update the underlying tables and have the materialized view change based on those modifications? Your intention is not clear. – Laurenz Albe Sep 16 '20 at 14:53
  • I want to update (overwrite through qgis db manager) the underlying tables without errors and then refresh MV – ocelot73 Sep 16 '20 at 15:02

1 Answers1

0

Refreshing a materialized view runs the complete defining query, so that is a long running and heavy operation for a complicated query.

It is possible to launch REFRESH MATERIALIZED VIEW from a trigger (it had better be a FOR EACH STATEMENT trigger then), but that would make every data modification so slow that I don't think that is practically feasible.

One thing that might work is to implement something like a materialized view that refreshes immediately “by hand”:

  • create a regular table for the “materialized view” and fill it with data by running the query

  • on each of the underlying tables, define a row level trigger that modifies the materialized view in accordance with the changes that triggered it

This should work for views where the definition is simple enough, for complicated queries it will not be possible.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks but refreshing MV wasn't my main goal. – ocelot73 Sep 16 '20 at 18:58
  • I'd like to understand how to overwrite an underlying table without first deleting linked MV and without getting errors – ocelot73 Sep 16 '20 at 19:01
  • When you say "overwrite", do you mean `UPDATE` or `ALTER/DROP TABLE`? When you say "linked" MV, do you mean that the MV uses the taböe in its definition? – Laurenz Albe Sep 17 '20 at 02:22
  • I've used "overwrite" term because it's the one used in Qgis DB Manager, but I thing it should be an UPDATE or ALTER (I think without dropping table). And yes, with "linked" MV I mean the MV uses the table in its definition – ocelot73 Sep 23 '20 at 11:56
  • `UPDATE` won't be a problem, so you must be using `ALTER TABLE` or `DROP TABLE`. That would be impossible as long as the materialized view is in place. Don't do that then. – Laurenz Albe Sep 23 '20 at 12:37