March 2nd, 2011 by depesz | Tags: , , , , , , , | No comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

On 8th of February, Tom Lane committed patch:

Core support for "extensions", which are packages of SQL objects.
 
This patch adds the server infrastructure to support extensions.
There is still one significant loose end, namely how to make it play nice
with pg_upgrade, so I am not yet committing the changes that would make
all the contrib modules depend on this feature.
 
In passing, fix a disturbingly large amount of breakage in
AlterObjectNamespace() and callers.
 
Dimitri Fontaine, reviewed by Anssi Kääriäinen,
Itagaki Takahiro, Tom Lane, and numerous others

Extensions are a way to group various database objects together to allow easy manipulation.

For starters – all contrib modules became extensions.

This means that installing and changing them is now much simpler.

For example, adding ltree contrib module to database is now as simple as:

$ select '1.1'::ltree;
ERROR: type "ltree" does not exist
LINE 1: select '1.1'::ltree;
^

$ create EXTENSION ltree;
CREATE EXTENSION

$ select '1.1'::ltree;
ltree
-------
1.1
(1 row)

Removing it is also trivial:

$ drop EXTENSION ltree;
DROP EXTENSION

But the great stuff is that you can easily load the extension to different schema, and after loading you can easily alter it.

Let's see how it works:

$ create extension ltree;
CREATE EXTENSION
 
$ create table test (z ltree);
CREATE TABLE

OK. So I have table with column of ltree datatype. But now I decided, I don't want ltree-related functions “polluting" public schema, and I want to move it to designated schema just for it:

$ create schema ltree;
CREATE SCHEMA
 
$ alter extension ltree set schema ltree;
ALTER EXTENSION

Table got modified too:

$ \d test
Table "public.test"
Column | Type | Modifiers
--------+-------------+-----------
z | ltree.ltree |

While this might not seem like a big deal, in fact it is.

Having all objects grouped together means that you can upgrade them, and dump/restore is easier. As instead of dumping all functions/tables/types definitions, dump contains only:

=$ pg_dump | grep ltree
-- Name: ltree; Type: SCHEMA; Schema: -; Owner: depesz
CREATE SCHEMA ltree;
ALTER SCHEMA ltree OWNER TO depesz;
-- Name: ltree; Type: EXTENSION; Schema: -; Owner:
CREATE EXTENSION ltree WITH SCHEMA ltree;
-- Name: EXTENSION ltree; Type: COMMENT; Schema: -; Owner:
COMMENT ON EXTENSION ltree IS 'data type for hierarchical tree-like structures';
z ltree.ltree

Benefit of this will be understood by anyone who ever tried to load dump made on some PostgreSQL to other PostgreSQL, when there were loaded extensions (modules), and paths did change. Or new version of contrib module didn't provide the same functions (some new, some old disappeared).

Writing extensions looks trivial, so I assume that we will see migration of pgFoundry projects to extensions approach soon.

Leave a comment