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

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.

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.

Simpliest way would be to split your pgsql function into multiple sub functions, than call them sequentially, application-side, managing transaction scope in the application.