Implementing ``STOP'' operator in PostgerSQL

Implementing ``STOP'' operator in PostgerSQL

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!

Thanks

Implementing ``STOP'' operator in PostgerSQL