Re: Incrementally refreshed materialized view

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.