I think even something that just does that in pure SQL/plpgsql would be a big step forward, even if we wouldn't want it directly in the codebase.
Something like creating a trigger under the hood each time a MV is created, that checks the changed rows on every statement against the query that generated the MV? That also seems feasible, but wouldn't it be rather too small for my three months of GSoC?
The query rewrite feature would be extremely desirable for us. >Do you think that implementing the staleness check as suggested >by Thomas could get us started in the query rewrite business?
There are many aspects related to the definition, maintenance, and use of MVs that need work; it seems to me that many of them can be pursued in parallel as long as people are communicating. Staleness tracking is definitely one aspect that is needed. If you want to put forward a proposal for that, which seems to be of a scope that is possible in the context of GSoC, that would be great. If there is any other aspect of the MV "big picture" that you can think of that you would like to tackle and seems of appropriate scope, please don't feel constrained to "staleness" as the only possible project; it was just one suggestion of something that might be of about the right size.
FWIW, what I would find most useful at this point is a way to get the equivalent of an AFTER STATEMENT trigger that provided all changed rows in a MV as the result of a statement. That would at least allow people do do their own MV refresh work without needing to study the methods for identifying how the results of a statement impact what should be in the MV. I think even something that just does that in pure SQL/plpgsql would be a big step forward, even if we wouldn't want it directly in the codebase. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com
