1 / 18

Implementing STOP Operator in PostgreSQL

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!

kenny
Télécharger la présentation

Implementing STOP Operator in PostgreSQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Implementing ``STOP'' operator in PostgerSQL

  2. 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

  3. 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

  4. PostgreSQL Source Code From:http://www.nuug.no/pub/dist/20080408-goopen-tech-1210-martinez-postgresql.pdf

  5. Architecture From:http://www.nuug.no/pub/dist/20080408-goopen-tech-1210-martinez-postgresql.pdf

  6. Execution From: http://www.postgresql.org/files/developer/tour.pdf

  7. Parsing SELECT * FROM tab1, tab2 WHERE tab1.a = tab2.f From: http://www.postgresql.org/files/developer/tour.pdf

  8. 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

  9. 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

  10. 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

  11. Milestones • Milestone #1: Installation from Source • Milestone #2: Add STOP to the grammar • Milestone #3: Change the query plan • Milestone #4: Implement the operator

  12. 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

  13. 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

  14. 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

  15. 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

  16. 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

  17. Good Luck On the Midterm!

  18. Thanks

More Related