I have a C# client application that uses Npgsql to call a plpgsql function in PostgreSQL 9.1.4. The function takes a very long time and I'd like to report progress to the client in some way. How should I do this?
The LISTEN/NOTIFY mechanism sounds perfect for this, except that the whole thing runs inside a transaction and NOTIFY events are not sent until the end of the transaction, which is useless to me.
The other thing I've tried is RAISE NOTICE, which I can process on the client, but even those notices seem to be buffered for a while and sent in batches. It's better than nothing, but not ideal. Is there any way I can "flush" them, so they're sent to the client immediately?