While currently too busy at work to deliver much Open Source contributions,
let's debunk an old habit of PostgreSQL extension authors. It's all down to
copy pasting from contrib, and there's no reason to continue doing $libdir
this way ever since 7.4 days.
Let's take an example here, with the prefix extension. This one too will
need some love, but is still behind on my spare time todo list, sorry about
that. So, in the prefix.sql.in we read
CREATE OR REPLACE FUNCTION prefix_range_in(cstring) RETURNS prefix_range AS 'MODULE_PATHNAME' LANGUAGE 'C' IMMUTABLE STRICT;
Two things are to change here. First, the PostgreSQL backend will
understand just fine if you just say AS '$libdir/prefix'. So you have to
know in the sql script the name of the shared object library, but if you do,
you can maintain directly a prefix.sql script instead.
The advantage is that you now can avoid a compatibility problem when you
want to support PostgreSQL from 8.2 to 9.1 in your extension (older than
that and it's no longer supported). You directly ship your script.
For compatibility, you could also use the control file module_pathname
property. But for 9.1 you then have to add a implicit Make rule so that the
script is derived from your .sql.in. And as you are managing several scripts
— so that you can handle versioning and upgrades — it can get hairy (hint,
you need to copy prefix.sql as prefix--1.1.1.sql, then change its name at
next revision, and think about upgrade scripts too). The module_pathname
facility is better to keep for when managing more than a single extension in
the same directory, like the SPI contrib is doing.
Sure, maintaining an extension that targets both antique releases of PostgreSQL and CREATE EXTENSION super-powered one(s) (not yet released) is a little more involved than that. We'll get back to that, as some people are still pioneering the movement.
On my side, I'm working with some debian developer on how to best manage the
packaging of those extensions, and this work could end up as a specialized
policy document and a coordinated team of maintainers for all things
PostgreSQL in debian. This will also give some more steam to the PostgreSQL
effort for debian packages: the idea is to maintain packages for all
supported version (from 8.2 up to soon 9.1), something debian itself can not
commit to.