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

Heikki Linnakangas wrote:
> Your proposal basically describes
> doing 1, in a limited fashion where the view is not updated
> automatically, but only when the DBA runs a command to refresh it. I'm
> not sure if that's useful enough on its own, writing "CREATE
> MATERIALIZED VIEW ... SELECT ..." doesn't seem any easier than just
> writing "CREATE TABLA AS ...". But if you can do something about 2, or
> even a very limited part of 1, keeping the view up-to-date
> automatically, it becomes much more useful.
>   

You've hit upon the core issue here.  You can build materialized views 
right now using "CREATE TABLE AS".  You can even update them by creating 
a new table the same way, with a new name, and doing the 
LOCK/RENAME/DROP shuffle--what people used to do for rewriting bloated 
tables before there was CLUSTER.  The first step in the proposal here is 
essentially syntax to give an easier UI for that.  That's an interesting 
step, but recognize that it doesn't actually provide anything you can't 
do already.

If you then note that doing any sort of incremental update to the view 
is a hard problem, and that a lot of the useful cases for materialized 
views involve tables where it's impractical to recreate the whole thing 
anyway, you'll inevitably find yourself deeply lost in the minutia of 
how to handle the updates.  It's really the core problem in building 
what people expect from a materialized view implementation in a serious 
database.  Chipping away at the other pieces around it doesn't move the 
feature that far forward, even if you get every single one of them 
except incremental updates finished, because everything else combined is 
still not that much work in comparison to the issues around updates.

There certainly are a fair number of subproblems you can break out of 
here.  I just think it's important to recognize that the path that leads 
to a useful GSoC project and the one that gives a production quality 
materialized view implementation may not have that much in common, and 
to manage expectations on both sides accordingly.  If Pavel thinks he's 
going to end up being able to say "I added materialized views to 
PostgreSQL" at the end of the summer, that's going to end in 
disappointment.  And if people think this project plan will lead to 
being able to claim PostgreSQL now has this feature, that's also not 
going to go well.  If the scope is "add initial grammar and rewriting 
moving toward a future materialized view feature", which the underlying 
implementation noted as a stub prototype, that might work out OK.  This 
is why I likened it to the work on "Syntax for partitioning", which has 
a similarly focused subgoal structure.

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