The parse tree above should look familiar - it’s almost precisely the same as the abstract syntax tree (AST) we saw ActiveRecord create earlier.
#Postgres in clause code
If you want to learn more about how Postgres parses SQL statements, follow the flow of control down from exec _ simple _ query through another C function called pg _ parse _ query.Īs you can see there are many helpful and detailed comments in the Postgres source code that not only explain what is happening but also point out important design decisions. Using LLDB and enabling some C logging code, I observed the Postgres parser produce this parse tree for our Captain Nemo query:Īt the top is a node representing the entire SQL statement, and below that are child nodes or branches that represent the different portions of the SQL statement syntax: the target list (a list of columns), the from clause (a list of tables), the where clause, the sort order and a limit count. Postgres parses SQL statements in exactly the same way. In Chapter One, I go through a detailed example of the LALR parse algorithm used by Bison and Ruby. If you’re interested in that sort of thing, I’d suggest taking a look at my book Ruby Under a Microscope.
I won’t take the time today to explain how parsing algorithms work in detail. Each grammar rule is triggered when the generated parser finds a corresponding pattern or syntax in the SQL string, and inserts a new C memory structure into the parse tree data structure. The generated parser code is what runs inside of Postgres when we send it SQL commands.
#Postgres in clause series
Bison runs during the Postgres C build process and generates parser code based on a series of grammar rules.
#Postgres in clause generator
It turns out that Postgres uses the same parsing technology that Ruby does, a parser generator called Bison. How does Postgres understand the SQL string we sent it? How does it make sense of the SQL keywords and expressions in our select statement? Through a process called parsing, Postgres converts our SQL string into an internal data structure it understands, the parse tree. You can find a link to it below, along with an LLDB backtrace which gives some context about exactly when and how Postgres calls exec _ simple _ query. The C function inside of Postgres that implements this 4 step process is called exec _ simple _ query. In this presentation I’ll briefly touch on the first three topics, and then focus more on the last step: Execute. Finally, Postgres actually executes our query. Postgres doesn’t run our query until it has a plan. After that, Postgres generates a plan for finding our data. Next Postgres analyzes and rewrites our query, optimizing and simplifying it using a series of complex algorithms. In the first step, Postgres parses our SQL statement and converts it into a series of C memory structures, a parse tree. Postgres processes each SQL command we send it using a four step process. What does Postgres do with this SQL string? How does it understand what we meant? How does it know what data we are looking for? Professor Aronnax and Captain Nemo plot the course of the Nautilus.įinding a single name in a string column like this should be straightforward, shouldn’t it? We’ll hold tightly onto this select statement while we explore Postgres internals, like a rope deep sea divers use to find their way back to the surface. Here’s the example query from the first half of my presentation we’ll follow Postgres as it searches for Captain Nemo:
It was clean, well-documented, and easy to follow.įind out for yourself how Postgres works internally by following me on a journey deep inside a tool you use everyday. In the end, the Postgres source code delighted me. In case you understand C, I’ll also leave you a few landmarks and signposts you can look for if you ever decide to hack on Postgres internals. I’ll use a series of simple, conceptual diagrams to explain how Postgres executed my query. I will describe the path I took and what I saw along the way. This post is an informal journal of my trip through the guts of Postgres. How did Postgres understand my query? How did it actually find the data I was looking for? I executed a very simple select statement and watched what Postgres did with it using LLDB, a C debugger. Preparing for this presentation over the Summer, I decided to read through parts of the PostgreSQL C source code. You can also read posts one and two in the series.Ĭaptain Nemo takes Professor Aronnax on a tour of the engine room, a fascinating description of future technology from an 1870 perspective. The series was originally published on his personal blog, and we are republishing it on Codeship with his kind permission. You can also watch the video recording of the presentation. This is the third in a series of Postgres posts that Pat Shaughnessy wrote based on his presentation at the Barcelona Ruby Conference.
0 kommentar(er)
