GSoC 2011 Eager MV implementation proposal - Mailing list pgsql-hackers

<span class="Apple-style-span" style="border-collapse: collapse; font-family: arial, sans-serif; "><div
style="background-color:transparent; font-family: 'Times New Roman'; font-size: 13px; "><font size="3"><span
style="white-space:pre-wrap; "><br /></span></font></div><div style="background-color: transparent; font-family: 'Times
NewRoman'; font-size: 13px; "><span style="font-size: medium; "><span style="font-size: 12pt; font-family: 'Times New
Roman';color: rgb(0, 0, 0); background-color: transparent; font-weight: bold; font-style: normal; text-decoration:
underline;vertical-align: baseline; white-space: pre-wrap; ">Title:</span></span><span style="font-size: medium;
"><spanstyle="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); background-color: transparent;
font-weight:normal; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap; "><span
style="white-space:pre-wrap; "> </span> Implementation of Eager Materialized views in postgres</span></span></div><div
style="background-color:transparent; font-family: 'Times New Roman'; "><font size="3" style="font-size: 13px; "><span
style="font-size:12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); font-weight: normal; font-style: normal;
text-decoration:none; vertical-align: baseline; white-space: pre-wrap; background-color: transparent;
"></span></font><br/><span style="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0);
background-color:transparent; font-weight: bold; font-style: normal; text-decoration: underline; vertical-align:
baseline;white-space: pre-wrap; ">Name of Proposer & Email :</span><span style="font-size: 12pt; font-family:
'TimesNew Roman'; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal;
text-decoration:none; vertical-align: baseline; white-space: pre-wrap; ">   Aamir Khan & <a
href="mailto:ak4u2009@gmail.com"style="color: rgb(17, 65, 112); " target="_blank">ak4u2009@gmail.com</a></span><br
/><fontsize="3" style="font-size: 13px; "><span style="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0,
0,0); font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;
background-color:transparent; "></span></font><br /><span style="font-size: 12pt; font-family: 'Times New Roman';
color:rgb(0, 0, 0); background-color: transparent; font-weight: bold; font-style: normal; text-decoration: underline;
vertical-align:baseline; white-space: pre-wrap; ">Synopsis:</span><font size="3" style="font-size: 13px; "><span
style="font-size:12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); font-weight: normal; font-style: normal;
text-decoration:none; vertical-align: baseline; white-space: pre-wrap; background-color: transparent;
"></span></font><br/><font size="3" style="font-size: 13px; "><span style="font-size: 12pt; font-family: 'Times New
Roman';color: rgb(0, 0, 0); font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;
white-space:pre-wrap; background-color: transparent; "></span></font><br /><span style="font-size: 12pt; font-family:
'TimesNew Roman'; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal;
text-decoration:none; vertical-align: baseline; white-space: pre-wrap; ">I would like to implement eager materialized
view.Aneager materialized view will be updated whenever the view changes. This is done with a system of triggers on all
ofthe underlying tables.</span><br /><font size="3" style="font-size: 13px; "><span style="font-size: 12pt;
font-family:'Times New Roman'; color: rgb(0, 0, 0); font-weight: normal; font-style: normal; text-decoration: none;
vertical-align:baseline; white-space: pre-wrap; background-color: transparent; "></span></font><br /><span
style="font-size:12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); background-color: transparent; font-weight:
bold;font-style: normal; text-decoration: underline; vertical-align: baseline; white-space: pre-wrap; ">Benefits to the
PostgreSQLCommunity:</span><span style="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: underline; vertical-align:
baseline;white-space: pre-wrap; "><br /></span><span style="font-size: 12pt; font-family: 'Times New Roman'; color:
rgb(0,0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration: none;
vertical-align:baseline; white-space: pre-wrap; "> First of all, it would be the best if my work is helpful to
everybodywho 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.</span><br /><font size="3" style="font-size:
13px;"><span style="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); font-weight: normal;
font-style:normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap; background-color:
transparent;"></span></font><br /><span style="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;
white-space:pre-wrap; "> </span><br /><span style="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0,
0);background-color: transparent; font-weight: bold; font-style: normal; text-decoration: underline; vertical-align:
baseline;white-space: pre-wrap; ">Deliverables:<br /></span><span style="font-size: 12pt; font-family: 'Times New
Roman';color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration:
none;vertical-align: baseline; white-space: pre-wrap; "> First of all, at the end of whole my project is not only
finishingthe patch if possible, get patch into next PostgreSQL release, or keep git repository actual to last PosgreSQL
version.</span><br /><font size="3" style="font-size: 13px; "><span style="font-size: 12pt; font-family: 'Times New
Roman';color: rgb(0, 0, 0); font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;
white-space:pre-wrap; background-color: transparent; "></span></font><span style="font-size: 12pt; font-family: 'Times
NewRoman'; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal;
text-decoration:none; vertical-align: baseline; white-space: pre-wrap; "> </span><span style="font-size: 12pt;
font-family:'Times New Roman'; color: rgb(0, 0, 0); background-color: transparent; font-weight: bold; font-style:
normal;text-decoration: underline; vertical-align: baseline; white-space: pre-wrap; ">Bio<br /></span><span
style="font-size:12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); background-color: transparent; font-weight:
normal;font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap; "> </span><br
/><spanstyle="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); background-color: transparent;
font-weight:normal; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap; ">I am
studentof one of the most premier institute in India namely Indian Institute of Technology Roorkee pursuing my Bachelor
ofTechnology Computer Science & Engineering.</span><br /><font size="3" style="font-size: 13px; "><span
style="font-size:12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); font-weight: normal; font-style: normal;
text-decoration:none; vertical-align: baseline; white-space: pre-wrap; background-color: transparent;
"></span></font><br/><span style="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;
white-space:pre-wrap; ">I, as a part of team manages institutes website <a href="http://www.iitr.ac.in/" style="color:
rgb(17,65, 112); " target="_blank">www.iitr.ac.in</a> . We have setup a intranet portal inside campus of IIT Rookee
whichhas around 80 applications hosted on it. I have created ebooks sharing portal within the intranet. I have very
goodexperience in web designing (e.g, I have created website during last year summer internship <a
href="http://www.raysconsultants.com/"style="color: rgb(17, 65, 112); " target="_blank">www.raysconsultants.com</a> ).
Regardingopen source work : i have submitted many patches for phpmyadmin</span><br /><font class="Apple-style-span"
size="3"><spanclass="Apple-style-span" style="white-space: pre-wrap;"></span></font><font size="3" style="font-size:
13px;"><span style="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); font-weight: normal;
font-style:normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap; background-color:
transparent;"></span></font><br /><span style="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;
white-space:pre-wrap; ">I like to contribute in open  source communitites because it gives me experience,technical
expertiseand it teaches spirit of team work. And its my time to give back to community from which i gained a lot of
experienceand motivation.</span><br /><font size="3" style="font-size: 13px; "><span style="font-size: 12pt;
font-family:'Times New Roman'; color: rgb(0, 0, 0); font-weight: normal; font-style: normal; text-decoration: none;
vertical-align:baseline; white-space: pre-wrap; background-color: transparent; "></span></font><br /><font size="3"
style="font-size:13px; "><span style="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0);
font-weight:normal; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;
background-color:transparent; "></span></font><br /><span style="font-size: 12pt; font-family: 'Times New Roman';
color:rgb(0, 0, 0); background-color: transparent; font-weight: bold; font-style: normal; text-decoration: underline;
vertical-align:baseline; white-space: pre-wrap; ">Project plan</span><br /><font size="3" style="font-size: 13px;
"><spanstyle="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); font-weight: normal; font-style:
normal;text-decoration: none; vertical-align: baseline; white-space: pre-wrap; background-color: transparent;
"></span></font><br/><span style="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;
white-space:pre-wrap; ">I am completely free this summer. I intend to indulge in some of my hobbies this summer, like
Squashetc, leaving me plenty of time to complete my project. The tentative schedule as discussed with my mentor is
:</span><br/><font size="3" style="font-size: 13px; "><span style="font-size: 12pt; font-family: 'Times New Roman';
color:rgb(0, 0, 0); font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;
white-space:pre-wrap; background-color: transparent; "></span></font><br /><span style="font-size: 12pt; font-family:
'TimesNew Roman'; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal;
text-decoration:none; vertical-align: baseline; white-space: pre-wrap; ">(1) Identify how an eager MV system would
work,by labelling and</span><br /><span style="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;
white-space:pre-wrap; ">identifying every table, query, trigger, etc... This would be a</span><br /><span
style="font-size:12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); background-color: transparent; font-weight:
normal;font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap; ">written document
thathas been checked by me for completeness,</span><br /><span style="font-size: 12pt; font-family: 'Times New Roman';
color:rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration: none;
vertical-align:baseline; white-space: pre-wrap; ">precision, and correctness. </span><span style="font-size: 12pt;
font-family:'Times New Roman'; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style:
normal;text-decoration: underline; vertical-align: baseline; white-space: pre-wrap; ">(1 week)</span><font size="3"
style="font-size:13px; "><span style="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0);
font-weight:normal; font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap;
background-color:transparent; "></span></font><br /><font size="3" style="font-size: 13px; "><span style="font-size:
12pt;font-family: 'Times New Roman'; color: rgb(0, 0, 0); font-weight: normal; font-style: normal; text-decoration:
none;vertical-align: baseline; white-space: pre-wrap; background-color: transparent; "></span></font><br /><span
style="font-size:12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); background-color: transparent; font-weight:
normal;font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap; ">(2) Identify the
internalstructure of a parsed query, the basic</span><br /><span style="font-size: 12pt; font-family: 'Times New
Roman';color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration:
none;vertical-align: baseline; white-space: pre-wrap; ">atomic parts of this structure, and how each atomic structure
maps</span><br/><span style="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); background-color:
transparent;font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline; white-space:
pre-wrap;">precisely to our MV system described in (1). Again, a written document</span><br /><span style="font-size:
12pt;font-family: 'Times New Roman'; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal;
font-style:normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap; ">approved by myself, and
reviewedby the PostgreSQL community. (</span><span style="font-size: 12pt; font-family: 'Times New Roman'; color:
rgb(0,0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration: underline;
vertical-align:baseline; white-space: pre-wrap; ">2</span><br /><span style="font-size: 12pt; font-family: 'Times New
Roman';color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration:
underline;vertical-align: baseline; white-space: pre-wrap; ">weeks)</span><font size="3" style="font-size: 13px;
"><spanstyle="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); font-weight: normal; font-style:
normal;text-decoration: none; vertical-align: baseline; white-space: pre-wrap; background-color: transparent;
"></span></font><br/><font size="3" style="font-size: 13px; "><span style="font-size: 12pt; font-family: 'Times New
Roman';color: rgb(0, 0, 0); font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;
white-space:pre-wrap; background-color: transparent; "></span></font><br /><span style="font-size: 12pt; font-family:
'TimesNew Roman'; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal;
text-decoration:none; vertical-align: baseline; white-space: pre-wrap; ">(3) Outline how a "create_eager_mv" function
wouldwork, starting with</span><br /><span style="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;
white-space:pre-wrap; ">the interface (what parameters it takes), and working through the</span><br /><span
style="font-size:12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); background-color: transparent; font-weight:
normal;font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap; ">exact SQL commands
thatmust be executed to complete the process.</span><br /><span style="font-size: 12pt; font-family: 'Times New Roman';
color:rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration: none;
vertical-align:baseline; white-space: pre-wrap; ">Identify which SQL commands depend on the structure of the
query.</span><spanstyle="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); background-color:
transparent;font-weight: normal; font-style: normal; text-decoration: underline; vertical-align: baseline; white-space:
pre-wrap;"> (2</span><br /><span style="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: underline; vertical-align:
baseline;white-space: pre-wrap; ">weeks)</span><br /><font size="3" style="font-size: 13px; "><span style="font-size:
12pt;font-family: 'Times New Roman'; color: rgb(0, 0, 0); font-weight: normal; font-style: normal; text-decoration:
none;vertical-align: baseline; white-space: pre-wrap; background-color: transparent; "></span></font><br /><span
style="font-size:12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); background-color: transparent; font-weight:
normal;font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap; ">(4) Write unit
teststhat check a variety of queries against your (as</span><br /><span style="font-size: 12pt; font-family: 'Times New
Roman';color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration:
none;vertical-align: baseline; white-space: pre-wrap; ">of yet, unwritten) function. Ensure completeness by reviewing
withme.</span><br /><span style="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: underline; vertical-align:
baseline;white-space: pre-wrap; ">(1 week)</span><br /><font size="3" style="font-size: 13px; "><span style="font-size:
12pt;font-family: 'Times New Roman'; color: rgb(0, 0, 0); font-weight: normal; font-style: normal; text-decoration:
none;vertical-align: baseline; white-space: pre-wrap; background-color: transparent; "></span></font><br /><span
style="font-size:12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); background-color: transparent; font-weight:
normal;font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap; ">(5) Finally, given
allof the above, translate (3) into working code,</span><br /><span style="font-size: 12pt; font-family: 'Times New
Roman';color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration:
none;vertical-align: baseline; white-space: pre-wrap; ">likely implemented in PL/Python or C, that passes the unit
tests.</span><br/><span style="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); background-color:
transparent;font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline; white-space:
pre-wrap;">Submit to PostgreSQL community for review. </span><span style="font-size: 12pt; font-family: 'Times New
Roman';color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration:
underline;vertical-align: baseline; white-space: pre-wrap; ">(2 weeks)</span><font size="3" style="font-size: 13px;
"><spanstyle="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); font-weight: normal; font-style:
normal;text-decoration: none; vertical-align: baseline; white-space: pre-wrap; background-color: transparent;
"></span></font><br/><span style="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;
white-space:pre-wrap; "> </span><br /><font size="3" style="font-size: 13px; "><span style="font-size: 12pt;
font-family:'Times New Roman'; color: rgb(0, 0, 0); font-weight: normal; font-style: normal; text-decoration: none;
vertical-align:baseline; white-space: pre-wrap; background-color: transparent; "></span></font><br /><span
style="font-size:12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); background-color: transparent; font-weight:
normal;font-style: normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap; "> </span><br
/><spanstyle="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); background-color: transparent;
font-weight:bold; font-style: normal; text-decoration: underline; vertical-align: baseline; white-space: pre-wrap;
">ContactInformation</span><font size="3" style="font-size: 13px; "><span style="font-size: 12pt; font-family: 'Times
NewRoman'; color: rgb(0, 0, 0); font-weight: normal; font-style: normal; text-decoration: none; vertical-align:
baseline;white-space: pre-wrap; background-color: transparent; "></span></font><br /><font size="3" style="font-size:
13px;"><span style="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0); font-weight: normal;
font-style:normal; text-decoration: none; vertical-align: baseline; white-space: pre-wrap; background-color:
transparent;"></span></font><br /><span style="font-size: 12pt; font-family: 'Times New Roman'; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;
white-space:pre-wrap; ">Name: Aamir Khan</span><br /><span style="font-size: 12pt; font-family: 'Times New Roman';
color:rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration: none;
vertical-align:baseline; white-space: pre-wrap; ">Country: Indian</span><br /><span style="font-size: 12pt;
font-family:'Times New Roman'; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style:
normal;text-decoration: none; vertical-align: baseline; white-space: pre-wrap; ">School and degree: Indian Institute of
Technology,Roorkee, Bachelor of Technology</span><br /><span style="font-size: 12pt; font-family: 'Times New Roman';
color:rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration: none;
vertical-align:baseline; white-space: pre-wrap; ">Email: <a href="mailto:ak4u2009@gmail.com" style="color: rgb(17, 65,
112);" target="_blank">ak4u2009@gmail.com</a> </span><br /><span style="font-size: 12pt; font-family: 'Times New
Roman';color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration:
none;vertical-align: baseline; white-space: pre-wrap; ">Phone: +91-9557647357</span></div></span>