On Fri, 14 Sep 2018 at 16:26, Jeremy Finzel <finz...@gmail.com> wrote:
> >> Could you apply something similar using triggers? >> One question would be how PG would identify changes to existing rows - >> using the replication facilities to essentially replicate into the view? >> This would be quite tricky I reckon. Otherwise a change to the underlying >> table may not propagate correctly to the MV. >> > > That's not what I had in mind. I only mean when REFRESH MATERIALIZED VIEW > is run, it gathers the results of the view in memory, then instead of > essentially "wiping and reloading" the table, it would only write the > differences. So if 90% of the rows would be the same as before the > refresh, we only update 10% of the rows. > On a related note, I've mused about allowing a WHERE clause on REFRESH MATERIALIZED VIEW. To start with, I imagine limiting it to refer to the columns of a primary key (which implies that primary key constraints would have to be allowed). As long as this is done, I think it's pretty clear what the semantics would have to be, at least as to the new view contents: the equivalent of DELETE with the WHERE clause, followed by INSERT of the view expression with the same WHERE clause applied.
