GSoC 2011 Eager MV implementation proposal
От | AAMIR KHAN |
---|---|
Тема | GSoC 2011 Eager MV implementation proposal |
Дата | |
Msg-id | AANLkTin6BJVrGpLn-S9ivKT1ZPBDJwX_2v7ZcsWxckD_@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: GSoC 2011 Eager MV implementation proposal
Re: GSoC 2011 Eager MV implementation proposal |
Список | 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>
В списке pgsql-hackers по дате отправления: