Now updating the API server every five minutes rather than hourly. New uploads will appear on pgxn.org more quickly.

No site feed for uploads, but I just realized that the Twitter feed provides exactly that. http://twitter.com/statuses/user_timeline/pgxn.atom

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

I hope you enjoy this version. “:)

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.

I’ve had a few reports over the last few months that folks uploaded new extensions to PGXN but they failed to show up in search results. For example, as of right now, if you search for “distinct”, you get two results, OmniPITR and pgTAP. This despite the fact that the recently-released adaptive_estimator extension is tagged “distinct”. Author Tomas Vondra reported this issue, and it took me a couple of days to realize why it wasn’t showing up.

Here’s the reason: Only “stable” releases are indexed. The first and second adaptive_estimator releases both have their release status set to “testing”. The PGXN API only indexes stable releases. The idea behind that is that you want most folks to continue using stable releases while you work on testing new versions. So when users search the site, only the latest stable release will appear in search results. Similarly, installing an extension via the PGXN client, prefers the latest stable release by default. If you want the most recent, you have to specify the --unstable or --testing option.

So, the upshot is, if you want your extension to appear in the full text search results on the PGXN, site, release a stable version.

That said, I first noticed this issue a while ago, and filed an issue with the idea that, if an extension is uploaded that is not stable, but there are no stable versions, then the extension should be indexed, anyway. The idea here is that, when you first upload it, you don’t have any existing users to keep on a stable release anyway, because there is no stable release. So perhaps we should go ahead and index it. A non-stable release would only be omitted from the index if there was an existing stable release.

Thoughts?

New version of api.pgxn.org released, with a new index generated. Better parsing of reST docs thanks to @dvarrazzo.

Okay, I think the API sync issue is fixed; omnipitr v0.3.1 now shows up on the site. http://www.pgxn.org/dist/omnipitr/ #phew

here seem to be some issues with the API syncing; will get them fixed today. Sorry for the lack up site updates in the meantime.

Day before yesterday, I finally got all of PGXN moved to a new server. I had been using a small server owned by my company, Kineticode, and hosted by Command Prompt. That was fine for a while, but CMD was needing its rack space back, and what with my new job, I was shutting down Kineticode, too. It was time to move PGXN elsewhere.

For a while, I got a lot of support and assistance towards moving PGXN to a PostgreSQL community server. Dave, Magnus, and Stefan kindly spun up a VM for me, and gave me permission to install Perl modules from CPAN, provided I supply them with a script to report to Nagios when Perl modules were out of date, which of course I did. This was necessary because I built PGXN with some pretty recent versions of CPAN modules that are not yet available in Debian stable. I was looking forward to getting things running and integrating with the community authentication service.

I got the server built, and everything was working reasonably well. Magnus and I were just working out some issues with the proxy server configuration, and I was starting to think about how to migrate the data over. But first, I decided to refactor the Perl module script to use a more efficient implementation. I fired it off and piped its output to the cpan utility to just get everything updated. Unfortunately, unlike my first implementation, which reported only on CPAN-installed modules, this version of the script also reported when Debian-installed modules were out-of-date. And since I have my CPAN build configuration set up to remove previous installations, I upgraded all those modules, replacing them with new versions.

Well, this was a major fuckup on my part. Turns out there’s no simple way to restore Debian-distributed versions of the modules without rebuilding the entire system. Worse, this was exactly the sort of thing the community sysadmins feared. They have to maintain a lot of servers. So they naturally prefer that they all be as similar as possible. The new PGXN server had been mostly similar to what they had before, and Dave and company had been willing to compromise quite a bit to get PGXN going, but I, unfortunately, demonstrated how easy it is to ruin the whole thing.

So we decided that a community server isn’t the right place for PGXN. At least not yet. Perhaps in a year or two the Debian distribution will be updated to have all the prerequisites I need. Better yet, maybe someone create a PGXN debian distribution! (Volunteers welcomed.) Then I won’t have to do anything special and we can try again (without any sudo privileges for me!). But in the meantime, I still needed to move things.

Fortunately, depesz came to the rescue. He has a very nice box hosting his blog, explain.depesz.com, and a few other things, and would I like to set things up there? Depesz used perlbrew to set up a Perl install just for the PGXN system accounts, meaning I could install any Perl modules I needed without interfering with the system Perl. And each account has its own privileges to run the services it needs (Manager, API, Site) without the risk of breaking anything else. A few days after getting access, we had everything set up and ready to go. I pulled the trigger on Monday, and it went of without a hitch.

My thanks to depesz for the server and all the assistance, not to mention his donation! PGXN now has a very nice home where it can mature.

And as for the future, I have some thoughts about that, too.

• I’d like to blog about the migration itself, and how easy it is (and isn’t) to build PGXN.
• There are some bugs to be fixed and minor improvements to be had. Interested in helping out?
• I’d love to hear your ideas about how to improve PGXN. What would make it better? What doesn’t work quite right for you now?

And yes, now that this migration is finally done, I expect I’ll have more time to blog and work on PGXN going foward. Please leave your thoughts and ideas in the comments. This thing is wide open to any kind of idea, and I would greatly appreciate your feedback.

Many thanks to depesz for the new PGXN hosting! http://depesz.com/