Re: GSoC 2011 Eager MV implementation proposal

Title: Implementation of Eager Materialized views in postgres

Name of Proposer & Email : Aamir Khan & ak4u2009(at)gmail(dot)com

Synopsis:

I would like to implement eager materialized view.An eager materialized view
will be updated whenever the view changes. This is done with a system of
triggers on all of the underlying tables.

Benefits to the PostgreSQL Community:
First of all, it would be the best if my work is helpful to everybody who
misses materialized views in PostgreSQL, because PostgreSQL do not have
still implemented materialized views. In addition, MV is mentioned as the
top rated feature in TODO list.

Deliverables:
First of all, at the end of whole my project is not only finishing the
patch if possible, get patch into next PostgreSQL release, or keep git
repository actual to last PosgreSQL version.
Bio

I am student of one of the most premier institute in India namely Indian
Institute of Technology Roorkee pursuing my Bachelor of Technology Computer
Science & Engineering.

I, as a part of team manages institutes website www.iitr.ac.in . We have
setup a intranet portal inside campus of IIT Rookee which has around 80
applications hosted on it. I have created ebooks sharing portal within the
intranet. I have very good experience in web designing (e.g, I have created
website during last year summer internship www.raysconsultants.com ).
Regarding open source work : i have submitted many patches for phpmyadmin

I like to contribute in open source communitites because it gives me
experience,technical expertise and it teaches spirit of team work. And its
my time to give back to community from which i gained a lot of experience
and motivation.

Project plan

I am completely free this summer. I intend to indulge in some of my hobbies
this summer, like Squash etc, leaving me plenty of time to complete my
project. The tentative schedule as discussed with my mentor is :

(1) Identify how an eager MV system would work, by labelling and
identifying every table, query, trigger, etc... This would be a
written document that has been checked by me for completeness,
precision, and correctness. (1 week)

(2) Identify the internal structure of a parsed query, the basic
atomic parts of this structure, and how each atomic structure maps
precisely to our MV system described in (1). Again, a written document
approved by myself, and reviewed by the PostgreSQL community. (2
weeks)

(3) Outline how a "create_eager_mv" function would work, starting with
the interface (what parameters it takes), and working through the
exact SQL commands that must be executed to complete the process.
Identify which SQL commands depend on the structure of the query. (2
weeks)

(4) Write unit tests that check a variety of queries against your (as
of yet, unwritten) function. Ensure completeness by reviewing with me.
(1 week)

(5) Finally, given all of the above, translate (3) into working code,
likely implemented in PL/Python or C, that passes the unit tests.
Submit to PostgreSQL community for review. (2 weeks)

Contact Information

Name: Aamir Khan
Country: Indian
School and degree: Indian Institute of Technology, Roorkee, Bachelor of
Technology
Email: ak4u2009(at)gmail(dot)com
Phone: +91-9557647357

On 03/31/2011 04:38 PM, AAMIR KHAN wrote:
> I would like to implement eager materialized view.An eager
> materialized view will be updated whenever the view changes. This is
> done with a system of triggers on all of the underlying tables.

Last summer someone worked on snapshot materialized views. That didn't
result in anything that could be committed. If you wanted to work on
trying to actually finish that, that might go somewhere useful. There
are a number of hard problems in getting a working implementation of
materialized views that all get ignored by all of the student proposals
we get, and what you're talking about doesn't address any of them.

You really should read all of the messages in the following threads:

http://archives.postgresql.org/pgsql-hackers/2010-04/msg00479.php
http://archives.postgresql.org/pgsql-hackers/2010-06/msg00743.php
http://archives.postgresql.org/pgsql-hackers/2010-07/msg00396.php

And the following summaries:

http://wiki.postgresql.org/wiki/Materialized_Views_GSoC_2010
http://rhaas.blogspot.com/2010/04/materialized-views-in-postgresql.html

