My presentation at the 2012 PDXPUG PGDay. It covers the basics of how to create useful extensions to PostgreSQL and distribute them on PGXN—without needing to learn C.

I’ve been working on my PostgreSQL Conference West presentation, which heavily features PGXN, of course. I think it’s looking good. If you’re at PGWest or are in the San Francisco area and free, come see the talk! Should be a good introduction to creating PostgreSQL extensions and distributing them on PGXN. The latest bit I added is a section on the modifications needed to support the forthcoming CREATE EXTENSION support slated for 9.1. Fortunately it’s dead simple, and will make dealing with extensions in the database a lot simpler, administratively. Really looking forward to that. Of course I’ll post slides once the talk is over.

As part of preparing for the talk, and because there isn’t currently much to actually see of PGXN, I’ve been mocking up the layout for the new search site, which as you know from the status page is the next part of the project I’m slated to work on. I’ve been committing the mockps to the gh-pages branch of the repository, which means you can see what it looks like live on the net right here. That’s the home page, including our sponsor links and tag cloud. Click the “PGXN Search” button to see a mockup of search results (or get them here). Click on any search result to see the mockup of a documentation page (or link it here). The design is based on the lazydays open-source Web design, and I’m quite happy with it. Your thoughts?

As this comes together, I’m gearing up to start hacking on the app to produce the search site. At this point, I’m thinking that it would become the new home page for PGXN, rather than a separate search.pgxn.org site. Thoughts?

I’ll post the slides tomorrow.

I’m thinking about how to add support for reserved extensions. These are extensions that one needs to depend on, but aren’t distributed via PGXN. Primarily, this means stuff distributed with the PostgreSQL core, including:

• PostgreSQL itself
• Bundled procedural languages (plpgsql, plperl, etc.)
• All contributed modules

Basically, anything that an extension might want to declare as a dependency, but that isn’t on PGXN itself.

There are a number of ways to do this. Which do you think would be the best approach?

1. Hard-code a list into the source code
2. Include an editable list in the runtime configuration file
3. Add a database table reserved for them and an API to edit it
4. Create a “pgdev” user and upload a distribution declaring all those extensions in a META.json file
5. Same as 4, but actually upload the PostgreSQL source

I’m leaning towards #2, perhaps having it automatically maintain a list in the database and a metdata file on the mirrors.

But what do you think? Opinions wanted!

I spent most of the time I had to work on PGXN the last two weeks creating the database for PGXN Manager. I had estimated 24 hours of work to design the database. So far I’ve logged 34 hours. But I think that will come out in the wash, really, because I did a lot of work to generate JSON from database functions. This is code I had originally expected to do in the app layer. I’m starting work on that this week, with an estimated 40 hours. Hope I can do it in 30. :-)

I’m pretty happy with how the database API is turning out. See the nifty database API documentation I whipped up using gendoc and embedded MultiMarkdown. There are still a few tweaks I need to make. I just checked in a change to eliminate all the ALIASes I blogged about last week. Turns out that one can just function-name-qualify the function parameter names. Who knew? I sure didn’t.

But I do have a question for you, dear readers. Right now, the database requires that extensions have unique version numbers in every distribution. So if, for example, you uploaded the distribution foo 1.2.2 with the extension bar 1.2.2, when you next uploaded foo 1.2.3, bar could not be 1.2.2. I designed this this way by following my own practice of always incrementing the version numbers of all modules included in my CPAN distributions. So all modules have unique version numbers, with never a duplicate.

However, CPAN itself only cares that distributions have unique version numbers. It doesn’t care about the version numbers of included modules. See, for example, HTML::Mason. Note that the various included modules have all sorts of different version numbers, and many have no version numbers at all. So while the core module has a version number (1.45 at the time of this writing), if you click to look at older versions, say Mason 1.44, you’ll see that no other modules have their version numbers changed.

I don’t think I want to allow extensions without version numbers on PGXN. That’s been a recipe for many annoyances with CPAN. But maybe I should allow extension version numbers to repeat? The upside is less maintenance for multi-extension distribution authors. The downside is potentially less accuracy in the determination of prerequisites.

For example, say that we have two versions of distribution foo:

Distribution	Extensions
foo 1.2.2	foo 1.2.2 bar 1.2.2
foo 1.2.3	foo 1.2.3 bar 1.2.2

Note how the version number of extension bar has not changed between releases. But if I was a user of both foo and bar, and I specified that I required bar 1.2.2 but was actually using stuff in foo 1.2.3, I could end up with errors because I would only have foo 1.2.3.

This is an issue periodically faced by Perl hackers. I might require HTML::Mason::ApacheHandler 1.69 but really what I need is HTML::Mason 1.44. Of course, as a Perl hacker, it’s my responsibility to make sure I require exactly what I need, but sometimes it’s not clear what’s the primary module in a distribution. And if I don’t realize that the version number of HTML::Mason::ApacheHandler doesn’t change with every release, I might make mistakes.

Maybe that’s okay. Maybe PGXN should be less rigid like this. But I’m leaning toward keeping things as they are and requiring new versions of every extension in every upload of a distribution. It’s a bit tougher for those (few?) hackers who will create multi-extension distributions, but probably more reliable for everyone else.

What do you think?

Anyway, I’m getting to work on the Web app this week while this issue percolates. Been studying up on Plack. So nice!