Background triggers?

Hi guys

A bit of a long first story, hope someone is able to help...

We have got a Ubuntu 8.04 server running Postfix, which reads its
mailing list subscription files, aliases, virtual, domains, etc from a
Postgres database.

This used to work fine when we had only a couple of e-mail distribution
lists. However, recently a lot of new lists have been added and the
system becomes very slow.

Turns out there a couple of very complicated views using other views,
using concatenated strings etc. A SELECT on this view takes about 2.5
seconds. Tuning the resource allocation brought it down to 1.2 seconds.
We have looked at the queries and came to the conclusion that it would
not be feasible to bring this down any further.

The database gets millions of SELECT queries per day, but only a hand
full of UPDATE, DELETE or INSERT queries, which made me think.

I created a trigger that fires on UPDATE, DELETE, or INSERT, and selects
* from the views and puts these calculated results in simple tables.
This way the data is cached, which is very fast of course.

This seems to work fine, but there is a problem. The database with
subscriptions is managed with a (PHP) web interface. Typically, all
actions that are being done with the interface are queries that INSERT,
UPDATE or DELETE. This means that this interface will be very slow
because each action will cause the trigger to fire - calculating * from
views taking 1.2 seconds each.

Ideally I would like the trigger to fire and then do the actual work in
the background, but I found no way of doing this.

So I came to NOTIFY/LISTEN. But because there a several pieces of
software talking to the database, this is not ideal either.

In the end we created a trigger that inserts NOW() into in a table
whenever one of the source tables gets a INSERT, UPDATE or DELETE. If
so, it runs commands to refresh the content of the cache tables.
The fact that the mail server can sometimes see stale data of less than
a minute old is no problem.

This seems to work fine, but cron does feel a bit kludgey though...

Is there a way to trigger stuff using NOTIFY/LISTEN, but WITHOUT an
external client doing the magic? Some kind of internal Postgres function
that listens for any changes, and then does the magic itself, without
tying up any 'real' clients for the duration of that magic?

Thanks!

--
Dick Visser