Last Wednesday, March 26, we hosted the second of five virtual Extension Mini-Summits in the lead up to the big one at the Postgres Development Conference (PGConf.dev) on May 13 in Montreal, Canada. Peter Eisentraut gave a very nice presentation on the history, design decisions, and problems solved by “Implementing an Extension Search Path”. That talk, plus another 10-15m of discussion, is now available for your viewing pleasure:

• Video
• Slides

If you’d like to attend any of the next three Mini-Summits, join the Meetup!

Once again, with many thanks again to Floor Drees for the effort, here’s the transcript from the session.

Introduction

Floor Drees introduced the organizers:

• David Wheeler, Principal Architect at Tembo, maintainer of PGXN
• Yurii Rashkovskii, Omnigres
• Keith Fiske, Crunchy Data
• Floor Drees, Principal Program Manager at EDB, PostgreSQL CoCC member, PGDay Lowlands organizer

Peter Eisentraut, contributor to PostgreSQL development since 1999, talked about implementing an extension search path.

The stream and the closed captions available for the recording are supported by PGConf.dev and their gold level sponsors, Google, AWS, Huawei, Microsoft, and EDB.

Implementing an extension search path

Peter: Thank you for having me!

I’m gonna talk about a current project by me and a couple of people I have worked with, and that will hopefully ship with Postgres 18 in a few months.

So, what do I know about extensions? I’m a Postgres core developer, but I’ve developed a few extensions in my time, here’s a list of extensions that I’ve built over the years.

• plsh
• pguint
• pgpcre
• pguri
• plxslt
• pgemailaddr
• pgtrashcan

Some of those are experiments, or sort of one-offs. Some of those are actually used in production.

I’ve also contributed to well-known extensions: orafce; and back in the day, pglogical, BDR, and pg_failover_slots, at EDB, and previously 2ndQuadrant. Those are obviously used widely and in important production environments.

I also wrote an extension installation manager called pex at one point. The point of pex was to do it in one shell script, so you don’t have any dependencies. It’s just a shell script, and you can say pex install orafce and it installs it. This was a proof of concept, in a sense, but was actually quite useful sometimes for development, when you just need an extension and you don’t know where to get it.

And then I wrote, even more experimental, a follow-on project called autopex, which is a plugin module that you load into Postgres that automatically installs an extension if you need it. If you call CREATE EXTENSION orafce, for example, and you don’t have it installed, autopex downloads and installs it. Obviously highly insecure and dubious in terms of modern software distribution practice, but it does work: you can just run CREATE EXTENSION, and it just installs it if you don’t have it. That kind of works.

So anyways, so I’ve worked on these various aspects of these over time. If you’re interested in any of these projects, they’re all under my GitHub account.

In the context of this presentation…this was essentially not my idea. People came to me and asked me to work on this, and as it worked out, multiple people came to me with their problems or questions, and then it turned out it was all the same question. These are the problems I was approached about.

The first one is extension management in the Kubernetes environment. we’ll hear about this in a future talk in this series. Gabriele Bartolini from the CloudNativePG project approached me and said that the issue in a Kubernetes environment is that if you launch a Postgres service, you don’t install packages, you have a pre-baked disk image that contains the software that you need. There’s a Postgres server and maybe some backup software in that image, and if you want to install an extension, and the extension is not in that image, you need to rebuild the image with the extension. That’s very inconvenient.

The ideal scenario would be that you have additional disk images for the extensions and you just somehow attach them. I’m hand waving through the Kubernetes terminology, and again, there will be a presentation about that in more detail. But I think the idea is clear: you want to have these immutable disk images that contain your pieces of software, and if you want to install more of them, you just wanna have these disk images augment ’em together, and that doesn’t work at the moment.

Problem number two is: I was approached by a maintainer of the Postgres.app project, a Mac binary distribution for Postgres. It’s a nice, user-friendly binary distribution for Postgres. This is sort of a similar problem: on macOS you have these .app files to distribute software. They’re this sort of weird hybrid between a zip file with files in it and a directory you can look into, so it’s kind of weird. But it’s basically an archive with software in it. And in this case it has Postgres in it and it integrates nicely into your system. But again, if you want to install an extension, that doesn’t work as easily, because you would need to open up that archive and stick the extension in there somehow, or overwrite files.

And there’s also a tie in with the way these packages are signed by Apple, and if you, mess with the files in the package, then the signature becomes invalid. It’s the way it’s been explained to me. I hope this was approximately accurate, but you already get the idea, right? There’s the same problem where you have this base bundle of software that is immutable or that you want to keep immutable and you want to add things to it, which doesn’t work.

And then the third problem I was asked to solve came from the Debian package maintainer, who will also speak later in this presentation series. What he wanted to do was to run the tests of an extension while the package is being built. That makes sense. You wanna run the tests of the software that you’re building the package for in general. But in order to do that, you have to install the extension into the the normal file system location, right? That seems bad. You don’t want to install the software while you’re into the main system while you’re building it. He actually wrote a custom patch to be able to do that, which then my work was inspired by.

Those are the problems I was approached about.

I had some problems I wanted to solve myself based on my experience working with extensions. While I was working on these various extensions over the years, one thing that never worked is that you could never run make check. It wasn’t supported by the PGXS build system. Again, it’s the same issue.

It’s essentially a subset of the Debian problem: you want to run a test of the software before you install it, but Postgres can only load an extension from a fixed location, and so this doesn’t work. It’s very annoying because it makes the software development cycle much more complicated. You always have to then, then run make all, make install, make sure you have a server running, make installcheck. And then you would want to test it against various different server versions. Usually they have to run this in some weird loop. I’ve written custom scripts and stuff all around this, but it’s was never satisfactory. It should just work.

That’s the problem I definitely wanted to solve. The next problem — and these are are all subsets of each other — that if you have Postgres installed from a package, like an RPM package for example, and then you build the extension locally, you have to install the extension into the directory locations that are controlled by your operating system. If you have Postgres under /usr, then the extensions also have to be installed under /usr, whereas you probably want to install them under /usr/local or somewhere else. You want to keep those locally built things separately, but that’s not possible.

And finally — this is a bit more complicated to explain — I’m mainly using macOS at the moment, and the Homebrew package manager is widely used there. But it doesn’t support extensions very well at all. It’s really weird because the way it works is that each package is essentially installed into a separate subdirectory, and then it’s all symlinked together. And that works just fine. You have a bunch of bin directories, and it’s just a bunch of symlinks to different subdirectories and that works, because then you can just swap these things out and upgrade packages quite easily. That’s just a design choice and it’s fine.

But again, if you wanna install an extension, the extension would be its own package — PostGIS, for example — and it would go into its own directory. But that’s not the directory where Postgres would look for it. You would have to install it into the directory structure that belongs to the other package. And that just doesn’t work. It’s just does not fit with that system at all. There are weird hacks at the moment, but it’s not satisfactory. Doesn’t work at all.

It turned out, all of these things have sort of came up over the years and some of these, people have approached me about them, and I realized these are essentially all the same problem. The extension file location is hard-coded to be inside the Postgres installation tree. Here as an example: it’s usually under something like /usr/share/postgresql/extension/, and you can’t install extensions anywhere else. If you want to keep this location managed by the operating system or managed by your package management or in some kind of immutable disk image, you can’t. And so these are essentially all versions of the same problem. So that’s why I got engaged and tried to find a solution that addresses all of ’em.

I had worked on this already before, a long time ago, and then someone broke it along the way. And now I’m fixing it again. If you go way, way back, before extensions as such existed in Postgres in 9.1, when you wanted to install a piece of software that consists of a shared library object and some SQL, you had to install the shared library object into a predetermined location just like you do now. In addition, you had to run that SQL file by hand, basically, like you run psql -f install_orafce.sql or something like that. Extensions made that a little nicer, but it’s the same idea underneath.

In 2001, I realized this problem already and implemented a configuration setting called dynamic_library_path, which allows you to set a different location for your shared library. Then you can say

dynamic_library_path = '/usr/local/my-stuff/something'

And then Postgres would look there. The SQL file just knows where is because you run it manually. You would then run

psql -f /usr/local/my-stuff/something/something.sql

That fixed that problem at the time. And when extensions were implemented, I was essentially not paying attention or, you know, nobody was paying attention. Extension support were a really super nice feature, of course, but it broke this previously-available feature: then you couldn’t install your extensions anywhere you wanted to; you were tied to this specific file system, location, dynamic_library_path still existed: you could still set it somewhere, but you couldn’t really make much use of it. I mean, you could make use of it for things that are not extensions. If you have some kind of plugin module or modules that install hooks, you could still do that. But not for an extension that consist of a set of SQL scripts and a control file and dynamic_library_path.

As I was being approached about these things, I realized that was just the problem and we should just now fix that. The recent history went as follows.

In April, 2024, just about a year ago now, David Wheeler started a hackers thread suggesting Christoph Berg’s Debian patch as a starting point for discussions. Like, “here’s this thing, shouldn’t we do something about this?”

There was, a fair amount of discussion. I was not really involved at the time. This was just after feature freeze,and so I wasn’t paying much attention to it. But the discussion was quite lively and a lot of people pitched in and had their ideas and thoughts about it. And so a lot of important, filtering work was done at that time.

Later, in September, Gabriele, my colleague from EDB who works on CloudNativePG, approached me about this issue and said like: “hey, this is important, we need this to make extensions useful in the Kubernetes environment.” And he said, “can you work, can you work on this?”

I said, “yeah, sure, in a couple months I might have time.” [Laughs]. But it sort of turns out that, at PGConf.EU we had a big brain trust meeting of various people who basically all came and said, “hey, I heard you’re working on extension_control_path, I also need that!”

Gabriele was there, and Tobias Bussmann from Postgres.app was there ,and Christoph, and I was like, yeah, I really need this extension_control_path to make this work. So I made sure to talk to everybody there and, and make sure that, if we did this, would it work for you? And then we kind of had a good idea of how it should work.

In November the first patch was posted and last week it was committed. I think there’s still a little bit of discussion of some details and, we certainly still have some time before the release to fine tune it, but the main work is hopefully done.

This is the commit I made last week. The fact that this presentation was scheduled gave me additional motivation to get it done. I wanna give some credits to people who reviewed it. Obviously David did a lot of reviews and feedback in general. My colleague Matheus, who I think I saw him earlier, he was also here on the call, did help me quite a bit with sort of finishing the patch. And then Gabriele, Marco and Nicolò, who work on CloudNativePG, did a large amount of testing.

They set up a whole sort of sandbox environment making test images for extensions and, simulating the entire process of attaching these to the main image. Again, I’m butchering the terminology, but I’m just trying to explain it in general terms. They did the whole end-to-end testing of what that would then look like with CloudNativePG. And again, that will, I assume, be discussed when Gabriele presents in a few weeks.

These are the stats from the patch

commit 4f7f7b03758

doc/src/sgml/config.sgml                                     |  68 +++++
doc/src/sgml/extend.sgml                                     |  19 +-
doc/src/sgml/ref/create_extension.sgml                       |   6 +-
src/Makefile.global.in                                       |  19 +-
src/backend/commands/extension.c                             | 403 +++++++++++++++++----------
src/backend/utils/fmgr/dfmgr.c                               |  77 +++--
src/backend/utils/misc/guc_tables.c                          |  13 +
src/backend/utils/misc/postgresql.conf.sample                |   1 +
src/include/commands/extension.h                             |   2 +
src/include/fmgr.h                                           |   3 +
src/test/modules/test_extensions/Makefile                    |   1 +
src/test/modules/test_extensions/meson.build                 |   5 +
.../modules/test_extensions/t/001_extension_control_path.pl  |  80 ++++++

the reason I show this is that, it’s not big! What I did is use the same infrastructure and mechanisms that already existed for the dynamic_library_path. That’s the code in that’s in dfmgr there in the middle. That’s where this little path search is implemented9. And then of course, in extension..c there’s some code that’s basically just a bunch of utility functions, like to list all the extensions and list all the versions of all the extensions. Those utility functions exist and they needed to be updated to do the path search. Everything else is pretty straightforward. There’s just a few configuration settings added to the documentation and the sample files and so on. It’s not that much really.

One thing we also did was add tests for this, Down there in test_extensions. We wrote some tests to make sure this works. Well, it’s one thing to make sure it works, but the other thing is if we wanna make changes or we find problems with it, or we wanna develop this further in the future, we have a record of how it works, which is why you write tests. I just wanted to point that out because we didn’t really have that before and it was quite helpful to build confidence that we know how this works.

So how does it work? Let’s say you have your Postgres installation in a standard Linux file system package controlled location. None of the actual packages look like this, I believe, but it’s a good example. You have your stuff under the /usr/bin/, you have the shared libraries in the /usr/lib/something, you have the extension control files and SQL files in the /usr/share/ or something. That’s your base installation. And then you wanna install your extension into some other place to keep these things separate. So you have /usr/local/mystuff/, for example.

Another thing that this patch implemented is that you can now also do this: when you build an extension, you can write make install prefix=something. Before you couldn’t do that, but there was also no point because if you installed it somewhere else, you couldn’t do anything with it there. Now you can load it from somewhere else, but you can also install it there — which obviously are the two important sides of that.

And then you set these two settings: dynamic_library_path is an existing configuration setting, yYou set that to where your lib directory is, and then the extension_control_path is a new setting. The titular setting of this talk, where you tell it where your extension control files are.

There’s these placeholders, $libdir and $system which mean the system location, and then the other locations are your other locations, and it’s separated by colon (and semi-colon on Windows). We had some arguments about what exactly the extension_control_path placeholder should be called and, people continue to have different opinions. What it does is it looks in the list directories for the control file, and then where it finds the control file from there, it loads all the other files.

And there’s a fairly complicated mechanism. There’s obviously the actual SQL files, but there’s also these auxiliary control files, which I didn’t even know that existed. So you can have version specific control files. It’s a fairly complicated system, so we wanted to be clear that what is happening is the, the main control file is searched for in these directories, and then wherever it’s found, that’s where it looks for the other things. You can’t have the control file in one path and then the SQL files in another part of the path; that’s not how it works.

That solves problem number five. Let’s see what problem number five was. I forgot [Chuckles]. This is the basic problem, that you no longer have to install the extensions in the directories that are ostensibly controlled by the operating system or your package manager.

So then how would Debian packaging use this? I got this information from Christoph. He figured out how to do this. He just said, “Oh, I did this, and that’s how it works.” During packaging, the packaging scripts that built it up in packages that you just pass these:

PKGARGS="--pgoption extension_control_path=$PWD/debian/$PACKAGE/usr/share/postgresql/$v/extension:\$system
--pgoption dynamic_library_path=$PWD/debian/$PACKAGE/usr/lib/postgresql/$v/lib:/usr/lib/postgresql/$v/lib"

These options set the control path and the dynamic_library_path and these versions and then it works. This was confirmed that this addresses his problem. He no longer has to carry his custom patch. This solves problem number three.

The question people ask is, “why do we have two?” Or maybe you’ve asked yourself that. Why do we need two settings. We have the dynamic_library_path, we have the extension_control_path. Isn’t that kind of the same thing? Kind of, yes! But in general, it is not guaranteed that these two things are in a in a fixed relative location.

Let’s go back to our fake example. We have the libraries in /usr/lib/postgresql and the SQL and control files in /usr/share/postgresql, for example. Now you could say, why don’t we just set it to /usr? Or, for example, why don’t we just set the path to /usr/local/mystuff and it should figure out the sub directories. That would be nice, but it doesn’t quite work in general because it’s not guaranteed that those are the subdirectories. There could be, for example. lib64, for example, right? Or some other so architecture-specific subdirectory names. Or people can just name them whatever they want. So, this may be marginal, but it is possible. You need to keep in mind that the subdirectory structure is not necessarily fixed.

So we need two settings. The way I thought about this, if you compile C code, you also have two settings. And if you think about it, it’s exactly the same thing. When you compile C code, you always have to do -I and -L: I for the include files, L for the lib files. This is basically the same thing. The include file is also the text file that describes the interfaces and the libraries are the libraries. Again, you need two options, because you can’t just tell the compiler, oh, look for it in /usr/local because the subdirectories could be different. There could be architecture specific lib directories. That’s a common case. You need those two settings. Usually they go in parallel. If somebody has a plan on how to do it simpler, follow up patches are welcome.

But the main point of why this approach was taken is also to get it done in a few months. I started thinking about this, or I was contacted about this in September and I started thinking about it seriously in the October/November timeframe. That’s quite late in the development cycle to start a feature like this, which I thought would be more controversial! People haven’t really complained that this breaks the security of extensions or anything like that. I was a little bit afraid of that.

So I wanted to really base it on an existing facility that we already had, and that’s why I wanted to make sure it works exactly in parallel to the other path that we already have, and that has existed for a long time, and was designed for this exact purpose. That was also the reason why we chose this path of least resistance, perhaps.

This is the solution progress for the six problems that I described initially. The CloudNativePG folks obviously have accompanied this project actively and have already prototyped the integration solution. And, and presumably we will hear about some of that at the meeting on May 7th, where Gabriele will talk about this.

Postgres.app I haven’t been in touch with, but one of the maintainers is here, maybe you can give feedback later. Debian is done as I described, and they will also be at the next meeting, maybe there will be some comment on that.

One thing that’s not fully implemented is the the make check issue. I did send a follow-up patch about that, which was a really quick prototype hack, and people really liked it. I’m slightly tempted to give it a push and try to get it into Postgres 18. This is a work in progress, but it’s, there’s sort of a way forward. The local install problem I said is done.

Homebrew, I haven’t looked into. It’s more complicated, and I’m also not very closely involved in the development of that. I’ll just be an outsider maybe sending patches or suggestions at some point, maybe when the release is closer and, and we’ve settled everything.

I have some random other thoughts here. I’m not actively working on these right now, but I have worked on it in the past and I plan to work on it again. Basically the conversion of all the building to Meson is on my mind, and other people’s mind.

Right now we have two build systems: the make build system and the Meson build system, and all the production packages, as far as I know, are built with make. Eventually we wanna move all of that over to Meson, but we want to test all the extensions and if it still works. As far as I know, it does work; there’s nothing that really needs to be implemented, but we need to go through all the extensions and test them.

Secondly — this is optional; I’m not saying this is a requirement — but you may wish to also build your own extensions with Meson. But that’s in my mind, not a requirement. You can also use cmake or do whatever you want. But there’s been some prototypes of that. Solutions exist if you’re interested.

And to facilitate the second point, there’s been the proposal — which I think was well received, but it just needs to be fully implemented — to provide a pkg-config file to build against the server, and cmake and Meson would work very well with that. Then you can just say here’s a pkg-config file to build against the server. It’s much easier than setting all the directories yourself or extracting them from pg_config. Maybe that’s something coming for the next release cycle.

That’s what I had. So extension_control_path is coming in Postgres 18. What you can do is test and validate that against your use cases and and help integration into the downstream users. Again, if you’re sort of a package or anything like that, you know, you can make use of that. That is all for me.

Thank you!

Questions, comments

• Reading the comments where several audience members suggested Peter follows Conference Driven Development he confirmed that that’s definitely a thing.

• Someone asked for the “requirements gathering document”. Peter said that that’s just a big word for “just some notes I have”. “It’s not like an actual document. I called it the requirements gathering. That sounds very formal, but it’s just chatting to various people and someone at the next table overheard us talking and it’s like, ‘Hey! I need that too!’”

• Christoph: I tried to get this fixed or implemented or something at least once over the last 10 something-ish years, and was basically shot down on grounds of security issues if people mess up their system. And what happens if you set the extension path to something, install an extension, and then set the path to something else and then you can’t upgrade. And all sorts of weird things that people can do with their system in order to break them. Thanks for ignoring all that bullshit and just getting it done! It’s an administrator-level setting and people can do whatever they want with it.

  So what I then did is just to implement that patch and, admittedly I never got around to even try to put it upstream. So thanks David for pushing that ahead. It was clear that the Debian version of the patch wasn’t acceptable because it was too limited. It made some assumptions about the direct restructure of Debian packages. So it always included the prefix in the path. The feature that Peter implemented solves my problem. It does solve a lot of more problems, so thanks for that.

• Peter: Testing all extensions. What we’ve talked about is doing this through the Debian packaging system because the idea was to maybe make a separate branch or a separate sub-repository of some sort, switch it to build Meson, and rebuild all the extension packages and see what happens. I guess that’s how far we’ve come. I doesn’t actually mean they all work, but I guess that most of them has tests, so we just wanted to test, see if it works.

  There are some really subtle problems. Well, the ones I know of have been fixed, but there’s some things that certain compilation options are not substituted into the Makefiles correctly, so then all your extensions are built without any optimizations, for example, without any -O options. I’m not really sure how to detect those automatically, but at least, just rebuild everything once might be an option. Or just do it manually. There are not thousands of extensions. There are not even hundreds that are relevant. There are several dozens, and I think that’s good coverage.

• Christoph: I realize that doing it on the packaging side makes sense because we all have these tests running. So I was looking into it. The first time I tried, I stopped once I realized that Meson doesn’t support LLVM yet; and the second time I tried, I just diff-ed the generated Makefiles to see if there’s any difference that looks suspicious. At thus point I should just continue and do compilation run and see what the tests are doing and and stuff.

  So my hope would be that I could run diff on the results; the problem is compiling with Postgres with Autoconf once and then with Meson the second time, then see if it has an impact on the extensions compiled. But my idea was that if I’m just running diff on the two compilations and there’s no difference, there’s no point in testing because they’re identical anyway.

• Peter Oooh, you want the actual compilation, for the Makefile output to be the same.

• Christoph: Yeah. I don’t have to run that test, But the diff was a bit too big to be readable. There was lots of white space noise in there. But there were also some actual changes. Some were not really bad, like9 in some points variables were using a fully qualified path for the make directory or something, and then some points not; but, maybe we can just work on making that difference smaller and then arguing about correctness is easier.

• Peter: Yeah, that sounds like a good approach.

• Jakob: Maybe I can give some feedback from Postgres.app. So, thank you very much. I think this solves a lot of problems that we have had with extensions over the years, especially because it allows us to separate the extensions and the main Postgres distribution. For Postgres.app we basically have to decide which extensions to include and we can’t offer additional extensions when people ask for them without shipping them for everyone. So that’s a big win.

  One question I am wondering about is the use case of people building their own extensions. As far as I understand, you have to provide the prefix/ And one thing I’m wondering whether there is there some way to give a default value for the prefix. Like in pg_config or in something like that, so people who just type make install automatically get some path.

• Peter: That might be an interesting follow on. I’m making a note of it. I’m not sure how you’d…

• Jakob: I’m just thinking because a big problem is that a lot of people who try things don’t follow the instructions for the specific Postgres. So for example, if we write documentation how to build extensions and people on a completely different system — like people Google stuff and they get instruction — they’ll just try random paths. Right now, if you just type make install, it works on most systems because it just builds into the standard directories.

• Peter: Yeah, David puts it like, “should there be a different default extension location?” I think that’s probably not an unreasonable direction. I think that’s something we should maybe think about, once this is stabilized. I think for your Postgres.app use case, it, I think you could probably even implement that yourself with a one or two line patch so that at least, if you install Postgres.app, then somebody tries to build an extension, they get a reasonable location.

• David: If I could jump in there, Jakob, my assumption was that Postgres.app would do something like designate the Application Support directory and Preferences in ~/Library as where extensions should be installed. And yeah, there could be some patch to PGXS to put stuff there by default.

• Jakob: Yeah, that would be nice!

• Peter: Robert asked a big question here. What do we think the security consequences of this patch? Well, one of the premises is that we already have dynamic_library_path, which works exactly the same way, and there haven’t been any concerns about that. Well, maybe there have been concerns, but nothing that was acted on. If you set the path to somewhere where anybody can write stuff, then yeah, that’s not so good. But that’s the same as anything. Certainly there were concerns as I read through the discussion.

  I assumed somebody would hav security questions, so I really wanted to base it on this existing mechanism and not invent something completely new. So far nobody has objected to it [Chuckles]. But yeah, of course you can make a mess of it if you go into that extension_control_path = /tmp! That’s probably not good. But don’t do that.

• David: That’s I think in part the xz exploit kind of made people more receptive to this patch because we want to reduce the number of patches that packaging maintainers have to maintain.

• Peter: Obviously this is something people do. Better we have one solution that people then can use and that we at least we understand, as opposed to everybody going out and figuring out their own complicated solutions.

• David: Peter, I think there are still some issues with the behavior of MODULEDIR from PGXS and directory in the control file that this doesn’t quite work with this extension. Do you have some thoughts on how to address those issues?

• Peter: For those who are not following: there’s an existing, I guess, rarely used feature that, in the control file, you can specify directory options, which then specifies where other files are located. And this doesn’t work the way you think it should maybe it’s not clear what that should do if you find it in a path somewhere. I guess it’s so rarely used that we might maybe just get rid of it; that was one of the options.

  In my mental model of how the C compiler works, it sets an rpath on something. If you set an absolute rpath somewhere and you know it’s not gonna work if you move the thing to a different place in the path. I’m not sure if that’s a good analogy, but it sort of has similar consequences. If you hard-code absolute path, then path search is not gonna work. But yeah, that’s on the list I need to look into.

• David: For what it’s worth, I discovered last week that the part of this patch where you’re stripping out $libdir and the extension make file that was in modules, I think? That also needs to be done when you use rpath to install an extension and point to extensions today with Postgres 17. Happy to see that one go.

• Christoph: Thanks for fixing that part. I was always wondering why this was broken. The way it was broken. It looked very weird and it turned out it was just broken and not me not understanding it.

• David: I think it might have been a documentation oversight back when extensions were added at 9.1 to say this is how you list the modules.

  Anyway, this is great! Im super excited for this patch and where it’s going and the promise for stuff in the future. Just from your list of the six issues it addresses, it’s obviously something that covers a variety of pain points. I appreciate you doing that.

• Peter: Thank you!

Many thanks and congratulations wrap up this call.

The next Mini-Summit is on April 9, Christoph Berg (Debian, and also Cybertec) will join us to talk about Apt Extension Packaging.

This Wednesday, March 26 at noon America/New_York (16:00 UTC), Peter Eisentraut has graciously agreed to give a talk at the Extension Mini Summit #2 on the extension search path patch he recently committed to PostgreSQL. I’m personally stoked for this topic, as freeing extensions from the legacy of a single directory opens up a number of new patterns for packaging, installation, and testing extensions. Hit the Meetup to register for this live video conference, and to brainstorm novel uses for this new feature, expected to debut in PostgreSQL 18.

Back on March 12, we hosted the first in a series of PostgreSQL Extensions Mini Summits leading up to the Extension Ecosystem Summit at PGConf.dev on May 13. I once again inaugurated the series with a short talk on the State of the Extension Ecosystem. The talk was followed by 15 minutes or so of discussion. Here are the relevant links:

• Video
• OCI Demo
• Slides

And now, with many thanks to Floor Drees for the effort, the transcript from the session.

Introduction

Floor Drees introduced the organizers:

• David Wheeler, Principal Architect at Tembo, maintainer of PGXN
• Yurii Rashkovskii, Omnigres
• Keith Fiske, Crunchy Data
• Floor Drees, Principal Program Manager at EDB, PostgreSQL CoCC member, PGDay Lowlands organizer

David presented a State of the Extension Ecosystem at this first event, and shared some updates from PGXN land.

The stream and the closed captions available for the recording are supported by PGConf.dev and their gold level sponsors, Google, AWS, Huawei, Microsoft, and EDB.

State of the Extensions Ecosystem

So I wanted to give a brief update on the state of the Postgres extension ecosystem, the past, present, and future. Let’s give a brie history; it’s quite long, actually.

There were originally two approaches back in the day. You could use shared preload libraries to have it preload dynamic shareable libraries into the main process. And then you could do pure SQL stuff using, including procedural languages like PL/Perl, PL/Tcl, and such.

And there were a few intrepid early adopters, including PostGIS, BioPostgres, PL/R, PL/Proxy, and pgTAP, who all made it work. Beginning of Postgres 9.1 Dimitri Fontaine added support for explicit support for extensions in the Postgres core itself. The key features included the ability to compile and install extensions. This is again, pure SQL and shared libraries.

There are CREATE, UPDATE, and DROP EXTENSION commands in SQL that you can use to add extensions to a database, upgrade them to new versions and to remove them. And then pg_dump and pg_restore support so that extensions could be considered a single bundle to be backed up and restored with all of their individual objects being included as part of the backup.

Back then, a number of us, myself included, saw this as an opportunity to have the extensibility of Postgres itself be a fundamental part of the community and distribution. I was a long time user of Perl and used CPAN, and I thought we had something like CPAN for Postgres. So, I proposed PGXN, the PostgreSQL Extension Network, back in 2010. The idea was to do distribution of source code. You would register namespaces for your extensions.

There was discovery via a website for search, documentation published, tags to help you find different kinds of objects, and to support installation through a command line interface. The compile and install stuff that Postgres itself provides, using PGXS and Configure.

This is what PGXN looks like today. It was launched in 2011. There’s a command line client, this website, an API an a registry you can upload your extensions to. The most recent one was pg_task a day or so ago.

In the interim, since that came out in 2011/2012, the cloud providers have come into their own with Postgres, but their support for extensions tends to be rather limited. For non-core extension counts, as of yesterday, Azure provides 38 extensions, GCP provides 44 extensions, and AWS 51. These are the third party extensions that don’t come with Postgres and its contrib itself. Meanwhile, PGXN has 420 extensions available to download, compile, build, and install.

A GitHub project that tracks random extensions on the internet, (joelonsql/PostgreSQL-EXTENSIONs.md), which is pretty comprehensive, has almost 1200 extensions listed. So the question is why is the support not more broad? Why aren’t there a thousand extensions available in every one of these systems?

Rthis has been a fairly common question that’s come up in the last couple years. A number of new projects have tired to fill in the gaps. One is Trusted Language Extensions. They wanted to make it easier to distribute extensions without needing dynamic shared libraries by adding additional features in the database itself.

The idea was to empower app developers to make it easy to install extensions via SQL functions rather than having to access the file system of the database server system itself. It can be portable, so there’s no compilation required, it hooks into the create extension command transparently, supports custom data types, and there have been plans for foreign data wrappers and background workers. I’m not sure how that’s progressed in the past year. The pg_tle extension itself was created by AWS and Supabase.

Another recent entrant in tooling for extensions is pgrx, which is native Rust extensions in Postgres. You build dynamic shared libraries, but write them in pure Rust. The API for pgrx provides full access to Postgres features, and still provides the developer-friendly tooling that Rust developers are used to. There’s been a lot of community excitement the last couple of years around pgrx, and it remains under active development — version 0.13.0 just came out a week or so ago. It’s sponsored and run out of the PgCentral Foundation.

There have also been a several new registries that have come up to try to fill the gap and make extensions available. They have emphasized different things than PGXN. One was ease of use. So, for example, here pgxman says it should be really easy to install a client in a single command, and then it installs something, and then it downloads and installs a binary version of your an extension.

And then there was platform neutrality. They wanted to do binary distribution and support multiple different platform, to know what binary∑ to install for a given platform. They provide stats. PGXN doesn’t provide any stats, but some of them are list stats like how many downloads we had, how many in the last 180 days.

And curation. Trunk is another binary extension registry, from my employer, Tembo. They do categorization of all the extensions on Trunk, which is at 237 now. Quite a few people have come forward to tells us that they don’t necessarily use Trunk to install extensions, but use them to find them, because the categories are really helpful for people to figure out what sorts of things are even available, and an option to use.

So here’s the State of the Ecosystem as I see it today.

• There have been some lost opportunities from the initial excitement around 2010. Extensions remain difficult to find and discover. Some are on PGXN, some are on GitHub, some are on Trunk, some are on GitLab, etc. There’s no like one place to go to find them all.

• They remain under-documented and difficult to understand. It takes effort for developers to write documentation for their extensions, and a lot of them aren’t able to. Some of them do write the documentation, but they might be in a format that something like PGXN doesn’t understand.

• The maturity of extensions can be difficult to gauge. If you look at that list of 1200 extensions on GitHub, which ones are the good ones? Which ones do people care about? That page in particular show the number of stars for each extension, but that the only metric.

• They’re difficult to configure and install. This is something TLE really tried to solve, but the uptake on TLE has not been great so far, and it doesn’t support all the use cases. There are a lot of use cases that need to be able to access the internal APIs of Postgres itself, which means compiling stuff into shared libraries, and writing them in C or Rust or a couple of other compiled languages.

  That makes them difficult to configure. You have ask questions lik: Which build system do I use? Do I install the tooling? How do I install it and configure it? What dependencies does it have? Et cetera.

• There’s no comprehensive binary packaging. The Postgres community’s own packaging systems for Linux — Apt, and YUM — do a remarkably good job of packaging extensions. They probably have more extensions packaged for those platforms than any of the others. If they have the extension you need and you’re using the PGDG repositories, then this stuff is there. But even those are still like a fraction of all the potential available extensions that are out there.

• Dependency management can be pretty painful. It’s difficult to know what you need to install. I was messing around yesterday with the PgSQL HTTP extension, which is a great extension that depends on libcurl. I thought maybe I could build a package that includes libcurl as part of it. But then I realized that libcurl depends on other packages, other dynamic libraries. So I’d have to figure out what all those are to get them all together.

  A lot of that goes away if you use a system like apt or yum. But if you, if you don’t, or you just want to install stuff on your Mac or Windows, it’s much more difficult.

• Centralized source distribution, we’ve found found, is insufficient. Even if all the extensions were available on PGXN, not everybody has the wherewithal or the expertise to find what they need, download it, compile it, and build it. Moreover, you don’t want to have a compiler on your production system, so you don’t want to be building stuff from source on your production system. So then you have to get to the business of building your own packages, which is a whole thing.

But in this state of the extension ecosystem we see new opportunities too. One I’ve been working on for the past year, which we call “PGXN v2”, is made possible by my employer, Tembo. The idea was to consider the emerging patterns — new registries and new ways of building and releasing and developing extensions — and to figure out the deficiencies, and to engage deeply with the community to work up potential solutions, and to design and implement a new architecture. The idea is to serve the community for the next decade really make a PGXN and its infrastructure the source of record for extensions for Postgres.

In the past year, I did a bunch of design work on it. Here’s a high level architectural view. We’d have a root registry, which is still the source code distribution stuff. There’s a web UX over it that would evolve from the current website. And there’s a command line client that knows how to build extensions from the registry.

But in addition to those three parts, which we have today, we would evolve a couple of additional parts.

1. One is “interactions”, so that when somebody releases a new extension on PGXN, some notifications could go out through webhooks or some sort of queue so that downstream systems like the packaging systems could know something new has come out and maybe automate building and updating their packages.

2. There could be “stats and reports”, so we can provide data like how many downloads there are, what binary registries make them available, what kinds of reviews and quality metrics rate them. We can develop these stats and display those on the website.

3. And, ideally, a “packaging registry” for PGXN to provide binary packages for all the major platforms of all the extensions we can, to simplify the installation of extensions for anybody who needs to use them. For extensions that aren’t available through PGDG or if you’re not using that system and you want to install extensions. Late last year, I was focused on figuring out how t build the packaging system.

Another change that went down in the past year was the Extension Ecosystem Summit itself. This took place at PGConf.Dev last May. The idea was for a community of people to come together to collaborate, examine ongoing work in the extension distribution, examine challenges, identify questions, propose solutions, and agree on directions for execution. Let’s take a look at the topics that we covered last year at the summit.

• One was extension metadata, where the topics covered included packaging and discoverability, extension development, compatibility and taxonomies as being important to represent a metadata about extensions — as well as versioning standards. One of the outcomes was an RFC for version two of the PGXN metadata that incorporates a lot of those needs into a new metadata format to describe extensions more broadly.

• Another topic was the binary distribution format and what it should look like, if we were to have major, distribution format. We talked about being able to support multiple versions of an extension at one time. There was some talk about the Python Wheel format as a potential precedent for binary distribution of code.

  There’s also an idea to distribute extensions through Docker containers, also known as the Open Container Initiative. Versioning came up here, as well. One of the outcomes from this session was another PGXN RFC for binary distribution, which was inspired by Python Wheel among other stuff.

  I wanted to give a brief demo build on that format. I hacked some changes into the PGXS Makefile to add a new target, trunk that builds a binary package called a “trunk” and uploads it to an OCI registry for distribution. Here’s what it looks like.

  • On my Mac I was compiling my semver extension. Then I go into a Linux container and compile it again for Linux using the make trunk command. The result is two .trunk files, one for Postgres 16 on Darwin and one for Postgres 16 on Linux.

  • There are also some JSON files that are annotations specifically for OCI. We have a command where we can push these images to an OCI registry.

  • Then we can then use an install command that knows to download and install the version of the build appropriate for this platform (macOS). And then I go into Linux and do the same thing. It also knows, because of the OCI standard, what the platform is, and so it installs the appropriate binary.

• Another topic was ABI and API compatibility. There was some talk at the Summit about what is the definition of an ABI and an API and how do we define internal APIs and their use? Maybe there’s some way to categorize APIs in Postgres core for red, green, or in-between, something like that. There was desire to have more hooks available into different parts of the system.

  One of the outcomes of this session was that I worked with Peter Eisentraut on some stability guidance for the API and ABI that is now committed in the docs. You can read them now on in the developer docs, they’ll be part of the Postgres 18 release. The idea is that minor version releases should be safe to use with other minor versions. If you compiled your extension against one minor version, it should be perfectly compatible with other minor versions of the same major release.

  Interestingly, there was a release earlier this year, like two weeks after Peter committed this, where there was an API break. It’s the first time in like 10 years. Robert Treat and I spent quite a bit of time trying to look for a previous time that happened. I think there was one about 10 years ago, but then this one happened and, notably it broke the Timescale database. The Core Team decided to release a fix just a week later to restore the ABI compatibility.

  So it’s clear that even though there’s guidance, you should in general be able to rely on it, and it was a motivating factor for the a new release to fix an ABI break, there are no guarantees.

  Another thing that might happen is that I proposed a Google Summer of Code project to build an ABI checker service. Peter [embarrassing forgetfulness and misattributed national identity omitted] Geoghegan POC’d an ABI checker in 2023. The project is to take Peter’s POC and build something that could potentially run on every commit or push to the back branches of the project. Maybe it could be integrated into the build farm so that, if there’s a back-patch to an earlier branch and it turns red, they quickly the ABI was broken. This change could potentially provide a higher level of guarantee — even if they don’t end up using the word “guarantee” about the stability of the ABIs and APIs. I’m hoping this happens; a number of people have asked about it, and at least one person has written an application.

• Another topic at the summit last year was including or excluding extensions in core. They’ve talked about when to add something to core, when to remove something from core, whether items in contrib should actually be moved into core itself, and whether to move metadata about extensions into catalog. And once again, support for multiple versions came up; this is a perennial challenge! But I’m not aware of much work on these questions. I’m wondering if it’s time for a revisit,

• As a bonus item — this wasn’t a formal topic at the summit last year, but it came up many times in the mini-summits — is the challenge of packaging and lookup. There’s only one path to extensions in SHAREDIR. This creates a number of difficulties. Christoph Berg has a patch for a PGDG and Debian that adds a second directory. This allowed the PGDG stuff to actually run tests against extensions without changing the core installation of the Postgres service itself. Another one is Cloud Native Postgres immutability. If that directory is part of the image, for your CloudNative Postgres, you can’t install extensions into it.

  It’s a similar issue, for Postgres.app immutability. Postgres.app is a Mac app, and it’s signed by a certificate provided by Apple. But that means that if you install an extension in its SHAREDIR, it changes the signature of the application and it won’t start. They work around this issue through a number of symlink shenanigans, but these issues could be solved by allowing extension to be installed in multiple locations.

  Starting with Christoph’s search path patch and a number of discussions we had at PGConf last year, Peter Eisentraut has been working on a search path patch to the core that would work similar to shared preload libraries, but it’s for finding extension control files. This would allow you to have them in multiple directories and it will find them in path.

  Another interesting development in this line has been, the CloudNativePG project has been using that extension search path patch to prototype a new feature coming to Kubernetes that allows one to mount a volume that’s actually another Docker image. If you have your extension distributed as an OCI image, you can specify that it be mounted and installed via your CNPG cluster configuration. That means when CNPG spins up, it puts the extension in the right place. It updates the search path variables and stuff just works.

  A lot of the thought about the stuff went into a less formal RFC I wrote up in my blog, rather than on PGXN. The idea is to take these improvements and try to more formally specify the organization of extensions separate from how Postgres organizes shared libraries and shared files.

I said, we’re bringing the Extension Summit back! There will be another Extension Summit hosted our team of organizers, myself, Floor, Keith Fiske from Crunchy Data, and Yurii from Omnigres. That will be on May 13th in the morning at PGConf.dev; we appreciate their support.

The idea of these Mini Summits is to bring up a number of topics of interest. Have somebody come and do a 20 or 40 minute talk about it, and then we can have discussion about implications.

Floor mentioned the schedule, but briefly:

• March 12: David Wheeler, PGXN: “State of the Extension Ecosystem”
• March 24: Peter Eisentraut, Core Team: “Implementing an Extension Search Path”
• April 9: Christoph Berg, Debian: “Apt Extension Packaging”
• April 23:
• May 7: Gabriele Bartolini, CNPG “Extension Management in CloudNativePG”

So, what are your interests in extensions and how they can be improved. There are a lot of potential topics to talk about at the Summit or at these Mini Summits: development tools, canonical registry, how easy it is to publish, continuous delivery, yada, yada, yada, security scanning — all sorts of stuff that could go into conceiving, designing, developing, distributing extensions for Postgres.

I hoe you all will participate. I appreciate you taking the time to listen to me for half an hour. So I’d like to turn it over to, discussion, if people would like to join in, talk about implications of stuff. Also, we can get to any questions here.

Questions, comments, shout-outs

Floor: David, at one point you talked about, metadata taxonomy. If you can elaborate on that a little bit, that’s Peter’s question.

David: So one that people told me that they found useful was one provided by Trunk. So it has these limited number of categories, so if you’re interested in machine learning stuff, you could go to the machine learning stuff and it shows you what extensions are potentially available. They have 237 extensions on Trunk now.

PGXN itself allows arbitrary tagging of stuff. It builds this little tag cloud. But if I look at this one here, you can see this one has a bunch of tags. These are arbitrary tags that are applied by the author. The current metadata looks like this. It’s just plain JSON, and it has a list of tags. The PGXN Meta v2 RFC has a bunch of examples. It’s an evolution of that META.json, so the idea is to have a classifications that includes tags as before, but also adds categories, which are a limited list that would be controlled by the core [he means “root”] registry:

{
  "classifications": {
    "tags": [
      "testing",
      "pair",
      "parameter"
    ],
    "categories": [
      "Machine Learning"
    ]
  }
}

Announcements

Yurii made a number of announcements, summarizing:

• There is a new library that they’ve been developing at Omnigres that allows you to develop Postgres extensions in C++. For people who are interested in developing extensions in C++ and gaining the benefits of that and not having to do all the tedious things that we have to do with C extensions: look for Cppgres. Yurii thinks that within a couple of months it will reach parity with pgrx.

  David: So it sounds like it would work more closely to the way PGXS and C works. Whereas pgrx has all these additional Rust crates you have to load and like slow compile times and all these dependencies.

  Yurii: This is just like a layer over the C stuff, an evolution of that. It’s essentially a header only library, so it’s a very common thing in the C++ world. So you don’t have to build anything and you just include a file. And in fact the way I use it, I amalgamate all the header files that we have into one. Whenever I include it in the project, I just copy the amalgamation and it’s just one file. You don’t have any other build chain associated yet. It is C++ 20, which some people consider new, but by the time it’s mature it’s already five years old and most compilers support it. They have decent support of C++ 20 with a few exclusions, but those are relatively minor. So for that reason, it’s not C++ 23, for example, because it’s not very well supported across compilers, but C++ 20 is.

• Yurii is giving a talk about PostgresPM at the Postgres Conference in Orlando. He’ll share the slides and recording with this group. The idea behind PostgresPM is that it takes a lot of heuristics, takes the URLs of packages and of extensions and creates packages for different outputs like for Red Hat, for Debian, perhaps for some other formats in the future. It focuses on the idea that a lot of things can be figured out.

  For example: do we have a new version? Well, we can look at list of tags in the Git repo. Very commonly that works for say 80 percent of extensions. Do we need a C compiler? We can see whether we have C files. We can figure out a lot of stuff without packagers having to specify that manually every time they have a new extension. And they don’t have to repackage every time there is a new release, because we can detect new releases and try to build.

• Yurii is also running an event that, while not affiliated with PGConf.dev, is strategically scheduled to happen one day before PGConf.dev: Postgres Extensions Day. The Call for Speakers is open until April 1st. There’s also an option for people who cannot or would not come to Montreal this year to submit a prerecorded talk. The point of the event is not just to bring people together, but also ti surface content that can be interesting to other people. The event itself is free.

Make sure to join our Meetup group and join us live, March 26, when Peter Eisentraut joins us to talk about implementing an extension search path.

I’m happy to announce that some PostgreSQL colleagues and have once again organized the Extension Ecosystem Summit at PGConf.dev in Montréal on May 13. Floor Drees, Yurii Rashkovskii, Keith Fiske will be on hand to kick off this unconference session:

Participants will collaborate to learn about and explore the ongoing work on PostgreSQL development and distribution, examine challenges, identify questions, propose solutions, and agree on directions for execution.

Going to PGConf.dev? Select it as an “Additional Option” when you register, or update your registration if you’ve already registered. Hope to see you there!

Extension Ecosystem Mini-Summit 2.0

We are also once again hosting a series of virtual gatherings in the lead-up to the Summit, the Postgres Extension Ecosystem Mini-Summit.

Join us for an hour or so every other Wednesday starting March 12 to hear contributors to a variety of community and commercial extension initiatives outline the problems they want to solve, their attempts to so, challenges discovered along the way, and dreams for an ideal extension ecosystem in the future. Tentative speaker lineup (will post updates as the schedule fills in):

• March 12: David Wheeler, PGXN: “State of the Extension Ecosystem”
• March 24: Peter Eisentraut, Core Team: “Implementing an Extension Search Path”
• April 9: Christoph Berg, Debian: “Apt Extension Packaging”
• April 23:
• May 7: Gabriele Bartolini, CNPG “Extension Management in CloudNativePG”

Join the meetup for details. These sessions will be recorded and Posted to the PGConf.dev YouTube and we’ll have again detailed transcripts. Many thanks to my co-organizers Floor Drees and Yurii Rashkovskii, as well as the PGConf.dev organizers for making this all happen!

Released yesterday: Sqitch v1.5.0. This version the MySQL driver DBD::mysql with DBD::MariaDB, both for its better backward compatibility with MySQL as well as MariaDB driver libraries and for its improved Unicode handling. The Docker image likewise switched to the MariaDB mysql client. I expect no compatibility issues, but you never know! Please file an issue should you find any.

V1.5.0 also features a fixes for Yugabyte deployment, Oracle error handling, existing Snowflake schemas, connecting to MySQL/MariaDB without a database name, and omitting the checkit MySQL/MariaDB function when the Sqitch user lacks sufficient permission to create it. Sqitch now will also complain when deploying with --log-only and a deployment file is missing.

Find it in the usual places:

• sqitch.org
• GitHub
• CPAN
• Docker
• Homebrew

Many thanks to everyone who has enjoyed using Sqitch and let me know in person, via email Mastodon, bug reports, and patches. It gratifies me how useful people find it.

I seek the wisdom of the Perl Monks:

The Sqitch project got a request to switch from DBD::mysql to DBD::MariaDB. DBD::mysql 5’s requirement to build from the MySQL 8 client library provides the impetus for the request, but in poking around, I found a blogs.perl.org post highlighting some Unicode fixes in DBD::MariaDB, as well.

Now, Sqitch likely doesn’t have the Unicode issue (it always works with Perl Unicode strings), but it depends on URI::db to provide the DBI connection string. For MySQL URIs, the URI::mysql dbi_driver method returns mysql.

Should it be changed to return MariaDB, instead? Is there general community consensus that DBD::MariaDB provides better compatibility with both MySQL and MariaDB these days?

I’m also curious what the impact of this change would be for Sqitch. Presumably, if DBD::MariaDB can build against either the MariaDB or MySQL client library, it is the more flexible choice to continue supporting both databases going forward.

Feedback appreciated via PerlMonks or the Sqitch issue.

Update 2025-01-08

URI-db 0.23 uses DBD::MariaDB instead of DBD::mysql for both URI::mysql and URI::MariaDB.

Similarly, Sqitch v1.5.0 always uses DBD::MariaDB when connecting to MySQL or MariaDB, even when using older versions of URI::db. Thanks everyone for the feedback and suggestions!

Happy New Year! 🎉🥳🍾🥂

The JSONPath RFC includes a section on defining normalized paths, which use a subset of JSONPath syntax to define paths to the location of a node in a JSON value. I hadn’t thought much about it, but noticed that the serde JSONPath Sandbox provides a “Located” switch adds them to query results. For the sake of complementarity, I added the same feature to the Go JSONPath Playground.

🛝 See it in action with this example, where instead of the default output:

[
  8.95,
  12.99,
  8.99,
  22.99,
  399
]

The located result is:

[
  {
    "node": 8.95,
    "path": "$['store']['book'][0]['price']"
  },
  {
    "node": 12.99,
    "path": "$['store']['book'][1]['price']"
  },
  {
    "node": 8.99,
    "path": "$['store']['book'][2]['price']"
  },
  {
    "node": 22.99,
    "path": "$['store']['book'][3]['price']"
  },
  {
    "node": 399,
    "path": "$['store']['bicycle']['price']"
  }
]

v0.3.0 of the github.com/theory/jsonpath Go package enables this feature via its new SelectLocated method, which returns a LocatedNodeList that shows off a few of the benfits of pairing JSONPath query results with paths that uniquely identify their locations in a JSON value, including sorting and deduplication. It also takes advantage of Go v1.23 iterators, providing methods to range over all the results, just the node values, and just the paths. As a result, v0.3.0 now requires Go 1.23.

The serde_json_path Rust crate inspired the use of LocatedNodeList rather than a simple slice of LocatedNode structs, but I truly embraced it once I noticed the the focus on “nodelists” in the RFC’s overview, which provides this definition:

A JSONPath expression is a string that, when applied to a JSON value (the query argument), selects zero or more nodes of the argument and outputs these nodes as a nodelist.

It regularly refers to nodelists thereafter, and it seemed useful to have an object to which more features can be added in the future. github.com/theory/jsonpath v0.3.0 thererfore also changes the result value of Select from []any to the new NodeList struct, an alias for []any. For now it adds a single method, All, which again relies on Go v1.23 iterators to iterate over selected nodes.

While the data type has changed, usage otherwise has not. One can iterate directly over values just as before:

for _, val := range path.Select(jsonInput) {
    fmt.Printf("%v\n", val)
}

But All removes the need to alias-away the index value with _:

for val := range path.Select(jsonInput).All() {
    fmt.Printf("%v\n", val)
}

I don’t expect any further incompatible changes to the main jsonpath module, but adding these return values now allows new features to be added to the selected node lists in the future.

May you find it useful!

Based on the recently-released Go JSONPath and JSONTree playgrounds, I’ve updated the design and of the SQL/JSON Playground. It now comes populated with sample JSON borrowed from RFC 9535, as well as a selection of queries that randomly populate the query field on each reload. I believe this makes the playground nicer to start using, not to mention more pleasing to the eye.

The playground has also been updated to use the recently-released sqljson/path v0.2 package, which replicates a few changes included in the PostgreSQL 17 release. Notably, the .string() function no longer uses a time zone or variable format to for dates and times.

Curious to see it in action? Check it out!

As a follow-up to the JSONPath module and playground I released last month, I’m happy to announce the follow-up project, called JSONTree. I’ve implemented it in the github.com/theory/jsontree Go package, and built a Wasm-powered browser playground for it.

JSONTree?

While a RFC 9535 JSONPath query selects and returns an array of values from the end of a path expression, a JSONTree compiles multiple JSONPath queries into a single query that selects values from multiple path expressions. It returns results not as an array, but as a subset of the query input, preserving the paths for each selected value.

In other words, it compiles multiple paths into a single tree of selection paths, and preserves the tree structure of the input. Hence JSONTree.

Example

Consider this JSON:

