News for hackers. Stuff that matters.
Re:New Features Coming in PostgreSQL 10 by fiatjaf on Saturday April 08, 2017 @12:27PM (#14067680)
Ok, I'm not a database manager for enormous projects, so these changes may be great, but I don't understand them and don't care about them. Postgres is already the most awesome thing in Earth to me.

Still, if my opinion counts I think SELF-UPDATING MATERIALIZED VIEWS should be the next priority.

Re:New Features Coming in PostgreSQL 10 by rhaas on Saturday April 08, 2017 @01:18PM (#14067912)
The work that has been done on transition tables is intended to enable future work on automatically updated materialized views; the idea is that the system will automatically derive a query to update the view based on the deltas between the set of old rows and the set of new rows. That will take more work, though. I do agree it would be valuable. It's possible to set up similar things by writing your own triggers, and having transition tables available in PL/pgsql will make it easier, but it's not necessarily easy to figure it all out by hand for a complex view involving joins and aggregates.
Re:New Features Coming in PostgreSQL 10 by pgaddict on Saturday April 08, 2017 @05:01PM (#14069016)
I wonder why not to go for a changelog-based implementation. Instead of modifying the materialized view directly, write the changes into a changelog, and then update the matview in the background. More efficient, less locking issues, etc.
Re:New Features Coming in PostgreSQL 10 by foota on Saturday April 08, 2017 @07:24PM (#14069623)
How do you know when the change will affect the matview? I don't know the syntax for creating it off the top of my head, but imagine a materialized view limited to the top ten records of a table.
Re:New Features Coming in PostgreSQL 10 by pgaddict on Sunday April 09, 2017 @03:17PM (#14073868)
I assume there will be some limitations what matviews can be updated automatically/ incrementally. E.g. updatable views (regular, not materialized) have a range of such restrictions, see: https://www.postgresql.org/docs/devel/static/sql-createview....

Hopefully, some of those restrictions will disappear, because matviews are often used to store pre-aggregted data, so the restriction on GROUP BY would be unfortunate.

Re:New Features Coming in PostgreSQL 10 by fiatjaf on Saturday April 08, 2017 @02:43PM (#14068309)
Thank you.
Re:New Features Coming in PostgreSQL 10 by rachbelaid on Saturday April 08, 2017 @04:50PM (#14068955)
Some work started in this direction. I didn't follow closely the whole thread but I don't think that got commited in PG10.

https://www.postgresql.org/message-id/flat/20170119213859.GA...

More info in the EDB roadmap: https://wiki.postgresql.org/wiki/EnterpriseDB_database_serve...

Postgres has been amazing in shipping the foundation required to deliver complex feature.. Logical Replication is an example of it, all the piece commited in the last 6y allowed to make this patch achievable.

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?