#pgxn

Quick announcement to say that I've replaced the ancient markdown parser with a new one, discount, which supports tables, code fences, definition lists, and more. I reindexed pg_clickhouse this morning and it's sooo nice to see the table properly formatted.

New uploads will use this parser for Markdown (but not MultiMarkdown) files from now on. I think I'll start working on reindexing all existing extensions, too. Give me a holler if you don't see an improvement in your extensions in the next few days.

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

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!

by Dickson S. Guedes

Hi everybody!

I'm proud to tell you that a new version of PGXN Utils was released with this new features:

Git support

Templates and custom templates

PGXN Client integration

Git support

You can start a new extension with or without version control. By default pgxn-utils supports git but it will not create a repository unless you use --git option in the skeleton task.

You can try:

$ pgxn-utils skeleton my_cool_versioned_extension --git

When you create a new extension with git support in addition to creating the skeleton, pgxn-utils will initialize a git repository and create the initial commit.

Once you have your extension in a git repository your bundle will use only the committed files to create the archive, but if your repository is dirty then pgxn-utils will suggest that you to commit or stash your changes before bundling.

You must be careful with new files not added to repository, because they will not be archived.

Default templates

There are three default templates: sql, c and fdw. If you call skeleton without specifying a template, sql is the default. But if your extension will supply some C modules or you will create a FDW, you can create the extension calling skeleton with a --template option.

Try:

$ pgxn-utils skeleton my_cool_c_extension --template=c $ pgxn-utils skeleton my_cool_fdw_extension --template=fdw

The templates contain example code and some links to PostgreSQL documentation that will try to help you to start coding. SQL and C templates contains some test examples, and the example code will compile and pass make installcheck. However, this code is intended to be an example, and you must write your own tests and code.

Custom templates

If you don't like the templates provided by pgxn-utils you can create you own. Just create a directory with at least a META.json or META.json.tt file and then use your directory as argument to the --template option.

To know how create your own template, see the examples in the templates directory.

PGXN Client integration

If you have PGXN client installed you can change the command line from pgxn-utils some_task to pgxn some_task and this will save you some typing.

See:

$ cd /tmp $ pgxn skeleton --help PGXN Utils version: 0.1.4 Usage: pgxn skeleton extension_name Options: [--git] # Initialize a git repository after create the extension -a, [--abstract=ABSTRACT] # Defines a short description to abstract -p, [--target=TARGET] # Define the target directory # Default: . [--template=TEMPLATE] # The template that will be used to create the extension. Expected values are: sql, c, fdw # Default: sql -r, [--release-status=RELEASE_STATUS] # Initial extension's release status -d, [--description=DESCRIPTION] # A long text that contains more information about extension -b, [--generated-by=GENERATED_BY] # Name of extension's generator -l, [--license=LICENSE] # The extension license -t, [--tags=one two three] # Defines extension's tags -v, [--version=VERSION] # Initial version -m, [--maintainer=MAINTAINER] # Maintainer's name <maintainer@email> Creates an extension skeleton in current directory $ pgxn skeleton test create test create test/test.control create test/.gitignore create test/.template create test/META.json create test/Makefile create test/README.md create test/doc/test.md create test/sql/test.sql create test/sql/uninstall_test.sql create test/test/expected/base.out create test/test/sql/base.sql $ cd test/ $ pgxn bundle run make distclean from "." create /tmp/test-0.0.1.zip