Hacking PostgreSQL
This is a modal window.
The media could not be loaded, either because the server or network failed or because the format is not supported.
Formal Metadata
| Title |
| |
| Title of Series | ||
| Number of Parts | 561 | |
| Author | ||
| License | CC Attribution 2.0 Belgium: You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor. | |
| Identifiers | 10.5446/44300 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language |
Content Metadata
| Subject Area | ||
| Genre | ||
| Abstract |
|
FOSDEM 201929 / 561
1
9
10
15
18
19
23
24
27
29
31
33
34
35
38
39
40
43
47
49
52
53
54
55
58
59
60
63
65
67
69
70
78
80
82
87
93
95
97
102
103
104
107
110
111
114
116
118
120
122
123
126
127
131
133
136
137
139
141
142
148
153
155
157
159
163
164
168
169
170
171
172
173
174
181
183
185
187
188
193
196
197
198
199
200
201
205
207
208
209
211
213
214
218
221
223
224
226
230
232
234
235
236
244
248
250
251
252
253
255
256
257
262
263
264
268
269
271
274
275
276
278
280
281
283
284
288
289
290
293
294
296
297
300
301
304
309
311
312
313
314
315
317
318
321
322
327
332
333
334
335
336
337
338
339
340
343
345
346
352
353
355
356
357
359
360
362
369
370
373
374
375
376
377
378
383
384
387
388
389
390
391
393
394
395
396
406
408
409
412
413
414
415
419
420
425
426
431
432
433
434
435
436
438
439
440
441
445
446
447
448
453
455
457
459
466
467
471
473
474
475
476
479
480
484
485
486
489
491
492
496
499
500
502
505
507
508
512
515
517
518
529
531
533
534
535
536
539
540
546
550
551
552
553
554
555
557
558
559
560
561
00:00
Default (computer science)Core dumpLocal ringPatch (Unix)Capability Maturity ModelSystem callHacker (term)Physical lawInclusion mapCodeExecution unitMaß <Mathematik>DemonEmailFormal languageProcedural programmingTable (information)Electronic program guideComputing platformPlanar graphPole (complex analysis)MathematicsLink (knot theory)RankingConvex hullSineTime zoneFuzzy logicMenu (computing)Front and back endsExtension (kinesiology)Social classCartesian coordinate systemDebuggerPhysical systemTerm (mathematics)WritingSource codeMultiplication signModule (mathematics)Subject indexingDirectory serviceBitTable (information)View (database)1 (number)Hacker (term)File formatAsynchronous Transfer ModeCodeProcess (computing)Electronic mailing listBuildingSoftware maintenanceSoftware developerRevision controlDatabaseStandard Generalized Markup LanguageInformationTheory of relativityConnected spaceTrailDirection (geometry)Binary codeLibrary catalogProcedural programmingInterface (computing)Client (computing)Library (computing)Set (mathematics)Utility softwareComputer fileGoodness of fitConfiguration spaceRow (database)Core dumpConnectivity (graph theory)MathematicsBranch (computer science)Patch (Unix)Different (Kate Ryan album)Crash (computing)EmailComputer programmingDefault (computer science)Right angleSoftware repositorySequelParsingWeb 2.0Logic gateAttribute grammarInstallation artLevel (video gaming)Binary fileFormal grammarInformation securityStatement (computer science)Computer animationLecture/Conference
09:14
Procedural programmingInclusion mapRankingMaxima and minimaConvex hullCodeSinePay televisionVacuumGamma functionFingerprintExecution unitMenu (computing)Annulus (mathematics)Wrapper (data mining)Independence (probability theory)Asynchronous Transfer ModeInfinityTelephone number mappingMathematical optimizationMultitier architectureConditional-access moduleColor managementServer (computing)Software testingFront and back endsComputer filePhysical systemComputing platformSubject indexingQuery languageProcess (computing)Directory serviceStatisticsRight angleTopological vector spaceCommunications protocolDifferent (Kate Ryan album)DatabaseMathematical optimizationCodeLibrary catalogAsynchronous Transfer ModeWrapper (data mining)Table (information)TwitterCASE <Informatik>Level (video gaming)BitConnectivity (graph theory)Default (computer science)Hacker (term)Direction (geometry)Linear regressionExtension (kinesiology)Functional (mathematics)TrailCoefficient of determinationLibrary (computing)Memory managementTime zoneMereologyTheory of relativityResultantTerm (mathematics)Exterior algebraBootstrap aggregatingNumberFormal languageInterpreter (computing)Structural loadSource codeInterface (computing)Data storage devicePlanningTimestampSlide ruleFree variables and bound variablesSystem callJust-in-Time-CompilerJava appletData structureCausalityLipschitz-StetigkeitRotationForm (programming)DebuggerComputer programmingSemiconductor memoryFormal grammarParsingMultiplication signLecture/Conference
18:22
Execution unitQuery languageCodeInclusion mapCoroutineGamma functionMathematical optimizationExtension (kinesiology)StatisticsMoving averageSimultaneous localization and mappingDuality (mathematics)Menu (computing)Color managementLibrary (computing)Rule of inferenceMach's principleBound stateWeb pageFatou-MengeTransport Layer SecurityDialectFormal grammarDenial-of-service attackoutputInformationMaxima and minimaLibrary (computing)Product (business)ParsingBitCASE <Informatik>Fuzzy logicMathematical optimizationStatement (computer science)Formal grammarStandard deviationRewritingCommunications protocolImplementationReplication (computing)CoroutineFront and back endsFunctional (mathematics)Directory serviceTransport Layer SecurityGeneric programmingInformationSyntaxbaumPartition (number theory)Physical systemExtension (kinesiology)Web pageMereologyMathematicsMemory managementElement (mathematics)AuthenticationSubject indexingElectronic mailing listQuery languagePoint (geometry)Patch (Unix)Information securityString (computer science)ParsingGoodness of fitDifferent (Kate Ryan album)Port scannerSoftware bugMatching (graph theory)Token ringStatisticsRegulärer Ausdruck <Textverarbeitung>Constraint (mathematics)Row (database)Level (video gaming)Buffer solutionEncryptionInterface (computing)Inheritance (object-oriented programming)BefehlsprozessorProcess (computing)CodeSoftware testingClient (computing)Connected spaceVacuumOrder (biology)Computing platformMaschinelle ÜbersetzungData storage deviceMultiplication signTranslation (relic)Binary fileFormal languagePlanningTable (information)Search engine (computing)Right angleDot productData managementDeclarative programmingSet (mathematics)Machine codeComputer programmingLoop (music)Data structureMultiplicationLipschitz-StetigkeitUtility softwareAdditionComputer configurationLoginLecture/Conference
27:30
Duality (mathematics)MathematicsConvex hullCase moddingTunisMaxima and minimaMusical ensembleDenial-of-service attackError messageInterior (topology)Front and back endsContext awarenessMemory managementGeneric programmingTraffic reportingLibrary catalogRight angleNumberTupleElectronic program guideSemiconductor memoryLie groupElectronic mailing listComputer configurationFigurate numberKey (cryptography)Functional (mathematics)Parameter (computer programming)Hacker (term)Token ringWordLevel of measurementPhysical systemBitLinear regressionStatisticsDifferenz <Mathematik>Formal grammarError messageEvent horizonBoolean algebraLibrary (computing)MereologyTemplate (C++)ResultantFlagQuery languageProduct (business)Hydraulic jumpOcean currentDatabase transactionExtension (kinesiology)WritingElement (mathematics)Resource allocationPosition operatorCodeSoftware testingShift operatorFehlererkennungscodeMathematicsState of matterParsingDifferent (Kate Ryan album)CASE <Informatik>Data compressionRule of inferenceData structureComputer fileMoving averageLevel (video gaming)TrailProcess (computing)Standard Generalized Markup LanguageRevision controlSource codeReduction of orderFile formatData storage deviceDefault (computer science)Goodness of fitLine (geometry)LeakFreewareStandard deviationBuildingReading (process)Variable (mathematics)Lecture/Conference
36:39
Error messageEuler anglesVolumeKey (cryptography)CoroutineData typeInclusion mapVertex (graph theory)Gamma functionEmailData structureTupleSingle-precision floating-point formatMathematical singularityMaizeAlpha (investment)Variable (mathematics)Software testingComputer programTablet computerFAQMaß <Mathematik>Game theoryFunction (mathematics)Core dumpPatch (Unix)Context awarenessSound effectCodeLine (geometry)Table (information)String (computer science)InformationFitness functionTupleTheory of relativityData structureKey (cryptography)Computer filePhysical systemImplementationFerry CorstenKnapsack problemLatent heatLattice (order)EmailPatch (Unix)File formatDifferent (Kate Ryan album)Maxima and minimaElectronic mailing listEqualiser (mathematics)Functional (mathematics)Pointer (computer programming)Order (biology)MereologyCoroutineComputer programmingMemory managementBlock (periodic table)LengthContext awarenessForm (programming)Hacker (term)Hand fanCore dumpMultiplication signKernel (computing)DataflowNumberDifferenz <Mathematik>BitQuery languageError messageRow (database)Inclusion mapSyntaxbaumDatabase transactionVariable (mathematics)Data storage deviceDoubling the cubeEntire functionData compressionType theoryCache (computing)Library catalogScaling (geometry)Binary codeSemiconductor memoryEvent horizonDescriptive statisticsPoint (geometry)Linear regressionSoftware testingRight angleFlagAttribute grammarMessage passing1 (number)Traffic reportingSystem callMultiplicationIdentifiabilityCartesian coordinate systemSoftware developerFront and back endsLevel (video gaming)Electronic program guideNetwork topologyMiniDiscBoilerplate (text)Lecture/Conference
45:47
Canonical ensembleComputer animation
Transcript: English(auto-generated)
00:06
So welcome to the second talk in the Postgres Dev Room, and our speaker is Steven Frost. He's a Postgres committer and works as CTO at Crunchy Data, and he's going to talk about taking Postgres. Thank you.
00:25
Well, thank you all very much. I don't know that that was necessary, but we'll get there. So as mentioned, I'm not going to hit too much on this, but CTO at Crunchy, committer, major contributor, blah, blah, blah. I worked on default roles. I did a lot with row-level security, column-level privileges, and I actually implemented the role system itself way back in 8.3. Maybe did some other stuff.
00:46
So for those of you who want to kind of follow along at home if you're interested, here's my little Git crash course. So if you're wondering where's the Postgres Git repo, it's on git.postql.org. We do have a mirror up on GitHub. I think there's actually a few on GitLab as well for those who want to go there.
01:04
But the base gist of it is that you can pull this down, and then you can start to look at all the wonderful directories and things I'm going to talk about here next. But it's pretty straightforward. When you're hacking with Postgres, we typically do things on feature branches. So you do a git checkout-b, which creates your local branch, and then you hack on Postgres.
01:22
Make changes. Do what you want. And then the way we tend to operate is you add the files you commit, and then you use format patch to actually create a patch that then gets sent up to the hacker's mailing list. And we'll talk a little bit about what that looks like later on in terms of how you register things
01:42
inside of what we call a commit-fest application. So now let's start talking about the actual source tree. So Postgres has a lot of different components. And as we're going through this, feel free to ask questions and let me know if you have questions as we go through.
02:01
I'm happy to take them as we go, and we'll have some time at the end as well. So in our top-level source directory, we have a config system. This is pretty straightforward. We do use autoconf and all the auto tools and what have you for our build system. We also have what's called contrib. So for those of you who were in the prior talk,
02:24
all of the extensions that kind of come with Postgres are included in this contrib directory. So these are all contrib modules. Now, you don't have to use the contrib modules that are in contrib. They're all optional, but they typically are going to be installed when you install Postgres, and they'll be available through just using create extension.
02:44
Some of the great ones are things like pgstat statements, which is really, really handy and is included in contrib. There are also extensions outside of Postgres that are not included with the core Postgres source code that you may be interested in, things like PostGIS, which is a fantastic external extension.
03:03
But if you're looking for how to write an extension, going into contrib and looking at some of the examples is the way to go. What I'm going to be mostly talking about is actually hacking the backend, hacking the grammar, hacking the parser, and we'll start talking about all those details after we kind of go through this rundown of what is in the source tree.
03:23
We then have doc, which is all of our SGML documentation, uses the OpenJade for some versions of Postgres to build system. That's pretty straightforward. If you're wondering how to build the docs, I cheat.
03:41
The Debian maintainer has done a fantastic job and has everything you need to build the docs. If you just apt-get build source, you're good. We then have src-backend. That's what we're going to be working in today. That's where we're going to start talking next. Let's go over a few of the other top-level items, though. You have src-bin, which is where psql, pgdump,
04:02
all of what we consider the client utilities live. These are client-side tools. These are things that are going to be installed with your client package. If you have postql-client installed, that's where all of that comes from. Other things in there that are interesting are initdb and pgupgrade.
04:21
Kind of a little-known secret that maybe not everybody is aware of. pgupgrade uses pgdump in a special mode called binary upgrade. If you're looking at how does pgupgrade work, the gist of it is actually mostly in pgdump inside of this magic binary upgrade system. That's something to be aware of.
04:41
We then have src-common. We actually have started to build out what we're considering a libpg-common thing, which is code that's actually common to both the front end and the back end of Postgres. Unfortunately, today a lot of things like logging are done completely differently in the front end than from the back end.
05:02
We're looking at making changes maybe to unify those interfaces and put both of them eventually into possibly src-common. We then have src-feutil. These are front end capabilities. Certain things that make sense to be in the front end that we don't use in the back end go here.
05:21
If you're wondering about some new thing you're building, should the common code go into feutil or should it go into common? The question is, is it going to be used in the back end? If not, put it into feutil. src-include is pretty straightforward. It's all of the include header files. The one big thing here that people should be aware of is that we have something called src-include-catalog.
05:42
Who here knows what the Postgres catalog is? Fantastic. A few of you. Let me go over it then. The Postgres catalog is that set of tables inside of pg-catalog that define how the rest of the system works. If you're wondering, in Postgres we have all these tables and tables have columns.
06:01
How does Postgres keep track of that? We have tables and we have columns. We actually have a table called pg-attribute. That's one of the pieces of the catalog. The pg-attribute table has a row in it for every column for every table in the system. pg-class is another one of those catalog tables.
06:22
pg-class has a row in it for every table in the system as well as every view and every index. Because there's a lot of things that are common to that, we all consider those classes in Postgres or more traditionally relations in Postgres. If you wanted to, say, add a new column
06:43
into one of the Postgres catalogs, say you're adding some new feature and you need to add some new information that's going to be tracked by the database system, where would you add that column? You add it in src-include-catalog. In fact, when you go and modify src-include-catalog,
07:01
magic happens. You just add the column in, you rerun the build system, and magically, suddenly, when you init db-postgres, you have that new column in this new table. Nothing's happening with it, there's nothing to do with it, but you have it there. Then a bunch of things like pound defines and whatnot, some of them get automatically created for you,
07:22
some of them you'll see inside of those included header files you may want to define yourself for specific values that go into those columns, for example. We then have src-interfaces. src-interfaces is the set of libraries
07:41
or other pieces of code that interface to Postgres directly. These are typically libpq is the big thing here. That's kind of huge. And then there's also ecpg, which I'm not sure how many people here are familiar with the idea of this, but there was a time once when people would write C code with embedded SQL in it
08:03
and run it through a system like ecpg, and that system would actually take all of that and figure out how to make all the connections to the database to run that SQL code and get it back. It made the development of C and SQL code a little bit easier as you're working through writing some new program.
08:22
I would say it's not used very much today. How many people here use ecpg? Exactly. For the record, nobody raised their hand. Maybe I should have. I've played with it a little bit, but that's about it. Then we have procedural languages. srcpl are the core procedural languages.
08:42
You can also install procedural languages as extensions. This is nowhere near the full complete list of all of the procedural languages that are available for Postgres. But the big ones here are here. PLPGSQL. This is similar to PLSQL. It's kind of like T-SQL if you're down for that.
09:02
But it's traditional procedural SQL. That's what PLPGSQL is. But you can also write code in Perl. When I'm talking about procedural languages here, what I'm talking about is the fact that you can write a bit of Perl code, shove it inside of a create function call
09:22
inside of the Postgres database, and then suddenly you have a new function inside the Postgres database that when you call that function, it runs Perl. It loads up the Perl interpreter and runs your Perl code on the database server. That's true for PLPython, PLTCL,
09:41
and then we also have things like PLR. If you are familiar with the R statistical language and you want to run R on your Postgres server, you can use PLR. There's also a PLJava. There's also a PLV8, which is JavaScript. If you want to run JavaScript inside your database server on the server in a backend process
10:01
with direct access to the data, you can do that with Postgres. Very cool stuff. We then have SRC port. That's pretty straightforward. It's just a bunch of platform-specific hacks because Postgres is supported on a stupidly large number of different platforms. We then have a test system.
10:21
The regression test system is actually really straightforward in terms of SRC test. It is write some SQL, run it, track whatever the results were, and shove it into an expected file, and that's your regression test. That's when you're adding new tests to Postgres. That's how it works. We then have some pieces of code
10:40
that we suck in from other places. In particular, SRC time zone comes directly from IANA. IANA actually publishes code for dealing with timestamp garbage, and we import that, and that's where it lives. This is an SRC time zone. We then have things like SRC tools, which includes things like pgindent.
11:01
Of course, Postgres being Postgres, we couldn't use any kind of standard indenting system. We had to reinvent our own. We have pgindent, which is interesting. We've actually got someone hacking the BSD indent to add in features so that our indent doesn't have to exist except as a wrapper around it.
11:21
That part's pretty cool. I forget who was doing that offhand, but it's really neat. Questions on the top-level source directory for Postgres? Did I forget anything? Probably. All right. Let's start talking about the backend code of Postgres.
11:43
These are all of the different directories that exist inside of that SRC backend directory. This is all server-side code, and they all have different components. They do different things. Let's start talking about them. The access directory includes all of our access methods.
12:02
These are methods for the heap. The heap are where all the table's data goes. When you create a table and store data into it, it goes into the heap. When you go and create an index, like a B-tree index, that's the default index, the access method for B-tree indexes and all of that code around B-tree indexes
12:21
lives inside of this access directory. And then Gist and Gin also live there. If you're thinking about creating your own index for Postgres, this is what you want to go look at because what you need to do to implement your own index for Postgres is basically just define a few methods
12:41
and then basically register it with Postgres, and you can do all of this in an extension if you want. Once you've done all of that, Postgres will happily let you create a table with your new index, and off you go. In fact, Gist is in fact another level of generalization around the ability to have alternative indexes in Postgres.
13:03
We then have Bootstrap. This is basically for initdb to kickstart things because we eat our own dog food in a way. When we start up Postgres with initdb, we run Postgres to actually go and create things inside of the data directory. That's part of what initdb does,
13:20
and that Bootstrap mode is what initdb is running. We then have catalog. We talked before about what the catalog was. All the definitional pieces for the catalog are in src include catalog, but all of the code for working with the catalog exists inside of src backend catalog.
13:44
That's how much lag we have. We then have the commands directory. Commands are kind of your top-level DDL commands. Things like create table, alter table, all of that code lives in src backend command.
14:02
If you wanted to add some new feature to say create table, then that would be where you would go look, is in src backend commands, and you would look at the define table command, or define relation actually, function. We then have the executor. Who knows what the executor is?
14:22
All right, a couple people here and there. The executor in Postgres, the way Postgres works is that you have all of your queries come in to something called the traffic cop. We'll talk about that on the next slide. That traffic cop, what it does with that query that you've sent is it first runs the planner. All of the actual planning happens inside of the optimizer down here.
14:44
The query optimizer basically takes in that parsed query after it's been parsed. First, it actually goes through parsing, which is here. It works for once. Most of the time, you're on TVs and it doesn't work. The parser, here we have the lexer and the grammar,
15:02
which is how PC understands the queries you send it. We parse it first. We then send it to the query optimizer, which generates a plan. That plan then gets sent to the executor, and the executor actually runs the plan. Whenever you do an explain on a query, you see all those different nodes.
15:22
The planner created that structure, created all of those nodes. The executor is where all of the code lives to actually execute those nodes, like an append node. For example, there is a file in that directory called node append. That's the code that does that append node.
15:40
That's its job. If you're wondering about hacking on SQL itself, that's where you would go. I'm just going to say this once right now, but people, sit down. The reason I say that is because FOSDEM, unfortunately, will be very upset with us if we have a whole bunch of people standing along the aisles
16:00
because the fire marshal will come and complain at us. As you come in, please, if you need to come across the front to go sit down on this side, please do, that's fine. Let's just get it done. Otherwise, we're going to have a whole bunch of people lined up over there and that's not okay. I don't want FOSDEM to get mad at me. Thank you everyone. If people could shift over maybe a little bit too, that would be great.
16:26
Tell people when they come in. Robert, tell them to come across the front. I don't want them messing with the camera, but we have to have people sitting. In case you're not sure, I'm also one of the bouncers
16:40
you all are going to be dealing with outside if you want to come back into this room after this. That's part of why I'm being particular about it. All right, thank you very much. All right, let's move on. We talked about the executor. Let's talk about foreign data wrappers. Postgres has a system called foreign data wrappers. This allows Postgres, when you execute a query against the database,
17:02
to go out and reach out into another server and get data from it. That includes another Postgres server. We can push down joins. We can push down where clauses. It's great. That also includes things like Twitter and Google. If you want to do a Google search from within your database, you can implement a foreign data wrapper to do that.
17:20
The baseline code for how foreign data wrappers work lives inside of foreign. That's not where the foreign data wrapper itself lives. This is all of the backend code. The foreign data wrapper for, say, Postgres FTW lives inside of contrib. We then have just-in-time compilation for Postgres.
17:43
This is where Postgres can take a query of yours and actually, for some portions of it, build a little program in memory, compile it, optimize it, and run it using LLVM. This is the infrastructure for that. Lib.
18:00
Lib is just a placeholder for a lot of stuff. We dump a bunch of different stuff in Lib for backend code. LibPQ. We talked about LibPQ being under SRC interfaces. Why is it here? This is the backend half. You have a frontend and a backend when you're talking the protocol. The frontend protocol, the thing that talks to the frontend protocol
18:23
is SRC interfaces LibPQ. That's what clients use. When the backend is communicating back and forth, that's from SRC backend LibPQ. Main. Main is where main is. Defines starting Postgres. In fact, when we start Postgres, we often start up a whole bunch of processes with it.
18:43
We start up a checkpoint or a process. We start up auto vacuum. We start up a bunch of other stuff. That's where everything gets kicked off. Nodes. When you see an explain plan, you see those different nodes. We have a generalized node infrastructure for doing that. That all lives inside of nodes.
19:00
We talked a little bit about the optimizer. The optimizer is what's going to actually take your query that you bring in and optimize it for running it. The parser is what actually parses the literal string that comes in. Then we have partitioning. This is native partitioning. Postgres now has something called native or declarative partitioning.
19:21
We used to have it in the inheritance system. Now we have native partitioning. This is where that code lives. PO is just translations. If you're interested in doing translation work, that's where you would go to check out our translation system. Postgres is actually translated into multiple different major languages, which is pretty neat.
19:40
Port is just back into the big platform hacks. The postmaster process. The postmaster process is that process that lives all the time. It's constantly running. Every time Postgres starts up, the postmaster is the thing that actually listens for inbound connections. That's where all the code for that. It answers requests that come in for new connections.
20:04
RegEx is Henry Spencer's RegEx library, which is also used by Tcl. In fact, we've kind of become the de facto upstream for it. The Tcl folks look at us for fixing bugs in it and whatnot. Yes, there are bugs still to be fixed in things like the RegEx library.
20:20
In particular, we had a very interesting case where somebody wrote a tool for basically fuzz testing SQL called SQL Smith. One of the things it found was that there were ways to make our RegEx library go dumb and just chew up lots of CPU. Some of those things have now been fixed and patches released and whatnot,
20:43
and some of that has gotten back into Tcl as well. We then have replication, which just basically handles the replication protocol and shipping wall logs. We have the query rewrite engine. The query rewrite engine is something that kind of happens early on. It actually is before the optimizer.
21:02
I lied a little bit. I skipped a piece. When things come into Postgres, the first thing that happens is it gets parsed. After it goes through the traffic cop, then it gets parsed, then it goes through the role system, then it goes through the optimizer, then it goes through the executor. There's a lot of different pieces here. But the rewrite engine also is what handles row-level security.
21:22
It's important that that happens before optimization because you actually want to have the constraints that are implemented by row-level security be able to be optimized so that we can then more efficiently pull out the rows once we've gotten whatever additional constraints are required by row-level security put in place.
21:42
Snowball stemming is just used for full-text search. We actually pull code from elsewhere for that. We have an extended statistic system. We have a storage layer. Storage layer is basically what handles the actual direct IO into the underlying system. This is things like if you want to go read a page, that's the part of the system that actually goes and reads the actual page
22:02
up into shared buffers. We then have the traffic cop, so that's what actually gets the queries in, and we have tsearch, which is our actual full-text search engine, and then we have utils, which has got a bunch of different stuff in there, including our caching system and memory manager. Questions about the backend code directory stuff?
22:24
I know, I'm going too fast. But that's okay because we're going to slow down a little bit and we're going to start talking about what do you want to change when you want to hack Postgres. This is what I always wonder about first. What do you want to do? You want a new backend command? You want a modified table? I don't know. What does it do?
22:41
Or maybe you want to implement something like merge, which is an actual SQL standard command. Maybe you want to add a new backslash command for psql in case you would go look at srcbin psql, or you want to make improvements to pgbench. That's also an srcbin. If you want to improve performance, come talk to us because it's a lot of work.
23:02
But it's good work. If you want to add a new authentication method, then you're talking about changes to SRC interfaces libpq and SRC backend libpq. If you want to add support for another TLS or SSL encryption library, a lot of that goes into, again, SRC interfaces libpq,
23:22
at least if you're talking about it for the protocol. We're going to talk a little bit about changing an existing backend command. Where do you start depends on what you want to do. When I'm talking about adding a new backend command or modifying one, I want to go hack the grammar because the grammar drives a lot.
23:43
What is the grammar? The grammar is that thing that takes whatever you have written out in text and decides whether it's legit or not, whether it's acceptable. If it is acceptable, what is going to happen? Some of the things you have to worry about when you're hacking the grammar are things like ambiguity.
24:00
You don't want to end up with two identical statements that could be considered in two different ways. Postgres needs to have a grammar that is not ambiguous. Where does the grammar live? It lives inside of the parser. One of the other things about the grammar is that it's one of those things that can be difficult to get agreement on. Not everybody agrees with what you want your grammar to look like.
24:23
Should it be create index concurrently or create concurrently index? These are things that we argue about. Let's talk a little bit about the parser versus the grammar. The parser actually consists of two pieces. We have a lexer which takes all of the individual words
24:40
inside of the statement that you've provided and tokenizes them. The grammar is what actually defines what tokens are allowed to be used with other tokens and in what order. While we're doing all of this parsing, we're collecting up bits of information about the command so we know what you want to do. Then once the full command is parsed,
25:03
a function gets called from the grammar. The parser lives in an SRC backend parser. We talked about that. Scan.L is our lexer. Gram.Y is our definition of the grammar. We have a bunch of parse routines. We have an analyze that transforms the parse tree into an actual query statement or a query structure.
25:22
Then we have support routines for it. That's all pretty straightforward. We're going to modify the grammar now. The grammar is a set of productions. Every production starts with a production name, a colon, and then a list, essentially. That tells us what we can do at this point.
25:43
The very top point of all of this is this statement. Then we have a list of every possible statement that we accept. They're separated by these pipes that are OR commands. They just indicate an OR statement, this or that or that or that or that.
26:02
Copy statement is what we're going to look at here. That's a Beamer mess-up because that should be a pipe. I'm sorry. We then have at the top level for copy,
26:20
we have this copy statement. Then we have all of these other things in here. Things that are capitalized here, those are direct tokens. Those match directly into the lexer. Things that are lowercase are other productions. Here we have things like op binary, qualified name,
26:42
op column list, et cetera. These are actual other productions. Let's look at some of them. Here you can see there's a copy from production that says whether it's copy is from or to. Dollar dollar says assign this node this value. This particular copy from is getting assigned
27:01
either true or false based on whether it's from or to. Then we have op program, which is program. Again, true or false, whether we have program. This is an optional additional keyword to the copy statement, et cetera, et cetera.
27:22
Then we have multi-value productions. Here we have cases where we have, say, this copy generic op list. We have copy generic op element. That is a production itself. When we get to this bit of code, the value from this production is going to be in this dollar one variable.
27:43
Then when we set this equal to dollar dollar, this list makes, we're going to turn this dollar one into a list. Then if you have multiple options here separated by a comma, we have a dollar one and a dollar three. Note that we skip over dollar two.
28:01
Why is that? Because this goes in ordinal. The dollar two is actually the comma itself. We don't need the comma. We're going to go past the comma. We have dollar one, dollar three, and we're going to append to this. This makes it recursive. You can have however many of these you want, and we'll figure out how to make a list out of all those different options for you.
28:26
Is everybody following along with this? This at one, it provides a line number. When you see a position, I should say,
28:40
when you see a post going to say, you screwed up here, that's what that's about. Any other questions about this stuff here? Come on, come on, come on. It's all good. Thank you. I think we actually came through a lot of this.
29:01
This is kind of a summary. We have seed template code in the grammar. It's compiled as part of the overall parser into gram.c. Dollar dollar is this node. Dollar one is blah, blah, blah. We talked about this stuff. Everybody get it? No. Copy options list.
29:20
Here we talked about the production of the different copy options. This is basically making default elements. We're saying format, oids, frees. We're just passing those through. We're making up an options list here for all the different options.
29:42
If we want to add a new copy option, what do we do? The first thing we're going to do is add it into that list of options. This option that I'm adding is going to be called compressed. We're going to add it to this list so that now the grammar will understand it.
30:02
The grammar will understand it and accept it, but other things won't. Come on, come on, come on. The rest of the system isn't going to have any clue about this yet. This is just the very beginning. This is where we start. You want to make sure that you can get the grammar to build correctly because if you start seeing things erroring out from that,
30:23
that usually means you have some kind of ambiguity. You've introduced a case of ambiguity into your parser. If you do that, we're not going to let it go in. That can be either a shift-reduce conflict or a reduce-reduce conflict. I'm not going to go into what those are,
30:40
but if you see those, those are bad. Go fix it or talk to us about what it is. You do also want to avoid adding reserved words if at all possible. When you do need to go add in a new token like this, you want to go add it to the list of unreserved keywords if you can get away with it.
31:00
If it has to be a reserved word, we're going to have talks about it because we've got to have a discussion with people about any new reserved words coming in. We also need to update the actual list of tokens. That's in kwlist.h. It's pretty straightforward. Now that we've got this in here and in the code,
31:22
what are we doing next? The code for copy is in src back in commands copy.c. Copy's got a function to process all of the options that are given. That's pretty convenient. What we're going to do is we're going to go add in a new Boolean into this code to keep track of this.
31:40
This is inside of our structure. We're adding a new item to this .h that's going to be basically is this a compressed file or not. When you are defining a structure inside of a .c file, we do ask that you put it near the top because that's a pretty important thing. We don't have a hard and fast rule about only having them in .h files,
32:01
although typically they're going to go there if they're being used by other parts of the system. Now talking about the code itself. This is pretty straightforward here. We have an if def for have libz, and then we say, okay, if we have it, then if this C state compressed, so the option has come in and said it's compressed,
32:25
then if we already have it set, we're going to say conflicting read on options because you specified it twice. Otherwise, we're going to actually set this compressed flag based on this boolean value that's been passed in. If we don't have libz on the system,
32:40
we're going to throw an error saying we haven't been compiled with libz, sorry. Is that all you have to do? No, there's a lot of stuff. This is the actual diffstat from when I wrote this feature. This included things of having to track gz file. When you're working with gz files,
33:01
you have to track gz file stars instead of file stars. That's a libz thing. Then you have to use gz read and gz write, so I had to go make changes to parts of the system to address that, and that's where that src back in storage file fd.c came into play. Then I had to make documentation updates over in docsource sgml ref copy,
33:24
and then I added some regression tests, which were inside of src tests, so hopefully all these different pieces look familiar. We're going to talk a little bit about what happened with this because this never got committed. We'll talk about that in a minute.
33:41
Postgres has a bunch of different subsystems. We have specific ways of handling memory management, error logging cleanup, linked lists, catalog lookups, nodes, datums, and tuples. Let's talk about that. When it comes to memory management, all memory allocated in the backend is done so inside of a context of some kind. If you want to allocate memory inside of Postgres, it is going to be living throughout the lifetime
34:02
of the backend process that you are currently operating inside of. You would allocate it inside of something called top memory context. If you allocate something inside of top memory context and you don't free it, then it's basically a memory leak. On the other hand, if you allocate inside of current memory context,
34:24
which is typically the correct context for whatever you're doing right now, we will typically clean that up automatically for you because current memory context is going to be something like a per-query context or a per-tuple context. What that means is that Postgres, when it's done with that query
34:41
or it's done working on that tuple, it will throw away the context and anything below it. We clean all of that up for you. Our allocator for all of this is called Palek. That's what you need to be using whenever you're allocating memory. This is also true for extensions.
35:01
If you're writing extensions, you should be using Palek because your code is running in the backend. Errors in a search. In the event that you have something that can't happen, these are situations that just shouldn't happen, you can use ELOG. ELOG always runs, and typically it should not be used
35:24
where a user might see the results, but it can be useful for debugging. Assert is your typical standard Assert system where it's only running in Assert-enabled builds, but do be aware that Assert-enabled builds can therefore act differently from non-Assert builds.
35:41
It's useful to make sure that other hackers are using your function correctly. When other people are writing code, they're typically doing it with Assert-enabled, and it's good to have Assert in there to make sure that the arguments that are getting passed to your function make sense. When you're logging from Postgres, you should be using E-Report,
36:02
and you should be passing in an error code and an error message. We have a style guide for our error messages that you should go make sure that you're familiar with, but the basic gist of it is that one of the important things about E-Report is that when you are doing an E-Report, an error or higher,
36:21
Postgres will just take care of cleaning everything up for you. This is really important because running an E-Report with an error will actually roll back the transaction for you and free all those memory contexts. The way we do this is actually through a long jump. The E-Report system will actually long jump out of that code,
36:43
back up higher up in the stack, handle cleaning up everything, freeing all of your memory contexts and whatnot, and rolling back the transaction itself. Once all of that's done, we're ready to accept new queries. You want to make sure that you're using this appropriately.
37:02
If you do an E-Report with a warning, or a warning notify, anything like that, those lower levels, the code will keep running. It will just issue a warning or a notify back to the user, but the code flow will continue from that point. Does everybody understand how that works?
37:25
Syscache and scanning catalog. We have a function called searchSyscache. We talked about the system catalog a little bit earlier. It's tricky when you want to modify or deal with the system catalog because there can be concurrent things happening. Other people can be making modifications. We have this whole caching system
37:42
to make it so that the system is performant. Whenever you want to go look things up inside of a system catalog, you should be using searchSyscache. That basically is some key that you pass in. If you want to look up a table, you would use searchSyscache, and you would pass in the OID of the relation that you want to look up,
38:04
the OID being the table's OID that you want to go find. Then when you get that back, you'll get back all the information about that table in a cached form that you can then access and work with. When you're done with it, you have to call releaseSyscache to let the system know that you're done with that.
38:21
You're not going to work with it any further. We have some convenience routines around this for lsyscache as well. Just in general, always look for existing routines before implementing a new one because we have lots of pieces of our system. I talked to him about nodes earlier. Expression trees are made up of nodes. Every node is a type plus a bunch of appropriate data.
38:43
The type is stored as the first item inside of the node structure, so you can use izza on it to determine what kind of a node it is. Whenever you create a node, you should be using makeNode, and then you need to make sure that you add into nodes.h and then all of the make, copy, and equality function pieces
39:01
instead of backend nodes. If you add a new node into the system, there's a bunch of stuff that you have to go add, a bunch of boilerplate, essentially. Things for knowing if the two nodes are equal, things for knowing how to copy that node. You can't cover the thing. Thank you.
39:20
It says zero minutes left, and I've got 10. Alright, datum. Inside of Postgres, we have this thing called datums. That's basically a structure for a single value. You can convert these back and forth between C items and Postgres datums, so int32 get datum, or datum get int32, for example.
39:41
Note that datums can be out of line. Postgres will sometimes toast things. If things are big enough, if a single value is large enough, we'll toast it, and that means we'll compress it and we'll store it out of line. You have to be aware of that when you're working with datums. If you try to go access some datum, you need to first check and see, has it been toasted? Do I need to do anything to deal with the fact
40:01
that it's been toasted? So be aware of that. Tuples are then rows, right? Tuples are datums that are all shoved together inside of a row. A heap tuple is defined and include access htup.h. We have a heap tuple data, which is an in-memory construct of a heap tuple, and that includes a length of the tuple
40:22
and a pointer to the actual header, and then we can use these in a lot of different places, so be aware of that. In particular, we have what's called a minimal tuple structure. That's what we use for hashing, right? So if you want to hash something, you typically don't want to necessarily hash the entire row, right? So instead, we have something smaller that allows us to build up just what we need
40:41
in order to put together the hash table. All right. I'm running a bit low on time here, so I'm just going to kind of skip through this. This is how a tuple itself is actually defined. It has a number of attributes, some flags, and then the data follows the header.
41:02
We have a number of other subsystems, so I'm just going to kind of hit on these, but just realize that this is stuff that, like, don't go implement another one of these when you're hacking on Postgres. We have enough of them. For example, we have a linked list implementation. Then we have a doubly and another single linked list implementation. We have a binary heap implementation.
41:22
We have something for solving the knapsack problem, right? We have red-black trees. We have string handling, right? We have all of these things already written inside of the backend code, so don't add another one. Go see if you can't use what's there. When you are hacking the Postgres way, you need to be working with the PGSQL hackers mailing list.
41:43
This is the primary mailing list for discussion about Postgres development, right? You need to get an account at list.postgresql.org in order to subscribe to it, and then you can discuss your ideas and thoughts about how you want to improve Postgres, right? Always watch out for other people working on similar things because that actually happens pretty often.
42:03
Try to think about general answers, not specific ones, right? Be supportive of other ideas and approaches. What happened with this copy compressed feature? Well, somebody wrote a feature called copy program, which will actually send and receive data from a program, and that program could happen to be like gzip or gunzip
42:22
or maybe bzip2 if you wanted to do that. So it's not quite identical, but there ends up being a lot of overlap between what copy compressed could do and what copy program could do. So in the end, we went with using copy program because it was more generalized and allowed you more flexibility in what you wanted to do.
42:43
And it also meant that we didn't have to have direct zlib, like gz file and things like gzread and gzwrite go into the system, and it was nice to be able to avoid that. Whenever you're working with Postgres, think about your code style. Try to make your code fit in.
43:01
pgStyleGuide is in the developer effect. Be aware of any copy and paste. We try to be C99. Actually, we in some ways go back to like C89, but the standard today is more or less C99 compliance with some caveats. We only use C style comments.
43:20
We don't do C++ style comments. Generally, you want to have comments on their own independent line. We're not a big fan of having an inline comment on the same line as code. Always describe why, not how. We can see what the code is doing. We want to know why is the code doing this. That's what really ends up mattering here to us.
43:43
Use a larger comment block for larger code blocks, of course, and in particular, comment your functions and tell people why they should use this function and what the function does. When it comes to the error message style, there's three main parts. There's a primary message, a detail information,
44:01
and then a hint, if appropriate. Don't make any assumptions about the formatting. Use double quotes when quoting and then quotes for file names, user identifiers, and other variables. Try to avoid using passive voice. Postgres does not consider itself to be a human, so use active voice.
44:21
When you want to submit your patch, you should be using context diff or git diff. Ideally, just pick whichever one makes it better. We use multiple patches in one email. Don't multi-email. We're not the Linux kernel. Include in your email two hackers, a description of the patch, regression test, documentation updates.
44:40
PG dump support, in particular, is something that's really key when you're hacking on Postgres. Then register your patch on this thing called commitfest.postgresql.org. This is where we track what patches have been submitted. If your patch is not on here, we probably aren't looking at it. We have a number of upcoming events. These are Postgres community events, plus FOSSASIA, which I'll be at.
45:01
If you're interested, check these out. There's a number of them that are even relatively local. I'll be at scale. I'll be at FOSSASIA. I'll be at PGCONS APAC, and I'll be at PGCON. When it comes to the big annual Postgres hacker meeting thing, that's at PGCON. That's in Ottawa, Canada. All right, questions?
45:34
No questions? Fantastic. Thank you all. You guys have been a fantastic audience. Thank you.
45:41
Exit out this way. So if you are leaving the room, please use this door. Yeah, exit this way.