180 likes | 274 Vues
Learn how to implement the STOP operator in PostgreSQL version 9.0.1 to limit query results. Follow milestones from installation to code modification for this lab. Good luck on your midterm!
E N D
What’s PostgreSQL • It is an open-source DBMS • It has a huge community of developers and users • Full support for SQL standards • Written in C • Started in 1977 • Last Stable version 9.0.1 • Estimated cost is over 38M
History • Started as Ingres project, at the University of California, Berkeley 1977 • Developed into Post-Ingres 1982 • Two Ph.D. students, developed it intro Postgres95 1995 • PostgreSQL 1996–today
PostgreSQL Source Code From:http://www.nuug.no/pub/dist/20080408-goopen-tech-1210-martinez-postgresql.pdf
Architecture From:http://www.nuug.no/pub/dist/20080408-goopen-tech-1210-martinez-postgresql.pdf
Execution From: http://www.postgresql.org/files/developer/tour.pdf
Parsing SELECT * FROM tab1, tab2 WHERE tab1.a = tab2.f From: http://www.postgresql.org/files/developer/tour.pdf
Executor • Execute Plan Tree • Each node returns one tuple to its parent • Bottom level are scan of physical tables SELECT SUM(a1)+1 FROM a WHERE a2 < a3
Lab 3 • 20% of your score • Groups of three • Send to me the group information • Implement STOP operator • You must use PostgreSQL v 9.0.1
STOP operator SELECT * FROM R WHERE [condition clause] STOP 10; This query returns at most 10 tuples from relation R, satisfying the given condition clause
Milestones • Milestone #1: Installation from Source • Milestone #2: Add STOP to the grammar • Milestone #3: Change the query plan • Milestone #4: Implement the operator
Install from Source ./configure -prefix=<installation_dir> make su make install adduserpostgres mkdir <installation_dir>/data chownpostgres <installation_dir>/data su - postgres <installation_dir>/bin/initdb -D <installation_dir>/data <installation_dir>/bin/postgres -D <installation_dir>/data <installation_dir>/bin/createdb test <installation_dir>/bin/psql test
Add STOP to the grammar • Copy the supplied gram.y into src/backend/parser • Copy the supplied kwlist.h into src/include/parser • Make sure you have GNU bison installed • Run • make • make install
Change the query plan • You need to add STOP into any select query • To do that you have to modify planner.c and createplan.c STOP SELECT SELECT
Implement STOP operator • Add file nodeStop.cto src/backend/executor & nodeStop.hto src/include/executor/ • Add nodeStop.o to Makefile • Define T_STOP & STOPState • Check out Limit operator but implement your own • Implement these functions: • ExecInitStop (): Initializes the node (see nodeGroup.c) • ExecStopNext(): Fetches the next tuple stops after n tuples • ExecEndStop(): Ends the node processing • ExecStopRescan(): Start the node’s processing from the beginning
Understanding the Code • To navigate easier in the code, use doxygen.postgresql.org • It is good idea to start by looking in the modified files in my implementation, refer to Lab3.pdf to know them • Limit keyword is parsed within SELECT query, the grammar given for STOP parse it seperately
Good Luck On the Midterm!