PgDD extension moves to pgrx

Our data dictionary extension, PgDD, has been re-written using the pgrx 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 pgrx framework and the ease it brings to developing Postgres extensions! Getting started with pgrx is straightforward and using cargo pgrx run makes it simple to build your extension against multiple versions of Postgres.

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

Note: pgrx was originally named pgx. This post has been updated to reflect its current name.

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 pgrx 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 pgrx

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, pgrx! 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 pgrx (a7fc97a) worked! At that time pgrx v0.0.14 was the latest release and the project was less than a year old.

As I write this post, pgrx 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 pgrx

• Ease of use
• Documentation / examples
• Help available on Discord

The three pros above all relate to the pgrx 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.

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 pgrx

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

• Rust
• Extension version updates
• Docker

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.

As extensions developed with pgrx 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 pgrx 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 pgrx 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 pgrx. At the beginning of this post I vaguely referenced cargo pgrx run. This uses the Postgres versions compiled to ~/.pgrx/ via cargo pgrx init. Want to test Postgres 13?

cargo pgrx run pg13

Postgres 10?

cargo pgrx run pg10

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

Summary

If you are considering pgrx to develop your Postgres extension: do it! The pgrx repo has a list of pgrx 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 pgrx. 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!