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,
