5 ms·
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
by rhaas 9y ago
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.
- fiatjaf 9y agoThank you.
- pgaddict 9y agoI 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.
- foota 9y agoHow 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.
- pgaddict 9y agoI 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.html#sql-createview-updatable-views 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.