Re: Having a plpgsql function return multiple rows that indicate its progress in a cursor like fashion

2010/2/16 Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl>:
> On 16 Feb 2010, at 1:04, Peter Geoghegan wrote:
>
>> Hello,
>>
>> At the moment my pg client application (running 8.4) transfers data
>> from several remote DBs (that run 8.3) via dblink, using cursors where
>> appropriate, and reporting back progress to users using a progress bar
>> and brief messages.
>>
>> I thought it would be great to change my code to have all this done
>> within a single plpgsql function, and have all the benefits that that
>> brings.
>>
>> It's easy enough to do all this, by writing a function that RETURNS
>> TABLE(progress integer, message text), and RETURNing NEXT when time
>> comes to display a new message or increment the progress bar. However,
>> that approach has the considerable drawback of not actually returning
>> any rows until it finally returns all of them. I'm not willing to give
>> up giving the user those messages and having their progress bar
>> updated in real-time though. I would like to have the function behave
>> as a cursor, and return one row at a time when control reaches each
>> RETURN NEXT statement.
>
>
> I'd think RETURN NEXT would behave the way you want it to. There's probably something in your function causing your function to behave like it does now. I suspect the problem lies in the way you determine how far you've progressed, but you didn't tell us anything about your function, so I'm just guessing.
>
> I don't know much about dblink, so it is possible your problem is related to that. I imagine it may batch "small" result sets and send them over all at once to reduce traffic. Do you see this problem with larger result sets (say >10k rows)?

if you have a large dataset (more than 1 M rows), then better is C
coding. PLpgSQL (return next, return query) push result to memory, and
it can be a problem.

Regards
Pavel Stehule

>
> Alban Hertroys
>
> --
> Screwing up is the best way to attach something to the ceiling.
>
>
> !DSPAM:737,4b7a5d9710441627593049!
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>