Traditionally, folks have created extensions for PostgreSQL by copying one of the contrib modules and hacking it into something new. One of the things that comes along for the ride is the Makefile (example). As a result, there are a lot of third-party extensions that use the USE_PGXS variable.

A bit of background. The core contrib extensions generally rely on a relative path to include the core Makefiles needed to build the extension. Because they ship with the core distribution, they can generally expect that the core has already been compiled, the necessary Makefiles have been created, and that they should be built against them. All the assumptions are that the extensions should be built against the source tree in which they are distributed. So there is no need to use pg_config to find PGXS; it already knows where to find what it needs.

But as extensions, there is still the possibility that one might want to build them against an existing installation of PostgreSQL, or an older version than the source with which they’re distributed. So the core hackers provided the USE_PGXS variable so that one can in effect tell make, “Don’t build against the local source tree, but find PGXS for some other install and build against that, instead.” It was expected to be exceptional, since most folks would build against the local source tree, and not a big deal to make anyone else build it with:

make USE_PGXS=1

Today things are different. There is a growing ecosystem of third party extensions on PGXN, pgFoundry, GitHub, and Bitbucket, and obviously they’re not distributed with the PostgreSQL core. For these extensions, there is no surrounding PostgreSQL source code to automatically include, so they must use pg_config to find PGXS in order build.

Yet, there are quite a few third-party extensions that nevertheless assume that they are in the contrib directory of the PostgreSQL source code distribution, and so still have the USE_PGXS variable. The twitter_ftw 1.0.0 Makefile is a recent example. Just like core extensions, it has this code:

ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
else
subdir = contrib/twitter_fdw
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif

Because Hitoshi-san originally copied the Makefile from a core extension, it still assumes it will be distributed in core by default. And as I said, there are quite a few third-party extensions that exhibit this pattern.

And now the PSA: Please don’t use USE_PGXS in PostgreSQL extension Makefiles.

Not only is it unnecessary, it makes no sense for third-party extensions. They should always assume that they need to use pg_config to find PGXS. If you have an extension Makefile with USE_PGXS like twitter_ftw 1.0.0 did, you should change it to something like this (as Hitoshi-san did in the twitter_ftw 1.0.1 Makefile):

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

That’s it. I am asking you to make your Makefile simpler.

---

An Aside

There is one situation in which you might need to include the core contrib Makefiles. And it’s pretty unusual. If you need to support PostgreSQL 8.1 or earlier, pg_config will not be able to tell you where to find PGXS. So users will have to copy the exension source directory into the PostgreSQL source contrib/ directory and build from there. They will need a way to tell make not to use PGXS. In this one unusual case, I suggest you add a NO_PGXS variable. pgTAP’s Makefile provides an example. But honestly, very few extensions need to support PostgreSQL 8.1 (the oldest release currently supported by the core hackers is 8.3!), so make use of this pattern only if absolutely necessary.

Otherwise, please don’t use USE_PGXS.

---

If you want a complete guide to creating your extension Makefile, have a look at the PGXN Howto, which includes some detailed examples that include support for pre- and post-CREATE EXTENSION support. The PGXS docs contain additional details about all the Makefile variables you can use to simplify extension configuration and installation. Check ’em out.

In order to keep distribution packaging as simple as possible, I worked up this Makefile some time ago:

