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.

by Dickson S. Guedes

Do you ever have problems with copy and paste? I often did, and that is why I create custom templates for often used files that match certain patterns.

With files from the structure of PostgreSQL’s extensions was the same thing.

I was tired of creating the files and edit the META, controlfile, READMEs, etc. every time I start a new extension and felt that I need something that made me more productive, so I decided to create an automatic generator and share it with the world.

I called it pgxn-utils, and you should give it a try: it is easy to install, easy to use and will help you to start hacking quickly!

How?

1. First install it:

   gem install pgxn_utils
   

2. Then start a new extension:

   pgxn_utils skeleton my_cool_extension
   

Thats all! It will create the initial skeleton for you and you can start coding! But, if you don’t want to install it, see it in action

Good hack!

I updated the howto yesterday. This document explains how to create a PGXN distribution. If you’re interested in releasing PostgreSQL extensions on PGXN, this document is worth a read.

In essence, it’s really simple: Just create a META.json and upload. But to get the full benefit, there are quite a few other recommendations. Already familiar with it? Here’s the checklist:

• Create a META.json
• Create a control file
• Create a Makefile
• Implement the code in the sql and src directories
• Write tests in the test directory
• Write a Text::Markup-recognizable README
• Write Text::Markup-recognizable documentation in the doc directory
• Consider including other files: Changes, LICENSE, INSTALL, COPYING, AUTHORS
• Release it!

Be sure to read the howto for details. Got feedback or suggestions? Leave a comment!

Oh, and check out pgxn-utils and simplify your extension-development life.

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.

Following the upload of pair to PGXN, I wanted to take a few minutes to write about how to structure a PGXN distribution.

OMG Distribution WTF?

First of all, what is a “distribution” in the PGXN sense? Basically, it’s a collection of one or more PostgreSQL extensions. That’s it.

So why allow more than one extension? Maybe no PGXN distribution will ever have more than one extension. After all, the goal should be many focused, minimalist tools that folks can combine in their apps. But sometimes it doesn’t work out that way.

As an example, I’ve been planning to break pgTAP up into two parts for a while: one for scalar and relational testing, the other for schema testing. Often one needs only the scalar and relational testing, while the schema testing is more often needed only for testing replication and whatnot. Whether or not I choose to distribute both parts in one package I have yet to determine, but it could well make sense to keep them in one distribution.

Besides, I’ve tried to write PGXN::Manager in such a way that it’s not specific to PostgreSQL. So that if someone wanted to create a Drupal XN with it or something, they could. Or PyXN. Or, hell, even if CPAN wanted to switch someday, they could. (Note that I’ve registered myxn.org for fun. I may or may not do anything with that, but see MyTAP. Yes, I am insane.)

That’s So Meta

Anyway, back to the structure of distributions. At its simplest, the only thing PGXN requires is a single file, META.json, which describes the package. This is (currently) the only file that PGXN Manager uses to index a distribution, so it’s important to get it right. The PGXN Meta Spec has a rather complete example of a hypothetical pgTAP distribution META.json.

If you have only one .sql file for your extension and it’s the same name as the distribution (and I expect this would be common), then you can make it pretty simple. For example, the pair distribution has only one SQL file. So the META.json could be:

{
   "name": "pair",
   "abstract": "A key/value pair data type",
   "version": "0.1.0",
   "maintainer": "David E. Wheeler <david@justatheory.com>",
   "license": "postgresql",
   "meta-spec": {
      "version": "1.0.0",
      "url": "http://pgxn.org/meta/spec.txt"
   },
}

That’s it. The only thing that may not be obvious from this example is that all version numbers in a META.json must be semantic versions. If they’re not, PGXN will make them so. So “1.2” will become “1.2.0”, and so would “1.02”. So do try to use semantic versions and not worry about it.

In the short run, you won’t need anything more in your META.json file. But once I get to creating the search site and the command-line client for PGXN, you’re probably going to want to do more. Other useful keys to include are:

