Re: background triggers?

Sim Zacks <sim(at)compulab(dot)co(dot)il> writes:
> Is there any way to write a statement trigger that runs in the
> background? In my testing, when I have an After Insert For Each
> Statement trigger, the function call does not end until the trigger is
> finished processing.
>
> What I would like to do, and please let me know if there is a better
> way to do this, is to have an after event statement trigger run in a
> separate process so the function call ends when it ends and the
> statement trigger gets executed on its own time.
>
> 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;

The notification doesn't get committed to internal table
pg_catalog.pg_listener until the transaction doing the writing
completes its COMMIT, so the listener won't "hear" anything until
then...

Per the docs:

The method a frontend application must use to detect notify events
depends on which Postgres application programming interface it
uses. With the basic libpq library, the application issues LISTEN as
an ordinary SQL command, and then must periodically call the routine
PQnotifies to find out whether any notify events have been
received. Other interfaces such as libpgtcl provide higher-level
methods for handling notify events; indeed, with libpgtcl the
application programmer should not even issue LISTEN or UNLISTEN
directly. See the documentation for the library you are using for
more details.
--
select 'cbbrowne' || '@' || 'ntlug.org';
http://cbbrowne.com/info/multiplexor.html
"I am a bomb technician. If you see me running, try to keep up..."