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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
