Sadly, the home of the PGXN Mastodon bot for the last two years, botsin.space is shutting down. I’ve created a new account, @pgxn@mastodon.social and moved all the followers. Please give it a follow if you didn’t follow the old account, and stay up-to-date on the latest PGXN releases!

New post up on Just a Theory, RFC: PGXN Metadata Sketch, seeking feedback on a slew of ideas to update the PGXN Meta Spec. Help us to help make the future of PGXN and the broader extension ecosystem better!

One of the perks of my new gig at Tembo is that I have more time to work on PGXN. In the last ten years I’ve had very little time to give, so things have stagnated. The API, for example, hasn’t seen a meaningful update since 2016!

But that’s all changed now, and every bit of the PGXN architecture has experienced a fair bit of TLC in the last few weeks. A quick review.

PGXN Manager

PGXN Manager provides user registration and extension release services. It maintains the root registry of the project, ensuring the consistency of download formatting and naming ($extension-$version.zip if you’re wondering). Last year I added a LISTEN/NOTIFY queue for publishing new releases to Twitter and Mastodon, replacing the old Twitter posting on upload. It has worked quite well (although Twitter killed the API so we no longer post there), but has sometimes disappeared for days or weeks at a time. I’ve futzed with it over the past year, but last weekend I think I finally got to the bottom of the problem.

As a result, the PGXN Mastodon account should say much more up-to-date than it sometimes has. I’ve also added additional logging capability, because sometimes the posts fail and I need better clarity into what failed and how so it, too, can be fixed. So messaging should continue to become more reliable. Oh, and testing and unstable releases are now prominently marked as such in the posts (or will be, next time someone uploads a non-stable release).

I also did a little work on the formatting of the How To doc, upgrading to MultiMarkdown 6 and removing some post-processing that appended unwanted backslashes to the ends of code lines.

PGXN API

PGXN API (docs) has been the least loved part of the architecture, but all that changed in the last couple weeks. I finally got over my trepidation and got it working where I could hack on it again. Turns out, the pending issues and to-dos — some dating back to 2011! — weren’t so difficult to take on as I had feared. In the last few weeks, API has finally come unstuck and evolved:

• Switched the parsing of Markdown documents from the quite old Text::Markdown module to CommonMark, which is a thin wrapper around the well-maintained and modern cmark library. The main way in which this change will be visible is in the support for fenced code blocks. Compare the pg_later 0.0.14 README, formatted with the old parser, with pg_later 0.1.0 README, formatted with the new parser.
• Updated the full text indexer to index the file identified in the META.json as the documentation for an extension even if the file name is different from the extension (issue 10), including the README.
• Furthermore, if the indexer finds no documentation for the extension, either in its metadata or an appropriately-named file, it falls back on the README (issue 12).
• These two fixes, the oldest and most significant of the whole system, greatly increase the accuracy of documentation search, because so many extensions have no docs other than the README. Prior to this fix, for example, a search for “later” failed to turn up pg_later, and now it’s appropriately the first result.
• The indexer now indexes releases marked “testing” or “unstable” in the metadata if there is no stable release (issue 2!). This makes new extensions under development easier to find on the site than previously, when only stable releases were indexed. However, once a stable release is made, no more testing or unstable releases will be indexed. I believe this is more intuitive behavior.
• Also fixed some permissions issue, ensuring that source code unzipped for browsing is accessible to the app. In other words, a zip file without READ permission would trigger a not found response; the API now ensures that all files and directories are accessible to the application.
• As a bonus, the API now also recognizes plain text files (.txt and .text) as documentation and indexes their contents and adds links for display on the site. Previously plain text files other than READMEs were ignored (issue 13).

I’m quite pleased with some of these fixes, and relieved to have finally cleared out the karmic overhang of the backlog. But the indexing and HTML rendering changes, in particular, are tactical: until the entire registry can be re-indexed, only new uploads will benefit from the indexing improvements. I hope to find time to work on the indexing project soon.

PGXN Site

The pgxn-site project powers the main site, pgxn.org, and has seen 7 new releases since the beginning of the year! Notable changes:

• Changed the default search index from Documentation too Distributions, because most release include no docs other than a READMe, which was indexed as part of the distribution and not the extension contained in a distribution. As a result, searches return more relevant and comprehensive results. However, I say “was indexed” because, as described above, the PGXN API has since been updated to appropriately identify and index READMEs as extension documentation. So this change might get reverted at some point, but not before the entire registry can be re-indexed.
• Changed the link to the How To in the footer from the somewhat opaque “Release It!” to “Release on PGXN”. Should make it clearer what it is. Also, have you considered releasing your extension on PGXN? You should!
• Fixed “not found” errors for links to files with uppercase letters, such as /dist/vectorize/vector-serve/README.html.
• Tweaked the CSS a bit to improve list item alignment in the “Contents” of doc pages (like semver), as well as the spacing between definition lists, as in the FAQ.
• Switched the parsing and formatting of the static pages (about, art, FAQ, feedback, and mirroring) from the quite ancient Text::MultiMarkdown module to MultiMarkdown 6.
• Oh, and I fixed or removed a bunch of outdated links from those pages, and replaced all relevant HTTP URLs with HTTPS URLs. Then had to revert those changes from a bunch of SVG files, where xmlns=“http://www.w3.org/2000/svg” is relevant but xmlns=“https://www.w3.org/2000/svg” is not. 🤦🏻‍♂️