{
  "store": {
    "book": [
      {
        "category": "reference",
        "author": "Nigel Rees",
        "title": "Sayings of the Century",
        "price": 8.95
      },
      {
        "category": "fiction",
        "author": "Evelyn Waugh",
        "title": "Sword of Honour",
        "price": 12.99
      },
      {
        "category": "fiction",
        "author": "Herman Melville",
        "title": "Moby Dick",
        "isbn": "0-553-21311-3",
        "price": 8.99
      },
      {
        "category": "fiction",
        "author": "J. R. R. Tolkien",
        "title": "The Lord of the Rings",
        "isbn": "0-395-19395-8",
        "price": 22.99
      }
    ],
    "bicycle": {
      "color": "red",
      "price": 399
    }
  }
}

This JSONPath query:

$..price

Selects these values (playground):

[8.95, 12.99, 8.99, 22.99, 399]

While this JSONPath query:

$..author

Selects (playground):

[
  "Nigel Rees",
  "Evelyn Waugh",
  "Herman Melville",
  "J. R. R. Tolkien"
]

JSONTree compiles these two JSONPaths into a single query that merges the author and price selectors into a single segment, which stringifies to a tree-style format (playground):

$
└── ..["author","price"]

This JSONTree returns the appropriate subset of the original JSON object (playground):

{
  "store": {
    "book": [
      {
        "author": "Nigel Rees",
        "price": 8.95
      },
      {
        "author": "Evelyn Waugh",
        "price": 12.99
      },
      {
        "author": "Herman Melville",
        "price": 8.99
      },
      {
        "author": "J. R. R. Tolkien",
        "price": 22.99
      }
    ],
    "bicycle": {
      "price": 399
    }
  }
}

Note that the original data structure remains, but only for the subset of the structure selected by the JSONPath queries.

Use Cases

A couple of use cases drove the conception and design of JSONPath.

Permissions

Consider an application in which ACLs define permissions for groups of users to access specific branches or fields of JSON documents. When delivering a document, the app would:

• Fetch the groups the user belongs to
• Convert the permissions from each into JSONPath queries
• Compile the JSONPath queries into an JSONTree query
• Select and return the permitted subset of the document to the user

Selective Indexing

Consider a searchable document storage system. For large or complex documents, it may be infeasible or unnecessary to index the entire document for full-text search. To index a subset of the fields or branches, one would:

• Define JSONPaths the fields or branches to index
• Compile the JSONPath queries into a JSONTree query
• Select and submit only the specified subset of each document to the indexing system

Go Example

Use the github.com/theory/jsontree Go package together with github.com/theory/jsonpath to compile and execute JSONTree queries:

package main

import (
	"fmt"

	"github.com/theory/jsonpath"
	"github.com/theory/jsontree"
)

func main() {
	// JSON as unmarshaled by encoding/json.
	value := map[string]any{
		"name":  "Barrack Obama",
		"years": "2009-2017",
		"emails": []any{
			"potus@example.com",
			"barrack@example.net",
		},
	}

	// Compile multiple JSONPaths into a JSONTree.
	tree := jsontree.New(
		jsonpath.MustParse("$.name"),
		jsonpath.MustParse("$.emails[1]"),
	)

	// Select from the input value.
	js, err := json.Marshal(tree.Select(value))
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("%#v\n", tree.Select(value))
}

And the output:

{"emails":["barrack@example.net"],"name":"Barrack Obama"}

Note that the index position of the selected email was not preserved. Replace New with NewFixedModeTree to create a “fixed mode” JSONTree that preserves index positions by filling gaps with nulls. Its output of the above example would be:

{"emails":[null,"barrack@example.net"],"name":"Barrack Obama"}

Status

The public interface of the jsontree module is quite minimal and stable. But I suspect there may remain some flaws in the merging of JSONPath selectors. Please report bugs via GitHub issues and I’ll get them fixed up ASAP.

Otherwise, please share and enjoy!

Several weeks ago, I started a pgsql-hackers thread proposing a new extension file organization and a search path GUC for finding extensions. The discussion of Christoph Berg’s extension_destdir patch inspired this proposal. These threads cover quite a lot of territory, so I want to pull together a more unified, public proposal.

Here goes.

Challenges

A number of challenges face extension users, thanks to extension file organization in the Postgres core. The common thread among them is the need to add extensions without changing the contents of the Postgres installation itself.

Packager Testing

On Debian systems, the user account that creates extension packages lacks permission to add files to Postgres install. But testing extensions requires installing the extension where Postgres can find it. Moreover, extensions ideally build against a clean Postgres install; adding an extension in order to run make installcheck would pollute it.

Christoph’s patch solves these problems by adding a second lookup path for extensions and dynamic modules, so that Postgres can load them directly from the package build directory.

Alas, the patch isn’t ideal, because it simply specifies a prefix and appends the full pg_config directory paths to it. For example, if --sharedir outputs /opt/share and extension_destdir GUC is set to /tmp/build/myext, the patch will search in /tmp/build/myext/opt/share. This approach works for the packaging use case, which explicitly uses full paths with a prefix, but would be weird for other use cases.

Peter Eisentraut proposed an alternate patch with a new GUC, extension_control_path, that provides a more typical search path pattern to find extension control files, but doesn’t account for shared modules that ship with an extension, requiring that they still live in the dynamic_library_path. Installing into custom directories requires the undocumented datadir and pkglibdir variables:

make install datadir=/else/where/share pkglibdir=/else/where/lib

This pattern can probably be simplified.

OCI Immutability

OCI (née Docker) images are immutable, while a container image runs on a writeable but non-persistent file system. To install persistent extensions in a container, one must create a persistent volume, map it to SHAREDIR/extensions, and copy over all the extensions it needs (or muck with symlink magic). Then do it again for shared object libraries (PKGLIBDIR), and perhaps also for other pg_config directories, like --bindir. Once it’s all set up, one can install a new extension and its files will be distributed to the relevant persistent volumes.

This pattern makes upgrades tricky, because the core extensions are mixed in with third-party extensions. Worse, the number of directories that must be mounted into volumes depends on the features of an extension, increasing deployment configuration complexity. It would be preferable to have all the files for an extension in one place, rather than scattered across multiple persistent volumes.

Peter Eisentraut’s patch addresses much of this issue by adding a search path for extension control files and related data/share files (generally SQL files). One can create a single volume with a lib directory for shared modules and share/extension directory for control and data/share files.

OCI Extension Images

However, an additional wrinkle is the ambition from the CloudNativePg (CNPG) community to eliminate the need for a persistent volume, and rely instead on mounting images that each contain all the files for a single extension as their own volumes, perhaps using Kubernetes image volume feature, (currently in alpha).

This feature requires all the file in an extension to live in a single directory, a volume mounted to an extension image contains all the files required to use the extension. The search path patches proposed so far do not enable this behavior.

Postgres.app Immutability

The macOS Postgres.app supports extensions. But installing one into SHAREDIR/extensions changes the contents of the Postgres.app bundle, breaking Apple-required signature validation. The OS will no longer be able to validate that the app is legit and refuse to start it.

Peter Eisentraut’s new patch addresses this issue as well, with all the same caveats as for the packager testing challenges.

Solution

To further address these issues, this RFC proposes to change file organization and lookup patterns for PostgreSQL extensions.

Extension Directories

First, when an extension is installed, by default all of its files will live in a single directory named for the extension. The contents include:

• The Control file that describes extension
• Subdirectories for SQL, shared modules, docs, binaries, etc.

Subdirectories roughly correspond to the pg_config --*dir options:

• bin: Executables
• doc: Documentation files
• html: HTML documentation files
• lib: Dynamically loadable modules
• locale: Locale support files
• man: Manual pages
• share: SQL and other architecture-independent support files

This layout reduces the cognitive overhead for understanding what files belong to what extension. Want to know what’s included in the widget extension? Everything is in the widget directory. It also simplifies installation of an extension: one need add only a directory named for and containing the files required by the extension.

Configuration Parameter

Add a new pg_config value that returns the directory into which extensions will by default be installed:

 --extdir   show location of extensions

Its default value would be $(pg_config --sharedir)/extension, but could be set at compile time like other configuration parameters. Its contents consist of subdirectories that each contain an extension, as described in Extension Directories. With a few extensions installed, it would look something like:

❯ ls -1 "$(pg_config --extdir)"
auto_explain
bloom
isn
pair
plperl
plpgsql
plv8
xml2
semver
vector

Extension Path

Add an extension lookup path GUC akin to dynamic_library_path, called extension_path. It lists all the directories that Postgres will search for extensions and their files. The default value for this GUC will be:

extension_path = '$extdir'

The special string $extdir corresponds to the pg_config option of the same name, and function exactly as $libdir does for the dynamic_library_path GUC, substituting the appropriate value.

Lookup Execution

Update PostgreSQL’s CREATE EXTENSION command to search the directories in extension_path for an extension. For each directory in the list, it will look for the extension control file in a directory named for the extension:

$dir/$extension/$extension.control

The first match will be considered the canonical location for the extension. For example, if Postgres finds the control file for the pair at /opt/pg17/ext/pair/pair.control, it will load files only from the appropriate subdirectories, e.g.:

• SQL files from /opt/pg17/ext/pair/share
• Shared module files from /opt/pg17/ext/pair/lib

PGXS

Update the extension installation behavior of PGXS to install extension files into the new layout. A new variable, $EXTDIR, will define the directory into which to install extension directories, and default to $(pg_config --extdir). It can be set to any literal path, which must exist and be accessible by the PostgreSQL service.

The $EXTENSION variable will be changed to allow only one extension name. If it’s set, the installation behavior will be changed for the following variables:

• EXTENSION: Creates $EXTDIR/$EXTENSION, installs $EXTDIR/$EXTENSION/$EXTENSION.control
• MODULES and MODULE_big: Installed into $EXTDIR/$EXTENSION/lib
• MODULEDIR: Removed
• DATA and DATA_built: Installed into $EXTDIR/$EXTENSION/share
• DATA_TSEARCH: Installed into $EXTDIR/$EXTENSION/share/tsearch_data
• DOCS: Installed into $EXTDIR/$EXTENSION/doc
• PROGRAM, SCRIPTS and SCRIPTS_built: Installed into $EXTDIR/$EXTENSION/bin

Each of these locations can still be overridden by setting one of the (currently undocumented) installation location options (e.g., datadir, pkglibdir, etc.).

Note

External projects that install extensions without using PGXS, like pgrx, must also be updated to either follow the same pattern or to delegate installation to PGXS.

Control File

The directory control file parameter will be deprecated and ignored.

The module_pathname parameter should only name a shared module in the lib subdirectory of an extension directory. Any existing use of a $libdir prefix will be stripped out and ignored before replacing the MODULE_PATHNAME string in SQL files. The implication for loading extension dynamic modules¹ differs from the existing behavior as follows:

