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!
As a 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 PGCON in
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
been to 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.
Also the 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?
The user defined function will get some information from magic variables
TG_EVENT and such. That allows easier integration of future
information we want to add, without disrupting those existing triggers that
you wrote (no
API change), at the cost of having to write a specific
integration per procedural language.
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 event, 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
Publishing the 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
the 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.
So yes, event triggers first use case for me is in relation with extensions.
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 dump and restore.
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 user
data, or even those that are all about them. The main difficulty here is
that our current design of dump and 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: PGQ.
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!