Re: GSoC - proposal - Materialized Views in PostgreSQL - Mailing list pgsql-hackers

pavelbaros wrote:
> I am also waiting for approval for my repository named 
> "materialized_view" on git.postgresql.org, so I could publish 
> completed parts. 

Presuming that you're going to wander there and get assigned what looks 
like an official repo name for this project is a bit...optimistic.  I 
would recommend that you publish to something like github instead (you 
can fork http://github.com/postgres/postgres ), and if the work looks 
good enough that it gets picked up by the community maybe you migrate it 
onto the main site eventually.  git.postgresql.org is really not setup 
to be general hosting space for everyone who has a PostgreSQL related 
project; almost every repo on there belongs to someone who has already 
been a steady project contributor for a number of years.

(Switching to boilerplate mode for a paragraph...) You have picked a 
PostgreSQL feature that is dramatically more difficult than it appears 
to be, and I wouldn't expect you'll actually finish even a fraction of 
your goals in a summer of work.  You're at least in plentiful 
company--most students do the same.  As a rule, if you see a feature on 
our TODO list that looks really useful and fun to work on, it's only 
still there because people have tried multiple times to build it 
completely but not managed to do so because it's harder than it 
appears.  This is certainly the case with materialized views.

You've outlined a reasonable way to build a prototype that does a 
limited implementation here.  The issue is what it will take to extend 
that into being production quality for the real-world uses of 
materialized views.  How useful your prototype is depends on how well it 
implements a subset of that in a way that will get used by the final design.

The main hidden complexity in this particular project relates to 
handling view refreshes.  The non-obvious problem is that when the view 
updates, you need something like a SQL MERGE to really handle that in a 
robust way that doesn't conflict with concurrent access to queries 
against the materialized view.  And work on MERGE support is itself 
blocked behind the fact that PostgreSQL doesn't have a good way to lock 
access to a key value that doesn't exist yet--what other databases call 
key range locking.  See the notes for "Add SQL-standard 
MERGE/REPLACE/UPSERT command" at http://wiki.postgresql.org/wiki/Todo 
for more information.

You can work around that to build a prototype by grabbing a full table 
lock on the materialized view when updating it, but that's not a 
production quality solution.  Solving that little detail is actually 
more work than the entire project you've outlined.  Your suggested 
implementation--"In function CloseIntoRel executor swap relfilenode's of 
temp table and original table and finally delete temp table"--is where 
the full table lock is going to end up at.  The exact use cases that 
need materialized views cannot handle a CLUSTER-style table recreation 
each time that needs an exclusive lock to switchover, so that whole part 
of your design is going to be a prototype that doesn't work at all like 
what needs to get built to make this feature committable.  It's also not 
a reasonable assumption that you have enough disk space to hold a second 
copy of the MV in a production system.

Once there's a good way to merge updates, how to efficiently generate 
them against the sort of large data sets that need materalized views--so 
you just write out the updates rather than a whole new copy--is itself a 
large project with a significant quantity of academic research to absorb 
before starting.  Dan Colish at Portland State has been playing around 
with prototypes for the specific problem of finding a good algorithm for 
view refreshing that is compatible with PostgreSQL's execution model.  
He's already recognized the table lock issue here and for the moment is 
ignoring that part.  I don't have a good feel yet for how long the 
targeted update code will take to mature, but based on what I do know I 
suspect that little detail is also a larger effort than the entire scope 
you're envisioning.  There's a reason why the MIT Press compendium 
"Materialized Views: Techniques, Implementations, and Applications" is 
over 600 pages long--I hope you've already started digging through that 
material.

Now, with all that said, that doesn't mean there's not a useful project 
for you buried in this mess.  The first two steps in your plan:

1) create materialized view
2) change rewriter

Include building a prototype grammer, doing an initial executor 
implementation, and getting some sort of rewriter working.  That is 
potentially good groundwork to lay here.  I would suggest that you 
completely drop your step 3:

3) create command that takes snapshot (refresh MV)

Because you cannot built that in a way that will be useful (and by that 
I mean committable quality) until there's a better way to handle updates 
than writing a whole new table and grabbing a full relation lock to 
switch to it.  To do a good job just on the first two steps should take 
at least a whole summer anyway--there's a whole stack of background 
research needed I haven't seen anyone do yet, and that isn't on your 
plan yet.  There is a precedent for taking this approach.  After getting 
stalled trying to add the entirety of easy partitioning to PostgreSQL, 
the current scope has been scaled back to just trying to get the syntax 
and on-disk structure right, then finish off the implementation.  See 
http://wiki.postgresql.org/wiki/Table_partitioning to get an idea how 
that's been broken into those two major chunks.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us