How to report progress from long-running PostgreSQL function to client

Question

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?

Answer 1

In addition to @Pavel's excellent point about RAISE NOTICE, there's another classic technique used to monitor query progress in Pg. It's a bit of a hack, but it's quite effective.

You can exploit the fact that changes to sequences are immediately visible everywhere to expose the progress of a function to the outside. Either use a hard-coded sequence and ensure the function is not called concurrently, or pass the progress monitoring sequence name into the function.

Your function can call nextval(seqname) at each iteration, and interested parties can examine the state of the sequence with SELECT last_value FROM seqname from another session.

You can make the sequence a count-down to completion by setting it up with

create sequence test maxvalue 2147483647 increment by -1;

and calling setval('seqname', num_items) at the start of your function. It'll then count down toward zero with each nextval call. 2147483647 is maxint, by the way.

Needless to say this isn't portable, and there's no guarantee that SELECTing from a sequence will always work this way. It's darn handy, though.

Answer 2

There is nothing better than RAISE NOTICE.

These signals are not buffered - and they are asynchronous - you have issue in notices processing in your application probably.