• tags: An array of tags to associate with a distribution. Will help with searching.
• prereqs: A list of prerequisite extensions or PostgreSQL contrib moules (or PostgreSQL itself).
• provides: A list of included extensions. Useful if you have more than one or the one has a different name that the distribution (silly, but it happens). It also will index such extension names such that you are the owner, if you’re the first to update one with that name.
• release_status: To label a distribution as “stable,” “unstable,” or “testing.” Useful for uploading distributions for people to test but that clients won’t install by default.
• resources: A list of related links, such as to an SCM repository or bug tracker. The search site will output these links.

Have a look at the META.json in the pair distribution for a more extended example.

New Order

For PGXN, the general idea is that you’ll use PGXS to create your PostgreSQL extensions. I’m hoping to encourage a slight modification of the directory layout for PGXN distributions, but as I hope I’ve made clear so far, PGXN itself doesn’t really care how you structure things, or if you use PGXS. That said, the proposed download and installation client will assume the use of PGXS (unless and until the PostgreSQL core adds some other kind of extension-building support), so it’s probably the best choice.

Most PGXS-powered distributions have the code files in the main directory, with documentation in a README.extension_name file. What I’d like to see instead, and will encourage via the forthcoming search site, is that things be organized into subdirectories:

• src for any C source code files
• sql for SQL source files. These usually are responsible for installing an extension into a database
• doc for documentation files (the search site will likely look there for Markdown, Textile, HTML, and other document formats)
• test for tests

I’ve tried to make the pair distribution a good example of this. To make it all work, The Makefile is written like so:

DATA = sql/pair.sql sql/uninstall_pair.sql
TESTS = $(wildcard test/sql/*.sql)
REGRESS = $(patsubst test/sql/%.sql,%,$(TESTS))
REGRESS_OPTS = --inputdir=test
DOCS = doc/pair.txt

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

The DATA variable identifies the files containing the extension, while TESTS loads a list of all the tests, which are in the test/sql directory. Note that I’m using pg_regress for tests. It expects that tests be named and that there be corresponding “expected” files to compare against. With the REGRESS_OPTS = --inputdir=test line, I’m telling pg_regess to find the test files in test/sql and the expected output files in test/expected. And finally, the DOCS variable points to a single file with the documentation, doc/pair.txt. If this extension had required any C code (like pgTAP or PostGIS do), I would have pointed the MODULES variable at files in a src directory.

After that we just have build instructions. If called with make NO_PGXS=1, it assumes that the unzipped distribution directory has been put in the “contrib” directory of the PostgreSQL source tree used to build PostgreSQL. That’s probably only important if one is installing on PostgreSQL 8.1 or lower. Otherwise, it assumes a plain make and uses the pg_config in your path to find PGXS to do the build.

For more on PostgreSQL extension building support, please consult the documentation.

Zip Me Up

Once you’ve got your extension developed and well-tested, and your distribution just right and the META.json file all proof-read and solid, it’s time to upload the distribution to PGXN. What you want to do is to zip it up to create a distribution archive. Here’s what I did for pair, exporting it from Git:

git checkout-index -af --prefix ~/Desktop/pair-0.1.0/
cd ~/Desktop/
rm pair-0.1.0/.gitignore
zip -r pair-0.1.0.zip pair-0.1.0

Then the pair-0.1.0.zip file was ready to upload. Simple, eh?

Now, one can upload any kind of archive file to PGXN, including a tarball, or bzip2…um…ball? Basically, any kind of archive format recognized by Archive::Extract. You can upload a .pgz if you like, in which case PGXN will assume that it’s a zip file. A zip file is best because then PGXN::Manager won’t have to rewrite it. It’s also preferable that everything be unpacked from an archive into a directory with the name $distribution-$version. If not, PGXN will rewrite it to do so. But it saves the server some effort if all it has to do is move a .zip file that’s properly formatted, so it would be appreciated if you would upload stuff that’s already nicely formatted for distribution in a zip archive.

Release It!

And that’s it! Not too bad, eh? Just please do be very careful cutting and pasting examples; I initially uploaded the pair distribution thinking that it contained pgTAP. It was kind of a PITA to fix. Hopefully we’ll be able to build things up to the point where a lot of this stuff can be automated (especially the creation of the META.json), but for now it’s done by hand. So be careful out there, and good luck!

Oh, and if you have an extension that you’d like to release on PGXN now, I am running a limited beta for interested extension developers. Please hit the mail list for the details to be posted shortly.