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

On 16 Feb 2010, at 10:34, Peter Geoghegan wrote:

>> I'd think RETURN NEXT would behave the way you want it to. There's probably something in your function causing your
functionto behave like it does now. I suspect the problem lies in the way you determine how far you've progressed, but
youdidn't tell us anything about your function, so I'm just guessing. 
>>
>
> Why do you think that? At the moment, the function merely sends two
> messages along the lines of "beginning downloading...",  "connecting
> to first db...", before connecting to the first remote DB, which, in
> my perfunctory testing was unavailable. The connection blocks, throws
> an exception, is handled in an EXECPTION block (by once again sending
> a message in the usual way). However, I get all 3 messages at once,
> only when the dblink function finishes blocking and throws its
> exception, which takes about 7 seconds because I like to use a timeout
> in my connection string.

I assumed you were generating the progress indicator from query results in a remote DB. It turns out that's entirely
notwhat you're doing, but how were we supposed to know that? 

You say you want your function to behave like a cursor.
A function can't behave like a cursor, just as a select can't; both result in a result set. What RETURN NEXT and RETURN
QUERYdo is make it possible to access the function results row by row. You still need a cursor to get the behaviour of
acursor out of that. 

If that doesn't answer your question, I'm pretty sure that to help you with this problem people will need quite a bit
moreinformation than you're giving us. What are you doing? What do you see vs. what do you expect? Some examples would
help.

>> I don't know much about dblink, so it is possible your problem is related to that. I imagine it may batch "small"
resultsets and send them over all at once to reduce traffic. Do you see this problem with larger result sets (say >10k
rows)?
>>
>
> I'll attempt to devise a useful test, but I'm a little doubtful that
> I'll get anywhere with my current approach, given that my initial,
> very simple test failed.


From your description it turns out dblink isn't involved yet, so it can't cause the issue you were asking about.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4b7a71b410441921015512!