Re: GSoC - proposal - Materialized Views in PostgreSQL

On Sat, Apr 10, 2010 at 11:40 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> To be frank, that makes for a materalized view implementation of little
> value over what you can currently do as far as I'm concerned.  It might be
> interesting as a prototype, but that's not necessarily going to look like
> what's needed to do this for real at all.  I'm not a big fan of dumping work
> into projects when you can see exactly how it's going to fail before you
> even get started.  As I see if, if you know where it's going to fall down,
> you don't need to build a prototype as an exercise to show you how to build
> it--you should work on that part first instead.

Hopefully, you're already aware that I have enormous respect for your
opinions on a wide variety of topics; if not, let me publicly say that
I absolutely do.

Having said that, I disagree with your conclusions in this instance.
I see nothing but upside from this work. It is vastly easier to write
a patch that builds on existing functionality than it is to write
something new from scratch. If there's any value in having manually
refreshed materialized views, then having the simplest possible
implementation of what those can look like committed will make it far
easier to plan out next steps. While the proposed implementation may
not solve a huge number of real-world problems, I think there's a good
argument that some people will get good use of it. Not everyone has
1TB tables with continuous access patterns. And, provided that it
doesn't conflict with anything we want to do in the future, being
useful to some people is a good enough reason to put it in.

I also think that you're underestimating the number of problems that
will have to be solved to get this done. It's going to take some
significant work - both design work and coding work - to figure out
how this should integrate into the rest of the system. (What should
be the value of pg_class.relkind? Where should the node
representation of the snapshot query be stored? And did we handle all
of those OID dependencies correctly?)

Where I can see this possibly falling down (other than being just too
much work for a relative PostgreSQL novice to get it done in one
summer) is if there are concerns about it being incompatible with
incrementally-updated views. I imagine that we're going to want to
eventually support both, so we need to make sure that this
implementation doesn't box us into a corner. But as far as snapshot
views go, complaining that the proposed locking is too strong doesn't
seem quite fair. Fixing that, AFAICS, is a very hard project,
possibly involving significant planner support and an implementation
of MERGE, and I would much rather try to land a fundamentals patch
like this first and then deal with the gyrations that will be involved
in making this work than try to land the whole thing all at once.

Of course, if I'm missing something, and there's a SIMPLE way to get
materialized views that can be refreshed without a full-table lock,
that's another story altogether - maybe you have an idea?

Finally, even if we decided NOT to merge this patch because of the
limitations you mention (and right now that doesn't seem to be the
consensus), having this part of it completed as a starting point for
future work might be reason enough by itself.

In short: I think you may be letting the perfect be the enemy of the good.

...Robert