And then say how what you're suggesting fits into the issues raised last
summer. The theory and way to implement eager MVs are interesting
problems. But working on them won't lead toward code that can be
committed to PostgreSQL this year.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us

As you people think and may be possible that complete implementation of
Eager MVs cannot be completed in summer. So maybe i can pick up the work
left to be done in snapshot MVs. I have cloned the repository of pavel baros
from https://github.com/pbaros/postgres.git and i will be looking to find
whats left out.

Could anybody help me in figuring out what is left to be done in snapshot
MVs implementation?

There are a number of hard problems in getting a working implementation of
> materialized views that all get ignored by all of the student proposals we
> get, and what you're talking about doesn't address any of them.
>
>

As soon as i know the shortcomings of snapshot MVs implementation and once
go through the code committed during GSoC, I will revert back with the
issues and how would i be tackling them.

You really should read all of the messages in the following threads:
>
> http://archives.postgresql.org/pgsql-hackers/2010-04/msg00479.php
> http://archives.postgresql.org/pgsql-hackers/2010-06/msg00743.php
> http://archives.postgresql.org/pgsql-hackers/2010-07/msg00396.php
>
> And the following summaries:
>
> http://wiki.postgresql.org/wiki/Materialized_Views_GSoC_2010
> http://rhaas.blogspot.com/2010/04/materialized-views-in-postgresql.html
>
> And then say how what you're suggesting fits into the issues raised last
> summer. The theory and way to implement eager MVs are interesting
> problems. But working on them won't lead toward code that can be committed
> to PostgreSQL this year.
>

On Thu, Apr 7, 2011 at 4:49 PM, AAMIR KHAN <ak4u2009(at)gmail(dot)com> wrote:
> As you people think and may be possible that complete implementation of
> Eager MVs cannot be completed in summer. So maybe i can pick up the work
> left to be done in snapshot MVs. I have cloned the repository of pavel baros
> from https://github.com/pbaros/postgres.git and i will be looking to find
> whats left out.

You had better start by getting a clear statement from Pavel as to
whether he wishes to release the code in that repository under the
PostgreSQL license. I am not sure that he ever formally submitted it.

I still think you should start with an easier project.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

> You had better start by getting a clear statement from Pavel as to
> whether he wishes to release the code in that repository under the
> PostgreSQL license. I am not sure that he ever formally submitted it.

I don't think it's reasonable for a student to do that. That really
needs to be up to us in the project.

> I still think you should start with an easier project.

Can you give him some suggestions? He's interested in materialized
views; what chunk is biteable-off?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Thu, Apr 7, 2011 at 6:31 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> You had better start by getting a clear statement from Pavel as to
>> whether he wishes to release the code in that repository under the
>> PostgreSQL license.  I am not sure that he ever formally submitted it.
>
> I don't think it's reasonable for a student to do that.  That really
> needs to be up to us in the project.

My point is that we cannot accept code unless it's clear that the
author wishes to contribute it under our license. We cannot assume
that code from any random source on the Internet falls into that
category.

>> I still think you should start with an easier project.
>
> Can you give him some suggestions?  He's interested in materialized
> views; what chunk is biteable-off?

Materialized views is a big project that will be quite invasive and
touch many areas of the system; it will be comparable to SQL/MED, in
that both add a new relkind. I don't think there's any chunk that can
be broken off there that will be reasonable for a first patch.

I made some other suggestions via IM last night, hence my use of the
word "still". If anyone has ideas, toss 'em out... one idea I had
for a project, if it's not stepping on Greg Stark's toes, is to pick
up the EXPLAIN (RESOURCE) patch that he was working on a while back
and finish it off.

Mind you, if he wants to work on materialized views, he's more than
welcome to do so, and if the patch is awesome, I will be as happy as
anyone. But it took me two years to work up to handling patches of
that complexity, so I think it's reasonable to suggest that he might
also need more than zero ramp-up. It's to no one's benefit if new
contributors try a project that is too hard, get discouraged, and give
up.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company