1. If the name is an absolute path, the given file is loaded.
2. If the name does not contain a directory part, the file is searched for in the in the lib subdirectory of the extension’s directory ($EXTDIR/$EXTENSION/lib).
3. Otherwise (the file was not found in the path, or it contains a non-absolute directory part), the dynamic loader will try to take the name as given, which will most likely fail. (It is unreliable to depend on the current working directory.)

Use Cases

Here’s how the proposed file layout and extension_path GUC addresses the use cases that inspired this RFC.

Packager Testing

A packager who wants to run tests without modifying a PostgreSQL install would follow these steps:

• Prepend a directory under the packaging install to the extension_path GUC. The resulting value would be something like $RPM_BUILD_ROOT/$(pg_config --extdir):$extdir.
• Install the extension into that directory: make install EXTDIR=$RPM_BUILD_ROOT
• Make sure the PostgreSQL server can access the directory, then run make installcheck

This will allow PostgreSQL to find and load the extension during the tests. The Postgres installation will not have been modified; only the extension_path will have changed.

OCI/Kubernetes

To allow extensions to be added to a OCI container and to persist beyond its lifetime, one or more volumes could be used. Some examples:

• Mount a persistent volume for extensions and prepend the path to that directory to the extension_path GUC. Then Postgres can find any extensions installed there, and they will persist. Files for all extensions will live on a single volume.
• Or, to meet a desire to keep some extensions separate (e.g., open-source vs company-internal extensions), two or more persistent volumes could be mounted, as long as they’re all included in extension_path, are accessible by PostgreSQL, and users take care to install extensions in the proper locations.

CNPG Extension Images

To meet the CNPG ambition to “install” an extension by mounting a single directory for each, create separate images for each extension, then use the Kubernetes image volume feature (currently in alpha) to mount each as a read-only volume in the appropriate subdirectory of a directory included in extension_path. Thereafter, any new containers would simply have to mount all the same extension image volumes to provide the same extensions to all containers.

Postgres.app

To allow extension installation without invalidating the Postgres.app bundle signature, the default configuration could prepend a well-known directory outside the app bundle, such as /Library/Application Support/Postgres, to extension_path. Users wishing to install new extensions would then need to point the EXTDIR parameter to that location, e.g.,

