Similar question has been asked many times on mailing lists and on IRC. Sometimes it's not mail sending, but file/directory creation, or something else that generally requires some interaction with “world outside of database".

Can it be done? Sure. How, then?

There are couple of methods, each has its own set of drawbacks and benefits (as always).

The answer most people are looking for (at least initially) is – how to do it from within database, without any kind of additional programs.

To do so, we will need to use stored procedure (function in PostgreSQL). But not just any.

We need something special.

Let's see. In psql, when I do \dL (to list languages) I get list like this:

Trusted languages are named so because you (at least theoretically) cannot interact (using them) with anything outside of database.

So, you cannot use files, sockets. This includes also loading modules in PL/Perl – since modules are in files – you can't load them (well, technically you can, but not within PL/Perl function, you have to do it from postgresql.conf).

So – any language marked as trusted will not work for stuff like sending mails. Simply not possible.

But – there are, so called, untrusted languages.

The very first, not listed above, but working in every Pg instance is “C". This is not a language for stored procedures – it's rather interface to functions written in C (or any other language compilable to .so files), stored somewhere in filesystem, that PostgreSQL backend can load.

While it's great, and provides limitless possibilities, we'd rather use something interpreted, so we could create the functions within SQL itself (like in psql).

Luckily, PostgreSQL has many accessible languages – all depending on compile options, and installed extensions. In base sources for PostgreSQL 9.2 (9.1 too afaik, and in previous versions too), we have:

PL/Perl

PL/PerlU

PL/PythonU

PL/Python2U

PL/Tcl

PL/TclU

PL/pgSQL

As you can imagine – PL/Tcl is using Tcl language, and PL/PythonU, of course, Python. There are many more PL/* languages, available on PGXN or PgFoundryM, but that's not important at the moment.

You might notice that some of the languages have letter “U" at the end of name – these are untrusted languages. Meaning – using them you are not limited in what you can do. So, for example – using open() in PL/Perl will generally fail, but in PL/PerlU will happily work.

You might also notice, that there is no PL/Python – just PL/PythonU (and PL/Python2U, which is (currently) the same thing). Reason is very simple – some time ago developers found out that it is not possible to limit Python functionality in a way that's safe. So PL/Python had to disappear (in 7.4 version of Pg, loooong time ago).

So, let's load the language, and do something that will reach outside of database:

So, I was able to create the extension, but I can't create function using normal user. Reason is very simple – since plperlu is untrusted – only superusers can use it to create functions. And you can't even grant privileges on it:

Does that mean that it's useless? Well, kind of. I stand on position that giving anyone superuser privileges in database equals to giving them shell access. But if you're not so strict – you can create the function using superuser, and then grant privileges to it to other users. Something like this:

notification was delivered only AFTER COMMIT. This is important, because it means that we will not have situations when NOTIFY was sent, but transaction is not yet committed, so the other process cannot see the data.

in psql at least – you have to do some kind of database interaction to get the notification

So – how can we use it to do something actually useful? Let's try to write a simple “daemon" that will simply print info about new users. Since I usually write in Perl, decided to write this (well, mostly copy/paste) in Python – for a change:

What the listening daemon will do – it's up to you. It can query back the database to get full row for new user, or it can send mails, or it can do anything you might want.

Is that perfect solution? Unfortunately no. While PostgreSQL will queue all NOTIFYs that weren't yet handled by all LISTENING processes (you can have many processes doing LISTEN on the same channel (new_user in my example)) – it will not queue them if nothing is listening.

So – if your listener will die – all notifications that happened in the time between its death and restart are gone. Of course this can be mitigated by running (for example) two daemons, and doing some kind of synchronization between them, but it's not perfect, and it's definitely not simple.

Interestingly – there is a whole set of tools that serve this particular case – get some info, and then keep it in queue until someone will take it. Yes – message queues.

Of course you can use general queues like RabbitMQ or ØMQ, but since we're in PostgreSQL, it would be cool to use something that was made for PostgreSQL. Right?

There is queue manager for PostgreSQL – it's called pgq, and it's part of Skype's SkyTools.

It's method of installation, and usage, is pretty well described in docs, and tutorial, so there is not much point in rewriting it here.

At this moment you might say: whoa, so much work just to get some emails sent when new user is created? Is there really nothing simpler? Well, there is. You can write a simple queue yourself.

And, on top of it – just write a simple script that will run as daemon or as cronjob that will check the process_queue, and do whatever is necessary for all rows that are not yet processed.

What's more – you can also use LISTEN/NOTIFY with this approach, so that, with daemon approach, you will have fewer scans of the table. I.e. instead of:

while1:
check_for_new_rows_in_queue()
sleep 5

while 1:
check_for_new_rows_in_queue()
sleep 5

(which scans the process_queue table every 5 seconds)

You will get logic as in python script above, waiting for notification from trigger, and only then checking for new rows. And if the daemon will die – all the not-yet-processed users will be waiting in process_queue, so on restart – just add one more check. Like:

As a final note – personally, I prefer the last option – its simple, can be done with standard PostgreSQL – even in cases when admins do not want to install new languages or extensions, and it “just works".

5 comments

after something changes into certain db table to send immediately an email with attached db report, importable in spreadsheet software – Open Office and keep detailed log about sent email reports.
I did not want to run daemons, because:
1. they tend to quietly die sometimes
2. put some useless burden on the server.
LISTEN and NOTIFY solution seemed too complicated.

Thanks a lot for this tutorial depesz!
I just used this “howto” to notifiy my application when something changes in the database (which then sends out an email ;).
Just wanna say you helped me quite often already with the tutorials on your blog – actually it became my #1 source when I got stuck with pg…
Keep posting! 🙂