Re: Asynchronous queries - processing listen (notify) in a procedural language

On Sat, Feb 20, 2010 at 9:38 PM, Petr Chmelar <chmelarp(at)fit(dot)vutbr(dot)cz> wrote:
> Hi there,
>
> I'd like to make a real-time enabled database and I need to process data
> asynchronously. Usually, there are many quick inserts sometimes causing
> vast updates. I can't use triggers and rules because the transactions
> are really long when there is the update (upgrade) needed.
>
> Is there a way how to listen and trigger the notify messages in the
> database (+-)immediately and/or to execute additional (trigger) queries
> in other transactions?
>
> In Oracle there is ON COMMIT trigger and an anonymous transaction that
> allows commit inside triggers. However this is not possible in Postgres.
> Moreover I can't know the check interval of the listening procedures in
> an external application (can be anything from millis to days). I was so
> desperate that I was thinking about own logging function.

The only way that I know of to send notify 'in-transaction' is via
dblink...you just send 'notify x' as the query which commits and fires
the action. It doesn't make sense to do this if your outer
transaction is very short in duration.

merlin