News for hackers. Stuff that matters.
Re:New Features Coming in PostgreSQL 10 by petepete on Saturday April 08, 2017 @12:54PM (#14067807)
How do you mean? Couldn't you use a trigger to update the view?
Re:New Features Coming in PostgreSQL 10 by ams6110 on Saturday April 08, 2017 @03:38PM (#14068591)
IMHO triggers are almost always best avoided. There are some exceptions but most of the time you want changes to be explicit not happening "by magic" behind the scenes as a side-effect of something else.
Re:New Features Coming in PostgreSQL 10 by mozumder on Saturday April 08, 2017 @01:30PM (#14067968)
Triggers on materialized views are really error-prone and tedious. It's cache invalidation, which is hard.
Re:New Features Coming in PostgreSQL 10 by phamilton on Saturday April 08, 2017 @01:18PM (#14067909)
Effectively yes, but if it's that simple why not make this a built in functionality? Other DBs have it.
Re:New Features Coming in PostgreSQL 10 by okket on Saturday April 08, 2017 @05:39PM (#14069190)
A trigger on what? Every update, insert, delete, etc.? On every table in the view?

Even if that is possible, it may be a major performance killer. This has to be done internally, I think.

Re:New Features Coming in PostgreSQL 10 by jeltz on Saturday April 08, 2017 @09:31PM (#14069995)
I am not sure it necessarily would be that bad. After all foreign keys are implemented with triggers and they are usually fast enough. You just need to write trigger functions which are fast enough.
Re:New Features Coming in PostgreSQL 10 by gnaritas on Saturday April 08, 2017 @10:15PM (#14070111)
It is possible, that's how I do it, and it doesn't really affect performance at all. You just don't do it real time, the triggers just kick an id into a queue and you have a background task work the queue updating the materialized table from the view it's a copy of just for the id's in the queue.
Re:New Features Coming in PostgreSQL 10 by petepete on Sunday April 09, 2017 @11:33AM (#14072752)
So what would make the MV self-update?