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.
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".
The functionality required is "lazy" update of agregate tables, which
need not be 100% acurate, but should be kept in-sync whenever possible.
-R