Обсуждение: 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

Dick Visser <visser@terena.org> writes:
> 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.

Well, the traditional solution is to have a trigger or rule that fires
the NOTIFY.  You don't need any cooperation from the client apps.

> Is there a way to trigger stuff using NOTIFY/LISTEN, but WITHOUT an
> external client doing the magic?

No.  You need a dedicated client process that just sits there waiting
for notifies.  (Well, it might be able to do some other useful work
too, but the simplest way is to dedicate a connection for this.)

            regards, tom lane

On Wed, Aug 19, 2009 at 8:23 PM, Dick Visser<visser@terena.org> wrote:
>
> 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?

What do you mean "tying up"? Even if there was no client an internal
process would be "tying up" the same resources as if there was one.

In my experience it's a lot more flexible having an external process
connecting to the database anyways since it means you can use whatever
language you like, whatever libraries you like, etc. You can also use
other resources, close and open the connection, start and stop
transactions, etc. Anything within the database has a lot more
restrictions.

--
greg
http://mit.edu/~gsstark/resume.pdf

Hi Dick,

On Wed, Aug 19, 2009 at 09:23:22PM +0200, Dick Visser wrote:

> 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.

Are you sure you have finished tuning at the database level - indices,
partitions etc.? What does EXPLAIN ANALYZE of a typical slow query show?

BTW: Which version of PG are you running?

Tino.

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.lichtkreis-chemnitz.de
www.craniosacralzentrum.de