RustProof Labs: blogging for education (logo)

PgDD extension moves to Pgx

By Ryan Lambert -- Published October 08, 2021

Our data dictionary extension, PgDD, has been re-written using the pgx framework in Rust! At this time I have tagged 0.4.0.rc3 and just need to do a bit more testing before the official 0.4.0 release. While I am excited for the news for PgDD, what is more exciting is the pgx framework and the ease it brings to developing Postgres extensions! Getting started with pgx is straightforward and using cargo pgx run makes it simple to build your extension against multiple versions of Postgres.

This post outlines how I came to the decision to use pgx for Postgres extension development.

Progression of PgDD

Before now, PgDD was a raw SQL extension, with that version being an evolution from prior iterations. Shortly after I converted PgDD to a raw SQL extension I wanted it to do more, specifically related to supporting newer features such as generated columns and native partitioning. Supporting new features in new versions of Postgres is a good idea, but I couldn't drop support for older versions at that time either. Using generated columns as an example, the feature was added in Postgres 12 and came along with an update to the pg_catalog.pg_attribute system catalog. In Pg12 and newer, pg_attribute has a column named attgenerated while earlier versions of Postgres do not have that column.

To handle this I needed to use feature switches based on the major version of Postgres. That is possible with a raw-SQL extension, shown in this slide deck referencing the pg_partman extension. With this path I realized I would have to maintain both the version specific functionality for CREATE EXTENSION as well as version specific upgrade paths for ALTER EXTENSION pgdd UPDATE;. It was these update paths where I thought the Postres version plus extension version update paths would get too time-consuming for me to maintain. I thought "There has to be a better way!"

Maintaining the update paths were not the only consideration for looking beyond raw SQL for PgDD. I have other extension ideas, I thought learning C would be useful, I thought there might be some performance gains... At this point I had decided to jump into rewriting PgDD in C! Nope, I was not to the pgx decision yet.

Attempt at C

Being relatively confident that I needed a more advanced solution than raw SQL, I decided to explore converting PgDD to an extension written in C. The time was August 2020. I did a bunch of reading in the Postgres docs (like here and here), and felt extremely lucky to have found three blog posts (a, b, c) that were all written in the few months prior to my foray into attempting to be a C programmer.

Long story short, I am not a C programmer! I probably spent 30 hours trying to work up my first functional protoype of PgDD in C. All I managed to do was crash Postgres. Over and over. The commit messages from those efforts accurately represents my memory, read from the bottom up to walk in my shoes.

* 8931b7e - (1 year, 1 month ago) Yet another place of error.... - Ryan Lambert (HEAD -> clang, privrepo/clang)
* c54116b - (1 year, 1 month ago) At a different place of error.... - Ryan Lambert
* 8b7413c - (1 year, 2 months ago) Forward progress, not yet functional. - Ryan Lambert
* 5e8d293 - (1 year, 2 months ago) Non-functional shell pointing in expected direction - Ryan Lambert

I still believe I could learn C and figure out what I was doing wrong, thought it no longer matters if that belief is accurate or not. I do not have the time nor the desire to dedicate myself to that learning curve right now. I just want to make stuff work, and I do not want to crash Postgres with my simple extension.

Enter pgx

Around the time I was failing to learn C, I had a conversation with Eric Ridge, the creator of ZomboDB. Beyond being fun to talk to, Eric suggested I try out a relatively new project of his. You guessed it, pgx! To summarize his sales pitch, he promised me unicorns and rainbows. I bought his story, hook, line and sinker! I must be a chump. Except...

I spent a bit of time reading through the READMEs before exploring and copy/pasting/editing some of the example code available. Probably another couple minutes to copy/paste some SQL from PgDD. After an hour or so, I had a working prototype of an extension! My first commit towards using pgx (a7fc97a) worked! At that time pgx v0.0.14 was the latest release and the project was less than a year old.

As I write this post, pgx v0.2.0.beta1 has been tagged for about two weeks. The chatter on Discord today was about adding Pg14 support and the soon-to-be released v0.2.0.beta2. I currently have PgDD pinned against the develop branch, as Hoverbear tracked down and fixed a strange bug I had encountered. When the beta2 is out I will be working to test that release ASAP including compatibility with Postgres 14.

Pros with pgx

The three pros above all relate to the pgx team's obvious focus on making it easy to create Postgres extensions. I will let the first two points speak for themselves, read the docs and try it out! I do want to share my experience with getting help, because I have asked for a lot of it. Early on in the process I ran into something unexpected, figured I was doing something wrong, so asked on Discord. The following screenshot shows Eric confirming it was a bug at 4:24 PM. 21 minutes later he had pushed a patched version and I had confirmed it fixed the issue on my end.

Screenshot from discord in 2020 with the confirmation that for once, it was not user error on my part but was instead a bug.

More recently, there was that strange bug I mentioned a couple paragraphs earlier. Hoverbear spent a couple hours working with me via Discord to identify what the heck was actually going on, before actually fixing the issue. Of course she did so promptly!

Cons with pgx

To be fair, I'll list some cons to pgx even though I am absolutely thrilled to be using it. I admit, I am biased.

Let's start with Rust. Because of this change, 56% of the code in PgDD is now Rust. That's bad branding for RustProof Labs! Since I've already been asked if I'm going to change our name: No.

Screenshot from GitHub showing the languages in the PgDD repository.  Languages in order from largest to smallest: Rust (55.9%), Shell (24.1%), Dockerfile (19%), and Makefile (1%)

As extensions developed with pgx have new versions introduced, the update path is a bit rocky. My plan for the short term is to use DROP EXTENSION / CREATE EXTENSION for extension updates, so if you're using PgDD over the next few months, be warned! In the longer term, there are two open issues (#120 and 121) that document what was found related to this. My memory was that the SQL generator rewrite (see pr) was the first big step to addressing the update-path issues. I have confidence these issues will be ironed out over the coming <vague reference of time in the not-so-distant future>.

Last, is Docker. When PgDD was a raw-SQL extension, the make/install process was easy and lightweight. For that I created an Ansible role to make/install PgDD as part of our standard deployment. Docker was not even a consideration of mine for that version. With pgx and Rust, the build process is... hot. To keep this process off production instances, I decided to build binaries for the latest versions of Ubuntu using Docker, modeled after ZomboDB's build system. I don't track my time on open source projects, but I'd estimate 80-90% of my effort working on this conversion to Pgx was a result of my non-expert Docker skills plus my inexperience with packaging binaries.

Testing multiple versions

To cap off this post, I have one more pro for using pgx. At the beginning of this post I vaguely referenced cargo pgx run. This uses the Postgres versions compiled to ~/.pgx/ via cargo pgx init. Want to test Postgres 13?

cargo pgx run pg13

Postgres 10?

cargo pgx run pg10

Easy. You don't have to manually install multiple versions of Postgres, pgx will do that for you. Perfect for development and testing! The process is described under the getting started section.


If you are considering pgx to develop your Postgres extension: do it! The pgx repo has a list of pgx related posts with additional helpful resources.

PgDD itself is not seeing significant functionality changes with this release, the main goal is to switch over to pgx. The 0.4.1 release will focus on adding new features again.

Need help with your PostgreSQL servers or databases? Contact us to start the conversation!

By Ryan Lambert
Published October 08, 2021
Last Updated October 08, 2021