Thread: Asynchronous trigger

Hi all,

I'm planning to convert an application to postgresql 8.1 (from mysql).
Currently we have some recursive procedures done on the application side
we would want to rewrite as stored pl/pgsql procedures called by triggers...

Is it possible for these triggers to be asynchronous, in order not to
block the client application during the recursive process ?

Regards,


--
Jean-Christophe Praud         -      http://shub-niggurath.com
Conseil & Développement Informatique      http://www.praud.com
Ph'nglui mglw'nafh Cthulhu n'gah Bill R'lyeh Wgah'nagl fhtagn!

On Wed, 2006-09-06 at 23:29 +0200, Jean-Christophe Praud wrote:
> Hi all,
>
> I'm planning to convert an application to postgresql 8.1 (from mysql).
> Currently we have some recursive procedures done on the application side
> we would want to rewrite as stored pl/pgsql procedures called by triggers...
>
> Is it possible for these triggers to be asynchronous, in order not to
> block the client application during the recursive process ?
>

You can send the query itself asynchronously using PQsendQuery (for
libpq, see your client library's docs for a similar function). That
allows your application to continue processing, but doesn't allow you to
issue any more queries over that connection.

You could make your trigger send a notification to an application that
just handles that one task in the background.

Regards,
    Jeff Davis

On 9/6/06, Jean-Christophe Praud <jc@praud.com> wrote:
> Hi all,
>
> I'm planning to convert an application to postgresql 8.1 (from mysql).
> Currently we have some recursive procedures done on the application side
> we would want to rewrite as stored pl/pgsql procedures called by triggers...
>
> Is it possible for these triggers to be asynchronous, in order not to
> block the client application during the recursive process ?

Have you considered doing the recursion on the server side?

http://people.planetpostgresql.org/merlin/index.php?/archives/2-Dealing-With-Recursive-Sets-With-PLPGSQL.html

merlin

You want to use the Notify/Listen framework.
1) You need a rule on a table that says on Update (or insert or delete)
do also notify {key}
2) You need a daemon running against the server that calls listen {key}
3) when the table is updated (or inserted or deleted) then your
application will be able to check the table and see what needs to be done.

I just did this for a process that needs to update a denormalized
statistics table every time the data changes and it seems to be working
great. My query takes 2.5 seconds and the users didn't want to wait that
long each time they modified a record. They also refused to tolerate it
being updated every 10 minutes. They wanted it in as close to real time
as it could be (with good reason, i might add). So the daemon calls a
function and it works just fine.

Sim

Jean-Christophe Praud wrote:
> Hi all,
>
> I'm planning to convert an application to postgresql 8.1 (from mysql).
> Currently we have some recursive procedures done on the application side
> we would want to rewrite as stored pl/pgsql procedures called by
> triggers...
>
> Is it possible for these triggers to be asynchronous, in order not to
> block the client application during the recursive process ?
>
> Regards,
>
>