PostgreSQL 9.1 Exploring Extensions   

SELECT 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.

Downloading the packages

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.

• temporal This provides a new data type called period which is composed of two timestamps that define a range. it also includes functions such as period_intersect() that will return another period that defines the intersection of two periods. Similar in concept to PostGIS intersection function.
• kmeans A kmeans window function implemented in C.
• first_last_agg Similar to ourwho's first and last functions, we described but implemented in C.

Compiling them under MingW and making available in PostgreSQL

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.

Using MingW compiled extensions under Windows PostgreSQL VC++ builds

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 functions

Installing the extensions into database

Okay I cheated and started installing them already. To install the extensions, you use PgAdminIII add Extension feature or:

To install in a named schema

To install in default schema

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.