GSoC - proposal - Materialized Views in PostgreSQL
in [PgSql]
|
From: Robert Haas on 11 Apr 2010 23:00 On Sun, Apr 11, 2010 at 10:13 PM, Florian G. Pflug <fgp(a)phlo.org> wrote: > 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. Well, the proposed project is to create views that only get refreshed manually. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 11 Apr 2010 23:09 On Sun, Apr 11, 2010 at 5:24 AM, Greg Smith <greg(a)2ndquadrant.com> wrote: > From the rest of your comments, I'm comfortable that you're in sync with the > not necessarily obvious risky spots here I wanted to raise awareness of. > It's unreasonable to expect we'll have exactly the same priorities here, > and I doubt it's useful to debate how I perceive the merit of various > development subsets here compared to yourself. I don't think it's really > important whether anyone agrees with me or not about exactly the value of a > full table lock implementation. The main thing I'm concerned about is just > that it's noted as a known risky part, one that could end up blocking the > project's ability to commit even a subset of the proposed patch here. I think that one of the things that we need to get our hands around is how we're going to distinguish the "snapshot" flavor of materialized view from the "continuous update" flavor. By definition, the latter will only ever be supportable for a fairly restricted subset of all possible queries, and I am assuming that we will not want to decide what the behavior is going to be based on the query but rather based on what the user specifies. Anything else seems like it would be have the potential for severe POLA violations. So we need to think now about how we'll distinguish between the two flavors. I imagine some sort of syntactic marker would be appropriate; not sure what. Reading this thread, I'm starting to grow concerned that some people may feel that manually refreshed materialized views are not even worth bothering with, because (the argument goes) you could just use some table and write a function that updates it. There's probably some truth to that, but I guess my thought is that it would have some value as a convenience feature; and eventually we might optimize it to the point where it would make more sense to use the built-in feature rather than rolling your own. However, if we're going to have complaints that manually refreshed materialized views suck and we should only ever support materialized views to the extent that we can make them automatically update on-the-fly, then let's have those complaints now before someone spends several months of their life on the project only to be told that we don't want it. Let's be clear: I think it's useful, but, if other people disagree, we need to iron that out now. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Heikki Linnakangas on 12 Apr 2010 01:13 Robert Haas wrote: > On Sun, Apr 11, 2010 at 10:26 AM, Heikki Linnakangas > <heikki.linnakangas(a)enterprisedb.com> wrote: >> Robert Haas wrote: >>> 2010/4/10 Andrew Dunstan <andrew(a)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. A materialized view with manually-defined triggers to keep it up-to-date is a continuously updated one. Other DBMSs allow that with snapshot views too, you just don't get totally up-to-date results, but I not sure we want to go there. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Pavel Stehule on 12 Apr 2010 02:16 2010/4/12 Robert Haas <robertmhaas(a)gmail.com>: > On Sun, Apr 11, 2010 at 5:24 AM, Greg Smith <greg(a)2ndquadrant.com> wrote: >> From the rest of your comments, I'm comfortable that you're in sync with the >> not necessarily obvious risky spots here I wanted to raise awareness of. >>  It's unreasonable to expect we'll have exactly the same priorities  here, >> and I doubt it's useful to debate how I perceive the merit of various >> development subsets here compared to yourself.  I don't think it's really >> important whether anyone agrees with me or not about exactly the value of a >> full table lock implementation.  The main thing I'm concerned about is just >> that it's noted as a known risky part, one that could end up blocking the >> project's ability to commit even a subset of the proposed patch here. > > I think that one of the things that we need to get our hands around is > how we're going to distinguish the "snapshot" flavor of materialized > view from the "continuous update" flavor.  By definition, the latter > will only ever be supportable for a fairly restricted subset of all > possible queries, and I am assuming that we will not want to decide > what the behavior is going to be based on the query but rather based > on what the user specifies.  Anything else seems like it would be have > the potential for severe POLA violations.  So we need to think now > about how we'll distinguish between the two flavors.  I imagine some > sort of syntactic marker would be appropriate; not sure what. > > Reading this thread, I'm starting to grow concerned that some people > may feel that manually refreshed materialized views are not even worth > bothering with, because (the argument goes) you could just use some > table and write a function that updates it.  There's probably some > truth to that, but I guess my thought is that it would have some value > as a convenience feature; and eventually we might optimize it to the > point where it would make more sense to use the built-in feature > rather than rolling your own.  However, if we're going to have > complaints that manually refreshed materialized views suck and we > should only ever support materialized views to the extent that we can > make them automatically update on-the-fly, then let's have those > complaints now before someone spends several months of their life on > the project only to be told that we don't want it.  Let's be clear: I > think it's useful, but, if other people disagree, we need to iron that > out now. > > ...Robert I thing so manually refreshed materialized views has sense. It is similar to replication - there was replications like slony, but for some people is more important integrated replication in 9.0. More - manually refreshed (periodically refreshed) views can share lot if infrastructure with dynamically actualised views. I am sure so dynamical materialised views is bad task for GSoC - it is too large, too complex. Manually refreshed views is adequate to two months work and it has sense. Regards Pavel Stehule > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 12 Apr 2010 06:52
On Mon, Apr 12, 2010 at 2:16 AM, Pavel Stehule <pavel.stehule(a)gmail.com> wrote: > I am sure so > dynamical materialised views is bad task for GSoC - it is too large, > too complex. Manually refreshed views is adequate to two months work > and it has sense. That is my feeling also - though I fear that even the simplest possible implementation of this feature may be a stretch. Anyway we agree: keep it simple. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |