On 11.04.10 20:47 , Robert Haas wrote:
On Sun, Apr 11, 2010 at 10:26 AM, Heikki Linnakangas <heikki.linnakan...@enterprisedb.com> wrote:Robert Haas wrote:2010/4/10 Andrew Dunstan<and...@dunslane.net>:Heikki Linnakangas wrote:1. Keep the materialized view up-to-date when the base tables change. This can be further divided into many steps, you can begin by supporting automatic updates only on very simple views with e.g a single table and a where clause. Then extend that to support joins, aggregates, subqueries etc. Keeping it really limited, you could even require the user to write the required triggers himself.That last bit doesn't strike me as much of an advance. Isn't the whole point of this to automate it? Creating greedy materialized views is usually not terribly difficult now, but you do have to write the triggers.Yeah, I agree.It doesn't accomplish anything interesting on its own. But if you do the planner changes to automatically use the materialized view to satisfy queries (item 2. in my previous email), it's useful.But you can't do that with a snapshot view, only a continuous updated one.
If continuous updates prove to be too hard initially, you could instead update the view on select if it's outdated. Such a materialized view would be a kind of inter-session cache for subselects. The hard part would probably be to figure out how to decide whether the view is outdated or not, and to deal with two concurrent transactions trying to use an outdates view (and both trying to refresh it). What makes the second problem hard is that you wouldn't want one of the transactions to wait for the other to complete, because this is not how SELECTs traditionally behave. best regards, Florian Pflug
smime.p7s
Description: S/MIME Cryptographic Signature
