PostgreSQL 9.2 is released! It’s an awesome new release that I urge you to
consider trying and adopting, an upgrade from even
9.1 should be very well
worth it, as your hardware could suddenly be able to process a much higher
load. Indeed, better performances mean more work done on the same budget,
that’s the name of the game!
PostgreSQL contributor though, the release of
9.2 mainly means to me
that it’s time to fully concentrate on preparing
9.3. The developement
season of which as already begun, by the way, so some amount of work has
already been done here.
The list of things I want to be working on for that next release is quite long, and looks more like a christmas list than anything else. Let’s only talk about those things I might as well make happen rather than all the things I wish I was able to be delivering in a single release…
9.2 for wanting to include too big a feature in one go, leading to
too many choices to review and take decision about, for once, and also to
some non optimal choices that had to be reconsidered. Thanks to
Ottawa earlier this year, I could meet in person with
Robert Haas and we’ve
been able to decide how to attack that big patch I had. The first step has
commit in the PostgreSQL tree only infrastructure parts, on which we
will be able to build the feature itself.
What we already have today is the ability to run
user defined function when
some event occurs, and an event can only be a
ddl_command_start as of now.
trigger itself must be written in
PL/C, as the support
for the other languages was not included from the patch.
That leaves some work to be done in the next months, right?
user defined function will get some information from
TG_EVENT and such. That allows easier integration of future
information we want to add, without disrupting those existing
you wrote (no
API change), at the cost of having to write a specific
So one of the first things to do now is to take the support for the others
PL that I had in my proposal and make a new patch with only that in there.
Fill-in more information
Then again, this first infrastructure part was all about being actually able
to run a user function and left behind most of the information I would like
the function to have. The information already there is the
command tag, the
event name and the
parsetree that’s only usable if you’re writing your
C, which we expect some users to be doing.
To supplement that, we’re talking about the
Object ID that has been the
target of the
schema it leaves in when applicable, the
Operation that’s running (
being the target of said operation (e.g.
FUNCTION), and the
Publishing the Command String
Command String here is not an easy task, because we have to
rebuild a normalized version of it. Or maybe we can go with passing explicit
context in which the command is running, such as the
Even with an explicit context that would be easy enough to
SET back again
(in a remote server where you would be replicating the
DDL, say), it would
be better to normalize the
command string so as to remove extra spaces and
make it easier to parse and process from a
user defined function.
That part looks like where most of the work is going to happen in the next commit fests.
The other big thing I want to be working on with respect to this feature is
event support, which is basically
hard coded to be
the current state of the
We certainly will want to be able to run user defined function not only at the very beginning of a DDL command, but also just before it finishes so that the newly created object already exists, for example.
We might also be interested into supporting triggers on more than
I doubt we will see that happening in
9.3, as some people in the community
would go crazy about complex use cases. Time is limited, and I think this is
better kept open for the next release, as the way our beloved PostgreSQL
works is by delivering reliable features: quality first.
I’m always happy to hear about use cases for the features I’m working on, and this one has the potential to be covering a non trivial amount of them. I already can think of trigger based replication systems and some integrated extension network facilities. With your help we can give those the place they should have: early days use cases in a great collection.
event triggers first use case for me is in relation with
Surprise! There’s still some more I want to do with
extensions, so much that
I could consider their implementation in
9.1 just an enabler. In
game has been to offer the best support we could design for existing
modules, with a very strong angle toward clean support for
The typical contrib module exports in SQL a list of C coded functions, sometime supporting a new datatype, sometime a set of administration functions. It’s quite rare that contrib modules are handling user data embedded in their SQL definition, and when it happens it’s mostly configuration kind of data, such as with PostGIS.
Now we want to fully support
extensions that are maintaining their own
data, or even those that are all about them. The main difficulty here is
that our current design of
restore support is following a model
where installing the same extesion in a new database is all covered by
create extension foo;. This is a limited model of the reality, that we need
The first manifestation of those problems is in the
SEQUENCE support in
extensions, and that impacts one of my favorite extensions:
PostgreSQL just released an awesome release with
9.2, where we get
tremendous performance optimisations and truly innovative features, such as
RANGE TYPE. How not to consider PostgreSQL as a part of your application
stack, where to develop and host your features.
While users are enjoying the newer release, contributors are already preparing the next one, hard at work again!