DATA = $(wildcard sql/*.sql)
DOCS = $(wildcard doc/*.txt)
TESTS = $(wildcard test/sql/*.sql)
REGRESS = $(patsubst test/sql/%.sql,%,$(TESTS))
REGRESS_OPTS = --inputdir=test --load-language=plpgsql
MODULES = $(patsubst %.c,%,$(wildcard src/*.c))

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

The nice thing about this code is that it has nothing specific to a distribution in it. It figures out what SQL files there are, what doc files there are, and what C files need compiling by just looking in the sql, doc, and src directories, respectively. It also specifies that tests are in the test directory. About the only thing I’ve customized here is adding --load-language=plpgsql to REGRESS_OPTS, as the tests for the distribution I’ve copied this from require PL/pgSQL to run. Simple, and anyone can use it with very little need to tweak it, as long as they don’t mind storing their files in the specified directories.

Today, I’m updating my distributions to support PostgreSQL 9.1’s new CREATE EXTENSION syntax, but I want to continue supporting older versions of PostgreSQL, as well. Basically, this means that the files listed in the DATA variable vary based on the version of PostgreSQL you’re installing against. Here are the additional things the Makefile needs to do:

• If installing against PostgreSQL less than version 9.1, exclude files in DATA that contain --. Such files are are migration scripts, which aren’t supported before 9.1.
• If installing against PostgreSQL greater than or equal to 9.1:
  • Copy the files sql/$EXTENSION.sql and sql/$EXTENSION--unpackaged.sql to sql/$EXTENSION--$EXTVERSION.sql and sql/$EXTENSION--npackated--$EXTVERSION.sql, respectively. CREATE EXTENSION requires that the version string be in migration file name. I’d rather not have to rename the file in my repository before every release (and I’d rather keep it without the version for < 9.1 anyway), so it needs to be copied.
  • Add the new sql/$EXTENSION--$VERSION.sql file to EXTRA_CLEAN.
  • Include only files in DATA that contain --. There’s no need to install the original file without the version number, or any uninstall file, either, since it’s not needed on 9.1 anymore.

I’ve been trying to figure out how to modify my standard Makefile to support these changes, without requiring a lot of tweaking, so that other folks can easily use it in the future. Thanks to help from Andrew Dunstan, this is what I’ve come up with:

EXTENSION=semver
EXTVERSION=0.2.2

DATA = $(filter-out $(wildcard sql/*--*.sql),$(wildcard sql/*.sql))
DOCS = $(wildcard doc/*.txt)
TESTS = $(wildcard test/sql/*.sql)
REGRESS = $(patsubst test/sql/%.sql,%,$(TESTS))
REGRESS_OPTS = --inputdir=test --load-language=plpgsql
MODULES = $(patsubst %.c,%,$(wildcard src/*.c))

PG_CONFIG = pg_config

VERSION     = $(shell $(PG_CONFIG) --version | awk '{print $$2}')
PGVER_MAJOR = $(shell echo $(VERSION) | awk -F. '{ print ($$1 + 0) }')
PGVER_MINOR = $(shell echo $(VERSION) | awk -F. '{ print ($$2 + 0) }')

ifeq ($(PGVER_MAJOR), 9)
ifneq ($(PGVER_MINOR), 0)
all: sql/$(EXTENSION)--$(EXTVERSION).sql sql/$(EXTENSION)--unpackaged--$(EXTVERSION).sql

sql/$(EXTENSION)--$(EXTVERSION).sql: sql/$(EXTENSION).sql
    cp $< $@

sql/$(EXTENSION)--unpackaged--$(EXTVERSION).sql: sql/$(EXTENSION)--unpackaged.sql
    cp $< $@

DATA = $(filter-out sql/$(EXTENSION)--unpackaged.sql,$(wildcard sql/*--*.sql)) sql/$(EXTENSION)--$(EXTVERSION).sql
EXTRA_CLEAN = sql/$(EXTENSION)--$(EXTVERSION).sql sql/$(EXTENSION)--unpackaged--$(EXTVERSION).sql
endif
endif

PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

This is not exactly ideal, but not too bad. It’s not quite the drop-in version we had before, because now the first line needs to name the extension we’re distributing, and the second needs to specify the version (and would then need to be updated for every release). Maybe they could be read from the control file somehow? Other than that, you should be able to just forget the rest of the file (mostly). Here’s how it addresses the above requirements:

• To exclude files with -- in them on < 9.1, the first DATA line filters them out:

  DATA = $(filter-out $(wildcard sql/*--*.sql),$(wildcard sql/*.sql))
  

• Next, we need to know if we’re on 9.1 or higher. So we use pg_config --version to get the version number and some awk stuff to get the major and minor parts. Then, if the major version is 9 and the minor is not 0, we:

  • Add sql/$(EXTENSION)--$(EXTVERSION).sql and sql/$(EXTENSION)--unpackaged--$(EXTVERSION).sql as dependencies of the all rule (which is the default PGXS rule).
  • Add the sql/$(EXTENSION)--$(EXTVERSION).sql and sql/$(EXTENSION)--unpackated--$(EXTVERSION).sql rules, which copy sql/$(EXTVERVERSION).sql and sql/$(EXTVERVERSION)--unpackaged.sql files. Of course this assumes that such files exist.
  • Add sql/$(EXTENSION)--$(EXTVERSION).sql and sql/$(EXTENSION)--unpackaged--$(EXTVERSION).sql to EXTRA_CLEAN, so that they’ll be deleted by make clean.
  • Set DATA again, this time to include only files with -- in them, except for sql/$(EXTENSION)--unpackaged.sql.

And with that, it works. But it has some disadvantages over the previous, very simple Makefile I’ve been using up to now:

• One must modify it for every release, if only to set EXTVERSION.
• It relies more on Unix tools than previously, specifically awk. I’m not sure how big a deal this is in practice; the pgTAP Makefile has been relying on awk an even worse gymnastics for some time and no one has complained.
• It’s not perfectly future-proof. It will need to be modified when PostgreSQL 10.0.0 is released, and again when 11.0.0 is released.
• It won’t handle a distribution with multiple extension in it. I don’t even want to think about that.

Suggestions for ways to eliminate these shortcomings would be greatly appreciated, especially if it allows use extension authors to get back to something as simple as the first example at the top of this post.

UPDATE: Added the unpackaged bits I didn’t realize I needed until after I’d released a new version of semver and discovered that the “unpackaged” script needs to always be tied to the default version.

I’m making good progress on PGXN Manager. Hopefully I can start alpha testing it next week. As I mentioned previously, I had estimated 40 hours of work to create it, but was hoping to get it done in around 30 (because I spent 10 extra hours on the database design). So far I’m at 23 hours, so it’s looking pretty good.

Architecturally, I’ve gone for a very minimal Plack-based app. No Catalyst, Jifty, or even Dancer. Just a very simple Plack app that uses Router::Simple::Sinatraish to route URIs to the appropriate controller actions (which are just class methods). The controller just dispatches to Template::Declare-based templates for the HTML rendering. I guess I’ve kind of created my own framework here, but really, there ain’t much to it. This app is simple enough that I couldn’t see the use of adding all the overhead of a framework.

Meanwhile, I’ve been hacking on the distributon class. This will be the core class of the app. It takes a Plack upload object and a username and does all the rest, analyzing an uploaded archive, normalizing it if necessary, registering it with the database, and indexing it by updating all the appropriate JSON files on the mirrors. It’s nearly finished, but I have one other thing to do in the database, first.

In my last update, I asked for advice on whether or not PGXN should allow an extension with the same version number to appear in multiple distributions. And thanks to a comment from Aristotle, I’m changing it to allow that. But it also means that my original extension json spec needs to change.

Here’s an example of what I’m thinking. Say that there are three versions of an extension named “trip”, and that they appear in distributions as follows:

trip 0.2.6
  pair-0.3.0

trip 0.2.5
  trip-0.2.2
  pair-0.2.2rc
  pair-0.2.1
  trip-0.1.1

trip 0.2.4
  pair-0.1.1rc
  pair-0.1.0

So sometimes it’s in the “trip” distribution and other times it’s in the “pair” distribution. My thought is that, for a given version, it would list the distributions it’s in in reverse chronological order (by upload date). So the format would be:

{
    "latest": "stable",
    "stable": { "dist": "pair", "version": "0.3.0" },
    "testing": { "dist": "pair", "version": "0.2.2rc" },
    "distributions": {
       "0.2.6": [
          { "dist": "pair", "version": "0.3.0" }
       ],
       "0.2.5": [
          { "dist": "trip", "version": "0.2.2" },
          { "dist": "pair", "version": "0.2.2rc", "status": "testing" },
          { "dist": "pair", "version": "0.2.1" },
          { "dist": "trip", "version": "0.1.1" }
       ],
       "0.2.4": [
          { "dist": "pair", "version": "0.1.1rc", "status": "testing" },
          { "dist": "pair", "version": "0.1.0" }
       ]
    }
}

This way, every distribution it’s included in is listed, and clients can quickly tell where to find the latest stable, testing, and unstable versions, and which of those is the most recent. This is a bit more convoluted than the original, but I think is a good choice, in that it’s comprehensive but also easy to figure out what’s the latest.

Unless you can think of a better format, this is what I’m going with. Comments?

Look for a post next week announcing an alpha program!