Grab Bag

I’ve made a number of more iterative improvements, as well, mostly to the maintainability of PGXN projects:

• The GitHub repositories for all of these projects have been updated with comprehensive test and release workflows, as well as improved Perl release configuration.
• The WWW::PGXN, PGXN::API::Searcher Perl modules have been updated with test and release workflows and improved Perl release configuration, as well.
• The libexec configuration of the PGXN Client Homebrew formula has been fixed, allowing PGXN::Meta::Validator to install itself in the proper place to allow pgxn validate-meta to work properly on Homebrew managed-systems (like mine!)
• The pgxn/pgxn-tools Docker image, which saw some fixes and improvements last month, has continued to evolve, as well. Recent improvements include support for PostgreSQL TAP tests (not to be confused with pgTAP, already supported), finer control over managing the Postgres cluster — or multiple clusters — by setting NO_CLUSTER= to prevent pg-start from creating the cluster. Both these improvements better enable multi-cluster testing.

The End

I think that about covers it. Although I’m thinking and writing quite a lot about what’s next for the Postgres extension ecosystem, I expect to continue tending to the care and feeding and improvement of PGXN as well. To the future!

The Tembo blog has posted a presentation on the PGXN architecture. There hasn’t been much coverage of it since a few posts here on the PGXN blog back in 2012, so worth a revisit — and a fair bit changed in the interim, as well!

Just a quick note to highlight some bug fixes and improvements in the pgxn/pgxn-tools Docker image in the last few weeks.

v1.4.0 adds the GIT_BUNDLE_OPTS and ZIP_BUNDLE_OPTS environment variables. The former is passed to git archive and the latter to zip, and let additional options be passed to those commands. For example, the vectorize extension’s release workflow sets GIT_BUNDLE_OPTS: –add-file META.json because git archive archives only checked-in files, and META.json is not checked in but generated from META.json.in.

v1.4.1 fixes an issue where git archive was never actually used to build a release zip archive. This changed at some point without noticing due to the introduction of the safe.directory configuration in recent versions of Git. Inside the container the directory was never trusted, and the pgxn-bundle command caught the error, decided it wasn’t working with a Git repository, and used the zip command, instead.

As a result, a number of recent releases have included files they shouldn’t, such as the contents of the .git directory. The fix disables safe.directory so that the repository directory is always trusted inside the container, as needed in GitHub actions.

If you use pgxn-bundle in a GitHub workflow, be aware that recent releases (since November 2021 at least) include stuff that it should not, including the .git directory (here’s a list) and patterns excluded in .gitattributes. Your next release should be cleaner.

v1.4.1 also allows the setting of the PROFILE environment variable, so that the default PROFILE=–Werror can be overridden.

v1.4.2 adds (and v1.4.3 improves, see below) git-archive-all to the image, and the GIT_ARCHIVE_CMD environment variable to tell pgxn-bundle which archive command to use, either archive or archive-all. The latter is useful for repositories that use Git submodules and need to include their contents in the release, as demonstrated in this plv8 pull request.

v1.4.2 also adds cmake and the libarchive-tools package to the image. The latter bundles in libarchive tools like bsdtar and bsdcpio, which might be useful for editing Zip files in place.

v1.4.3 passes the –force-submodules option to git-archive-all, because otherwise submodules weren’t included in the bundle.

All of this should just start showing up in your GitHub workflows as long as they use container: pgxn/pgxn-tools or container: pgxn/pgxn-tools@v1.

Questions? Post em in the #extensions channel on the Postgres Slack or to the attention of the PGXN Mastodon bot.

Hey all you Postgres people out there! Just wanted to make a quick post regarding the PGXN twitter bot. In light of the recent announcements by Twitter to charge for API use, I udpated PGXN Manager to also post to Mastodon! If youre have joined the exodus to the Fediverse, give it a follow:

@pgxn@botsin.space

In fact, I made some time to rewrite the notification bits of the manager service. Previously there was just some code jammed into the upload controller that would make an API call to Twitter. I ripped that out, and added a trigger to the distributions table that posts a a LISTEN/NOTIFY message.

Then I wrote a little service that checks for new notifications every 5 seconds and dispatches to one mor more configured consumers. Today there are just two: Twitter and Mastodon; in the future the might be more, especially since I also added triggers to the users and mirrors tables, so we could have the bot announce new mirrors and users. Should be pretty easy to do, might put in the time in the next few weeks.

Oh, and for fun, I also added some emoji to the Mastodon posts, as well as the abstract from new releases. Makes the messages more informative than on Twitter. Of course, we could probably do the same, there, especially since the post length was extended to 280 characters sometime after the original bot. I have in mind to make a little library that allows the customization of messages via configuration.

