2010/2/16 Alban Hertroys <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>: > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
