One of the new features I'm excited about in upcoming PostgreSQL 9.1 are extensions. It is also my hope that for PostGIS 2.0, we'll be able to package PostGIS 2.0 as an extension. Reinspired in my mission by David Wheeler's recent post and video on Building and Distributing Extensions without C, I decided to take some time to investigate how all the extension pieces fit together.
The three things I like most about extensions are:
Of course the ease is all in the thoughtfulness of the packaging. To get some ideas of how we would go about packaging PostGIS 2.0 as an extension (it could very well be 3 extensions if we decide to package the core postgis, raster, and topology (and even tiger geocoder) as separate extensions), I thought I would take a look at how others have packaged theirs, and how one goes about registering one of these packages to make it available in CREATE EXTENSION.
First I decided to start by doing a little snooping, by applying some lessons from our previous article Querying table, view, column and function descriptions I wrote this query to figure out what useful functions are available to learn about extensions.
SELECT p.proname AS funcname, d.description
FROM pg_proc p
INNER JOIN pg_namespace n ON n.oid = p.pronamespace
LEFT JOIN pg_description As d ON (d.objoid = p.oid )
WHERE n.nspname = 'pg_catalog'
AND (d.description ILIKE '%extension%' or p.proname ILIKE '%extension%')
ORDER BY n.nspname, p.proname ;
-- which output this --
funcname | description
---------------------------------+-----------------------------------------------------
pg_available_extension_versions | list available extension versions
pg_available_extensions | list available extensions
pg_extension_config_dump | flag an extension's table contents to be emitted by pg_dump
pg_extension_update_paths | list an extension's version update pathsSELECT c.relname As tname,c.relkind As type, d.description
FROM pg_class As c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN pg_description As d ON (d.objoid = c.oid AND d.objsubid = 0)
WHERE n.nspname = 'pg_catalog'
AND (d.description ILIKE '%extension%'
OR c.relname ILIKE '%extension%')
ORDER BY n.nspname, c.relname ;
-- which output this --
tname | type | description
---------------------------------+------+-------------
pg_available_extension_versions | v |
pg_available_extensions | v |
pg_extension | r |
pg_extension_name_index | i |
pg_extension_oid_index | i |The most useful of the bunch, as you can probably guess is the pg_available_extensions which outputs something like this on my PostgreSQL 9.1beta3 install:
name | default_version | installed_version | comment --------------------+-----------------+-------------------+---------------------------------------------------------------------- adminpack | 1.0 | | administrative functions for PostgreSQL autoinc | 1.0 | | functions for autoincrementing fields btree_gin | 1.0 | | support for indexing common datatypes in GIN btree_gist | 1.0 | | support for indexing common datatypes in GiST chkpass | 1.0 | | data type for auto-encrypted passwords citext | 1.0 | | data type for case-insensitive character strings cube | 1.0 | | data type for multidimensional cubes dblink | 1.0 | | connect to other PostgreSQL databases from within a database dict_int | 1.0 | | text search dictionary template for integers dict_xsyn | 1.0 | | text search dictionary template for extended synonym processing earthdistance | 1.0 | | calculate great-circle distances on the surface of the Earth file_fdw | 1.0 | | foreign-data wrapper for flat file access fuzzystrmatch | 1.0 | 1.0 | determine similarities and distance between strings hstore | 1.0 | | data type for storing sets of (key, value) pairs insert_username | 1.0 | | functions for tracking who changed a table : :
Now the next question on my mind, is how the heck does an extension get themselves on this list. What I will describe is a somewhat Microsoft Windows focused approach, but much of it is the same as what you would do in a Unix-based system, since I'm compiling under MingW.
While my out of the box PostgreSQL had many extensions, not all are present. Most of the ones listed were contribs of yesteryear.
Many of the newer PostgreSQL extensions can be found at http://www.pgxn.org/.
To get a feel for how this whole new Extension world works, I thought I'd take a stab at compiling a couple and installing them.
I was able to get many of the ones I tried to compile under MingW and run in my 32-bit PostgreSQL 9.1 EnterpriseDb VC++ binaries.
The ones I experimented with do not come packaged with the EnterpriseDB packaged Windows binaries and were relatively painless since they didn't have extra dependencies
beyond what is required by PostgreSQL. Some others I wanted try like the twitter FDW had extra dependencies I didn't have the energy to gather
and the explain analyze one I tried but it failed (though it did create the package, it failed with a plpgsql
error when I went to install it with CREATE EXTENSION). Below are the ones I was successful with.
It should be noted that while these are packaged as extensions, many will compile under lower versions of PostgreSQL like 8.4/9.0 and can be used like any other contrib
in lower versions. You just don't get the syntactic/manageability goodness of the new EXTENSION model.
What I am describing here are my compile steps for MingW, but the steps should be essentially the same on Unix/Linux platforms, the last. Most of the extensions follow the same path, you need postgresql-develop available and path to your pg_config should be in your path (pg_config is generally located in the bin folder of your PostgreSQL install). Since we use mingW to compile PostGIS for various versions of PostgreSQL (8.2-9.1), as described Compiling PostGIS 2.0 under MingW-32 we change our paths accordingly to compile for a different versions of PostgreSQL.
So for example, to compile kmeans, we extracted the folder into C:\projects\postgresql\extensions
export PG_VER=91b3
export PGPORT=5441
export PGUSER=postgres
export PATH="/c/projects/pg/pg${PG_VER}/bin/:$PATH"
cd /c/projects/postgresql/extensions
cd kmeans-1.1.0
make
make install
The make install will install it in your PostgreSQL install and if you were actually using your PostgreSQL compile environment, you'd be done and have the extensions available and listed in pg_available_extensions. However, for production use, we normally use the VC++ compiled PostgreSQL distributed by EnterpriseDb. Locally these seem to work fine under there.
There are two sets of things you need to copy which is where extensions install their stuff. /share/extension (under MingW, this is share/postgresql/extension and the relevant extension dlls in the /lib/ folder in MingW this is lib/postgresql.
Copy these into the lib and share/extension folder of your regular PostgreSQL install and run this query to confirm they are available.
SELECT * FROM pg_available_extensions
WHERE name IN('first_last_agg', 'temporal','kmeans' )
ORDER BY name;
voila!
name | default_version | installed_version | comment
----------------+-----------------+-------------------+-------------------------------------------
first_last_agg | 0.1.1 | 0.1.1 | first() and last() aggregate functions
kmeans | 1.1.0 | | k-means classification by window function
temporal | 0.7.1 | 0.7.1 | temporal data type and functionsOkay I cheated and started installing them already. To install the extensions, you use PgAdminIII add Extension feature or:
To install in a named schema
CREATE EXTENSION kmeans SCHEMA contrib;
To install in default schema
CREATE EXTENSION kmeans;
If there exist multiple versions of an extension, you'll want to use the optional VERSION clause:
CREATE EXTENSION kmeans SCHEMA contrib VERSION 1.1.0;
For those windows users, who are interested in trying out these particular extensions and aren't set up for compiling -- you can download them in postgres91_32bit_extensions.zip. I'm still setting up my 64-bit desktop and hope to have 64-bit equivalents of these running in my 64-bit PostgreSQL 9.0/9.1 installs.