Just a quick note to say that PGXN Manager has been updated with more secure password storage. Existing passwords are unmodified, but the next time you change your password, it will be upgraded to a more robust password hashing algorithm that’s more resistant to attacks. I recommend everyone update their passwords. If you have a PGXN Manager account, just hit password reset, enter your username or email address, and check your mail for a reset link. Once you update your password, it will be stored in the new, more secure format.

The last couple weeks I’ve returned to PGXN and made a few updates. Nothing huge, but all long overdue.

First up, PGXN Manager is all TSL now. No public HTTP-only site. What started as a convenient division of labor (http for the public site and https for the authenticated site) turned out to be quite irksome — especially since some browsers wouldn’t load the non-TLS site at all anymore. So I did away with it, and now it’s all TLS, authenticated and not. (The API and search sites have been TLS for a while now.)

I also fixed a few long-standing bugs in PGXN Manager, most of which weren’t visible to end-users, but have annoyed me over the years. A few silly server errors, some instances of uploads failing for anything other than zip files, that sort of thing.

Oh, and if you’re a extension author, PGXN Manger now allows updates to old distribution versions to be uploaded. Previously it only allowed a new version to be greater than all previous versions. Now it will allow a new X.Y.Z version if X.Y previously existed and the new .Z is greater, and a new X.Y version if X previously existed and the new .Y is greater than any previous X.Y. To get this to work properly, I also dropped the check for versions of extensions in the uploaded files. It would just be too complicated to add a bunch of rules more likely to annoy than not. So it now only enforces patterns for distribution release versions. I trust extension authors not to then lower extension versions on new releases — that would just be silly, and not helpful to your users.

As part of this work, I also revamped the management of the PGXN server. Back in 2010 I wrote Capistrano files to manage the server, but have long ceased to use them, as they ceased to work. I’ve now removed all that detritus from the PGXN Manager, API, and Site repositories, and replaced them all with a single new repository, pgxn-ops, which contains Ansible playbooks to manage all the services. They don’t deal with a lot of the server-side stuff, which depsz handles separately, but they now make it much easier to build and deploy a new release, restart it remotely, manage passwords, etc.

And finally, I’ve updated the PGXN search site. In addition to fixing a few long-standing minor annoyances (borders around images linked in documentation, broken links, etc.), I also updated most of the graphics to be retina-friendly. So it should look a lot sharper on your hi-res screens now. Check it out!

Next up I think I’d like to make the search site more mobile-friendly, and then perhaps I’ll finally go back and attack the terrible search provided by the API. I’ll try to do it in less than five years this time.

I took a little time this summer to finally address some nagging issues in PGXN Manager, the site to which extensions are uploaded and added to the master repository. Most of the changes were internal, improving the interface through which I sometimes have to re-index a release. There are also a couple of minor changes to the sample Makefile in the How To. But the most important change for new releases going forward is that version ordering is now enforced. That means two things:

• Distribution versions must be greater than the version of the previous release. You can no longer release v1.2.0 today and v1.1.0 tomorrow.
• Extension versions must be greater than or equal to versions in previous releases. It’s pretty common to have a new release version but have embedded extensions be the same version as before. But they can’t be any less than before.

The changes have been applied to the reindexing code, as well, which also prevents versions from being greater than in subsequent releases. That won’t come up very often—most of the time, I end up reindexing something that has just been released. But in the future I expect to add an interface for release managers to reindex their own distributions, so it may be that someone reindexes a release that’s a few versions old. Or not. But just in case, it’ll be handled.

So what are you going to release on PGXN today? Get to it!.

Looks like it’s been close to two years since my last post on the PGXN blog. Apologies for that. I’ve thought for a while maybe I should organize an “extension of the week” series or something. Would there be interest in such a thing?

Meanwhile, I’m finally getting back to posting to report on a fun thing you can now do with your PGXN distributions. Thanks to the Version Badge service from the nice folks at Gemfury, you can badge your distributions! Badges look like this:

You’ve no doubt seem simlar badges for Ruby, Perl, and Python modules. Now the fun comes to PGXN. Want in? Assuming you have a disribution named pgfoo, just put code like this into the README file:

[![PGXN version](https://badge.fury.io/pg/pgfoo.svg)](https://badge.fury.io/pg/pgfoo)

This is Markdown format; use the syntax appropriate to your preferred README format to get the badg to show up on GitHub and PGXN.

That’s it! The badge will show the current releases version on PGXN, and the button will link through to PGXN.

Use Travis CI? You can badge your build status, too, as I’ve done for pgTAP, like this:

[![Build Status](https://travis-ci.org/theory/pgtap.png)](https://travis-ci.org/theory/pgtap)

Coveralls provides patches, too. I’ve used them for Sqitch, though I’ve not yet taken the time figure out how to do coverage testing with PostgreSQL extensions. If you have, you can badge your current coverage like so:

[![Coverage Status](https://coveralls.io/repos/theory/sqitch/badge.svg)](https://coveralls.io/r/theory/sqitch)

So get badging, and show off your PGXN distributions GitHub and elsewhere!