I require eagerly refreshed materialized views for my use case, which is something Postgres does not currently support. I need my updates to a table the view refers to visible within the same transaction, and often it is a single change to one row which will only effect a single row in the view. If I used materialized views as they're currently implemented, that would run the entire query and replace what needs to change, but it has to run that entire query to do so. For my use case, that is totally out of the question to do for every insert / update / delete that could effect the view.
For example, if I had a account balance materialized view that pretty much summed transactions for a specific user from two different tables and displayed a "balance" for every user, I would want that to only run the query for the refresh for the specific user(s) that just had data inserted/updated/deleted. Not every user in the system after every statement.
I've pretty much implemented this manually for some specific views which performed horribly in Postgres (but would work fine in SQL Server for example). I am looking to do this in a generic way so it's easier to implement when necessary, and can be packaged as an extension for others who may need to use it.
Obviously if we had better support for statement level triggers (so we could reference all the NEW / OLD values touched by a statement) this would be lower overhead, but that is one of the things holding up incrementally refreshed materialized views from being implemented in the first place. I just thought up a way to do it which gets around not having better statement level triggers and wanted to see where I could get with things as they are.