$ make install EXTDIR="/Library/Application Support/Postgres"`

Or the app could get trickier, setting the --extdir value to that location so that users don’t need to use EXTDIR. As long as extension_path includes both the bundle’s own extension directory and this external directory, Postgres will be able to find and load all extensions.

Extension Directory Examples

A core extension like citext would have a structure similar to:

citext
├── citext.control
├── lib
│   ├── citext.dylib
│   └── bitcode
│       ├── citext
│       │   └── citext.bc
│       └── citext.index.bc
└── share
    ├── citext--1.0--1.1.sql
    ├── citext--1.1--1.2.sql
    ├── citext--1.2--1.3.sql
    ├── citext--1.3--1.4.sql
    ├── citext--1.4--1.5.sql
    ├── citext--1.4.sql
    └── citext--1.5--1.6.sql

The subdirectory for a pure SQL extension named “pair” in a directory named “pair” that looks something like this:

pair
├── LICENSE.md
├── README.md
├── pair.control
├── doc
│   ├── html
│   │   └── pair.html
│   └── pair.md
└── share
    ├── pair--1.0--1.1.sql
    └── pair--1.1.sql

A binary application like pg_top would live in the pg_top directory, structured something like:

pg_top
├── HISTORY.rst
├── INSTALL.rst
├── LICENSE
├── README.rst
├── bin
│   └── pg_top
└── doc
    └── man
        └── man3
            └── pg_top.3

And a C extension like semver would live in the semver directory and be structured something like:

semver
├── LICENSE
├── README.md
├── semver.control
├── doc
│   └── semver.md
├── lib
│   ├── semver.dylib
│   └── bitcode
│       ├── semver
│       │   └── semver.bc
│       └── semver.index.bc
└── share
    ├── semver--1.0--1.1.sql
    └── semver--1.1.sql

Phase Two: Preloading

The above-proposed solution does not allow shared modules distributed with extensions to compatibly be loaded via shared library preloading, because extension modules wil no longer live in the dynamic_library_path. Users can specify full paths, however. For example, instead of:

shared_preload_libraries = 'pg_partman_bgw'

One could use the path to the lib subdirectory of the extension’s directory:

shared_preload_libraries = '/opt/postgres/extensions/pg_partman_bgw/lib/pg_partman_bgw'

But users will likely find this pattern cumbersome, especially for extensions with multiple shared modules. Perhaps some special syntax could be added to specify a single extension module, such as:

shared_preload_libraries = '$extension_path::pg_partman_bgw'

But this overloads the semantics of shared_preload_libraries and the code that processes it rather heavily, not to mention the LOAD command.

Therefore, as a follow up to the solution proposed above, this RFC proposes additional changes to PostgreSQL.

Extension Preloading

Add new GUCs that complement shared library preloading, but for extension module preloading:

• shared_preload_extensions
• session_preload_extensions
• local_preload_extensions

Each takes a list of extensions for which to preload shared modules. In addition, another new GUC, local_extensions, will contain a list of administrator-approved extensions users are allowed to include in local_preload_extensions. This GUC complements local_preload_libraries’s use of a plugins directory.

Then modify the preloading code to also preload these files. For each extension in a list, it would:

• Search each path in extension_path for the extension.
• When found, load all the shared libraries from $extension/lib.

For example, to load all shared modules in the pg_partman extension, set:

shared_preload_extensions = 'pg_partman'

To load a single shared module from an extension, give its name after the extension name and two colons. This example will load only the pg_partman_bgw shared module from the pg_partman extension:

shared_preload_extensions = 'pg_partman::pg_partman_bgw'

This change requires a one-time change to existing preload configurations on upgrade.

Future: Deprecate LOAD

For a future change, consider modifying CREATE EXTENSION to support shared module-only extensions. This would allow extensions with no SQL component, such as auto_explain, to be handled like any other extension; it would live under one of the directories in extension_path with a structure like this:

auto_explain
├── auto_explain.control
└── lib
   ├── auto_explain.dylib
   └── bitcode
       ├── auto_explain
       │   └── auto_explain.bc
       └── auto_explain.index.bc

Note the auto_explain.control file. It would need a new parameter to indicate that the extension includes no SQL files, so CREATE EXTENSION and related commands wouldn’t try to find them.

With these changes, extensions could become the primary, recommended interface for extending PostgreSQL. Perhaps the LOAD command could be deprecated, and the *_preload_libraries GUCs along with it.

Compatibility Issues

• The module_pathname control file variable would prefer the name of a shared module. The code that replaces the MODULE_PATHNAME string in SQL files would to strip out the $libdir/ prefix, if present.
• The behavior of loading dynamic modules that ship with extensions (i.e., the value of the AS part of CREATE FUNCTION) would change to look for a library name (with no directory part) in the lib subdirectory of the extension directory.
• The directory control file parameter and the MODULEDIR PGXS variable would be deprecated and ignored.
• *_preload_libraries would no longer be used to find extension modules without full paths. Administrators would have to remove module names from these GUCs and add the relevant extension names to the new *_preload_extensions variables. To ease upgrades, we might consider adding a PGXS variable that, when true, would symlink shared modules into --pkglibdr.
• LOAD would no longer be able to find shared modules included with extensions, unless we add a PGXS variable that, when true, would symlink shared modules into --pkglibdr.
• The EXTENSION PGXS variable will no longer support multiple extension names.
• The change in extension installation locations must also be adopted by projects that don’t use PGXS for installation, like pgrx. Or perhaps they could be modified to also use PGXS. Long term it might be useful to replace the Makefile-based PGXS with another installation system, perhaps a CLI.

Out of Scope

This RFC does not include or attempt to address the following issue:

• How to manage third-party shared libraries. Making system dependencies consistent in a OCI/Kubernetes environment or for non-system binary packaging patterns presents its own challenges, though they’re not specific to PostgreSQL or the patterns described here. Research is ongoing into potential solutions, and will be addressed elsewhere.

Acknowledgements

A slew of PostgreSQL community members contributed feedback, asked hard questions, and suggested moderate to significant revisions to this RFC via the the pgsql-hackers list, in-person discussion at PGConf.eu, and pull request comments. I’d especially like to thank:

• Yurii Rashkovskii and David Christensen for highlighting this issue at the Extension Ecosystem Summit
• Christoph Berg for the original patch, calling attention to the permission issues when building Debian packages, and various lists discussions
• Tobias Bussmann for calling attention to the immutability issues with Postgres.app
• Christoph Berg, Gabriele Bartolini, Peter Eisentraut, and Andres Freund for detailed discussion at PGConf.eu on extension location issues and getting to consensus on a genera approach to solving it
• Douglas J Hunley, Shaun Thomas, and Keith Fiske for pull request reviews and corrections
• Álvaro Hernández Tortosa for a very close review and ton of substantive feedback on the pull request
• Paul Ramsey, Tristan Partin, Ebru Aydin Gol, and Peter Eisentraut for pgsql-hackers list discussions.
• Tembo for supporting my work on this and many other extension-related issues

All remaining errors and omissions remain my own.

I quit Twitter in November 2022, when it became clear that Phony Stark would take control of the company. No regrets; I’ve been active on Mastodon since then, via Ivory, where I’ve enjoyed the Twitter ca. 2010 vibe.

But I hadn’t deleted my Tweets or my accounts until this week. The change was finally triggered by the latest announcement:

On Friday, X will adopt a new terms of service policy that for the first time explicitly says all users agree to let their posts be used to train artificial intelligence, like the company’s generative AI service, Grok. Currently, X users can go to the site’s settings and opt out.

I have no interest in feeding Grok or any other large language model. So this week I deleted all my tweets and then my accounts. I tried to transfer my personal username to a new, empty account, but in the second between changing it and switching to another tab to take it, it was somehow acquired by an account created in 2011 with no posts. Not sure how that worked. Pretty scummy.

So, just to be clear, anyone using these usernames on Twitter are not me anymore:

• @theory
• @lunar_theory
• @DesignScene
• @pgxn

See you on Mastodon — or perhaps Bluesky.

I’ve written and release a RFC 9535 JSONPath JSONPath Go package, github.com/theory/jsonpath. Why? For a personal project, I needed a simpler JSONPath engine to complement to the Go SQL/JSON Path package, and quickly found myself implementing most of the RFC. So I decided do the whole thing.

Yes, yet another JSONPath package in Go. I really appreciate the idea of a standard — plus its support for features not included in the original design from 2007, such as object slices! But I could find no reference to the RFC on pkg.go.dev. Today the search shows one!

Example

Usage is straightforward; here’s a quick example (Go playground):

package main

import (
	"fmt"
	"log"

	"github.com/theory/jsonpath"
)

func main() {
	// Parse a jsonpath query.
	p, err := jsonpath.Parse(`$["name", "slogan"]`)
	if err != nil {
		log.Fatal(err)
	}

	// Select values from unmarshaled JSON input.
	json := map[string]any{
		"name":   "Kamala Harris",
		"title":  "Vice President of the United States",
		"home":   "California",
		"slogan": "We are not going back!",
	}
	items := p.Select(json)

	// Show the result.
	fmt.Printf("%#v\n", items)
}

And the output:

[]interface {}{"Kamala Harris", "We are not going back!"}

🛝 Playground

No need to write code to try it out, though. I’ve also written a playground webapp to encourage experimentation and exploration of the syntax and behavior of the package. The implementation follows the precedents set by the Go SQL/JSON Playground and design of the Rust JSONPath Playground. Moreover, thanks to TinyGo, the Wasm file comes in at a mere 254K!

The webapp loads sample JSON from the RFC, and randomly rotates through a few example JSONPath queries. Fill in your own and tap the “Permalink” button to share links. The Playground is a stateless JavaScript/Wasm web application: data persists only in permalink URLs.¹

🛝 Try this example Playground permalink right now!

Status

The root jsonpath package is stable and ready for use. Other packages remain in flux, as I refactor and rejigger things in the coming weeks as part of the aforementioned personal project. But for actual JSONPath execution and querying, it should continue to work as-is for the foreseeable future.

I hope you find it useful.

This is a big deal. My former colleagues in the New York Times Tech Guild have declared a strike today in response to a number of unfair labor practice violations and management’s inability to bargain in good faith. From the Washington Post:

Represented by the NewsGuild of New York, the Tech Guild has been negotiating its first contract with the company since 2022. Both sides have accused the other side of bogging down contract talks, which have recently focused on three key issues: “just cause” job protections (which ensure employees can’t be fired without reason and due process), remote work and pay equity. Unit members voted overwhelmingly in September to authorize the strike, saying the timing of the authorization — in the run-up to the high-profile election period — was “no accident.”

I urge you to join me in supporting the strike fund, which will help more vulnerable members to weather the strike.

Beyond that, the Guild asks supporters to refrain from interacting with Cooking and Games, and to break your streaks. Times reporters Maggie Astor has the details:

As of this morning, the New York Times Tech Guild, which represents NYT tech workers, is on strike.

Unless it’s resolved while today’s Wordle is still live, this streak is gone.

NYT Games and Cooking are BEHIND THE PICKET LINE. Please don’t play or engage with Games or Cooking content while the strike lasts!

News coverage is NOT behind the picket line. It’s okay to read and share that, though the site and app may have problems.

Read the whole thread for more information, which, as she says, “the rules may not be what you think!”

Last week I MCed the first Extension Ecosystem Summit EU and attended my first at PGConf EU in Athens, Greece. Despite my former career as an archaeologist — with a focus on Mediterranean cultures, no less! — this was my first visit to Greece. My favorite moment was the evening after the Summit, when I cut out of a networking shindig to walk to Pláka and then circumnavigate the Acropolis. I mean just look at this place!

Highlight of the trip for sure. But the Summit and conference were terrific, as well.

Extension Ecosystem Summit

Floor Drees kindly organized The Extension Ecosystem Summit EU, the follow-up to the PGConf.dev original. While the Vancouver Summit focused on developers, we tailored this iteration to users. I started the gathering with a condensed version of my POSETTE talk, “State of the Postgres Extension Ecosystem”, but updated with a Trunk OCI Distribution demo. Links:

• PDF
• PDF with Notes
• Keynote

We then moved into a lightning round of 10 minute introductions to a variety of extensions:

• Jimmy Angelakos showed off pg_statviz, a “minimalist extension and utility pair for time series analysis and visualization of PostgreSQL internal statistics”.
• Adam Hendel gave a brief history of pgmq, is a “lightweight message queue like AWS SQS and RSMQ, but on Postgres.”
• Gülçin Yıldırım Jelínek introduced pgzx, “a library for developing PostgreSQL extensions written in Zig.”
• James Sewell talked about pgvectorscale and pgai for managing machine training workloads on Postgres.
• Alastair Turner described pg_tde, an extension that provides transparent data encryption for Postgres.

Quite the whirlwind! There followed open discussion, in which each maintainer went to a corner to talk to attendees about contributing to their extensions. Details to come in a more thorough writeup on the Tembo blog, but I personally enjoyed some fascinating discussions about extension distribution challenges.

PGConf.eu

Following the Summit, I attended several thought-provoking and provocative presentations at PGConf.eu, which took place at the same hotel, conveniently enough.

• In the conference keynote, Stacey Haysler explained the The PostgreSQL License Fee. I’m pleased to say that my employer “pays” license fee!
• Andres Freund (yes, that one) summarized NUMA vs PostgreSQL, covering some of the issues and opportunities for optimization of PostgreSQL on servers using the NUMA multi-processor memory architecture.
• Heikki Linnakangas offered an overview of The Wire Protocol, that bit of PostgreSQL technology that lets clients talk to PostgreSQL.
• Ophir Lojkine and Thomas Guillemard showed how a pretty sweet framework, SQLPage, lets archaeologists like Thomas write complete web applications in pure SQL, enabling rapid data entry while in the field.
• Tomas Vondra’s Performance Archaeology dug into the history of PostgreSQL improvements, mapping their compounding returns over time.
• Floor Drees (photo above) talked about Supporting extensions, but really now, making a strong case for the need to organize infrastructure to support the broader extension ecosystem.

There were many more talks, but clearly I tend to be drawn to the most technical, core-oriented topics. And also archaeology.

Museums

Speaking of which, I made time to visit two museums while in Athens. First up was the National Archaeological Museum of Athens, where I was delighted to explore the biggest collection of Mycenaean artifacts I’ve ever seen, including massive collections from the excavations of Heinrich Schliemann. So much great Bronze Age stuff here. I mean, just look at this absolute unit:

The animal decorations on Mycenaean and Akrotiri pottery is simply delightful. I also enjoyed the Hellenistic stuff, and seeing the famed Antikythera Mechanism filled my nerd heart with joy. A good 3 hours poking around; I’ll have to go back and spend a few days there sometime. Thanks to my pal Evan Stanton for gamely wandering around this fantastic museum with me.

Immediately after the PGConf.eu closing session, I dashed off to the Acropolis Museum, which stays open till 10 on Fridays. Built in 2009, this modern concrete-and-glass building exhibits several millennia of artifacts and sculpture exclusively excavated from the Acropolis or preserved from its building façades. No photography allowed, alas, but I snapped this photo looking out on the Acropolis from the top floor.

I was struck by the beauty and effectiveness of the displays. It easily puts the lie to the assertion that the Elgin Marbles must remain in the British Museum to protect them. I saw quite a few references to the stolen sculptures, particularly empty spots and artfully sloppy casts from the originals, but the building itself makes the strongest case that the marbles should be returned.

But even without them there remains a ton of beautiful sculpture to see. Highly recommended!

Back to Work

Now that my sojourn in Athens has ended, I’m afraid I must return to work. I mean, the event was work, too; I talked to a slew of people about a number of projects in flight. More on those soon.

Speaking of PGXN news, I neglected to link to this post I wrote for the Tembo Blog last month, a fairly detailed accounting of what’s been happening on the PGXN v2 project:

Forgive me Postgres community, for it has been five months since my last PGXN v2 Update. In my defense, it has been super busy! The time went into ongoing community discussions, planning, designs, and the start of implementation. Join me below for the lowdown.

A few highlights:

• PGXN RFCs Repository and rfcs.pgxn.org
• Binary Distributution POC and OCI POC
• Extension Ecosystem Summit
• API and ABI guidance
• pgxn_meta v0.1.0
• PGXN Meta JSON Schemas
• project plan

There’s been quite a bit of activity since then, including the aforementioned PGXN RFC–5 — Release Certification. More soon!

A couple weeks ago, I drafted PGXN RFC–5 — Release Certification, which proposes to replace the simple inclusion of a SHA-1 hash digests in PGXN release META.json files with a JWS-signed release payload. From the introduction:

This RFC therefore proposes to extend v2 distribution metadata with a single additional property, certs, that contains one or more certifications that attest to the authenticity or other characteristics of a release on PGXN.

The certs value is an object that contains at least one property, pgxn, which itself contains a PGXN-generated RFC 7515 JSON Web Signature in the JWS JSON Serialization format. The pgxn property will allow clients not only to assemble the release URL and verify the downloaded file against checksums, but also validate it against a public key provided by PGXN.

The design allows multiple signatures, certifications, or other attestations, which in the future MAY allow authors or other entities to sign releases with their own keys. The new format appends a structure such as this to the distribution META.json file:

{
  "certs": {
    "pgxn": {
      "payload": "eyJ1c2VyIjoidGhlb3J5IiwiZGF0ZSI6IjIwMjQtMDktMTNUMTc6MzI6NTVaIiwidXJpIjoiZGlzdC9wYWlyLzAuMS43L3BhaXItMC4xLjcuemlwIiwiZGlnZXN0cyI6eyJzaGE1MTIiOiJiMzUzYjVhODJiM2I1NGU5NWY0YTI4NTllN2EyYmQwNjQ4YWJjYjM1YTdjMzYxMmIxMjZjMmM3NTQzOGZjMmY4ZThlZTFmMTllNjFmMzBmYTU0ZDdiYjY0YmNmMjE3ZWQxMjY0NzIyYjQ5N2JjYjYxM2Y4MmQ3ODc1MTUxNWI2NyJ9fQ",
      "signature": "cC4hiUPoj9Eetdgtv3hF80EGrhuB__dzERat0XF9g2VtQgr9PJbu3XOiZj5RZmh7AAuHIm4Bh-rLIARNPvkSjtQBMHlb1L07Qe7K0GarZRmB_eSN9383LcOLn6_dO--xi12jzDwusC-eOkHWEsqtFZESc6BfI7noOPqvhJ1phCnvWh6IeYI2w9QOYEUipUTI8np6LbgGY9Fs98rqVt5AXLIhWkWywlVmtVrBp0igcN_IoypGlUPQGe77Rw"
    }
  }
}

Review and feedback would be very much appreciated, especially on the list of unresolved questions toward the end.

Thanks to David Christensen and Steven Miller for the early reviews!

Meanwhile, I’ve released pgxn_meta v0.4.0, which adds support for this format, as well as code to rewrite PGXN v1 release fields to the new format. It doesn’t actually do signature verification, yet, as the server back end hasn’t been updated with the pattern and PKI. But I expect to modify it in response to feedback and get it implemented in early 2025.