Re: background triggers?

On 5/23/06, Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com> wrote:
> On Tue, 2006-05-23 at 15:56 +0000, Chris Browne wrote:
> > > The use that I have for this at the moment, and I can think of many
> > > other uses, is that I want to populate a statistics table each time
> > > that a table is updated. But the code to populate the table takes 10
> > > seconds to run. I don't want the user to have to wait 10 seconds to
> > > add a record.
> >
> > This seems a case for using NOTIFY/LISTEN.
> >
> > - You have a process connected to the database that runs LISTEN,
> > causing it to listen for a particular message.
> >
> > LISTEN regen_statistics;
> >
> > - Your trigger submits a notification:
> >
> > NOTIFY regen_statistics;
>
> Some time ago I fell into quite similair problem. But LISTEN/NOTIFY was
> not a solution.
>
> In that case I needed to *disconnect* and never bother about the outcome
> of a long running background trigger.

The idea is that you *disconnect* and you have a daemon running at the
server side, which will handle LISTEN efficiently. Daemon can be quickly
written in perl, and it can use select(2) call to listen for incoming notifies.
Fast, efficient and powerful.

> So if I may re-phrase the question: "is there a way to have a trigger,
> that, when launched, can check if it's already running in backgroung for
> some other INSERT, return imediately if so, but turn into background for
> a long-lasting job if not".

It is also possible. You probably want to use locks checking in your
trigger (I'm writing from memory, so forgive me syntax erros, if any).
a trigger might be, say:

CREATE OR REPLACE FUNCTION foo_trigger() RETURNS trigger AS $$
BEGIN
SELECT key FROM foo_stats WHERE key = NEW.key FOR UPDATE NOWAIT;
-- this will either lock the row with "key" or return
immediately, if it's been locked:
UPDATE foo_stats SET count=(SELECT count(*) FROM foo WHERE
key=NEW.key) WHERE key=NEW.key;
RETURN NEW;
EXCEPTION
WHEN lockbusyorsomething THEN RETURN NEW;
END;
$$ LANGUAGE PLpgSQL;

Most likely there are better ways to accomplish your goal.

Regards,
Dawid