Thread: Terminating a SETOF function call sequence

Some SQL constructs will be satisfied before all rows of a set has been 
examined. I'm thinking of for instance:

EXISTS(SELECT * FROM y WHERE y.a > 0)

If the first row of collection y fulfills the WHERE predicate, there's 
no reason to continue perusing the rest of the rows. Now, what if 'y' is 
a function returning SETOF something? I see only one possible way for a 
C-function to detect that it doesn't need to return more rows and that 
would be if the FuncCallContext call_cntr reaches max_calls.

My question is, what happens when the evaluator doesn't need more rows? 
Will it:
a) call the function with call_cntr >= max_calls?
b) continue calling until the set is exhausted anyway?
c) simply stop calling?

a) seems unlikely since max_calls is set by the user, b) doesn't seem 
very optimal, and c) would be very bad since it doesn't give me any 
chance to release the resources that where used in order to produce the 
rows.

Regards,
Thomas Hallgren

> ... c) would be very bad since it
> doesn't give me any chance to release the resources that
> where used in order to produce the rows.

You are supposed to free resources used to produce the rows before srf_return_next();
The actual rows are pfree()'d by pg. (an dso are any other palloc()'d resources, but I'd recommend freeing them anyway,
especiallyif youre going to use the function in an index or transactions, since resources a not freed till the end of
thetransaction) 

... JOhn

John,

>You are supposed to free resources used to produce the rows before srf_return_next();
>  
>
I can (and must) free up the resources used to produce one single row at
that time yes, but I might have resources that is common to all rows.
Let's assume that I have a file open for instance. I read one row at a
time from that file. I need to know when to close the file.

Regards,
Thomas Hallgren

Thomas Hallgren <thhal@mailblocks.com> writes:
> My question is, what happens when the evaluator doesn't need more rows? 
> Will it:
> a) call the function with call_cntr >= max_calls?
> b) continue calling until the set is exhausted anyway?
> c) simply stop calling?

(c)

> a) seems unlikely since max_calls is set by the user, b) doesn't seem 
> very optimal, and c) would be very bad since it doesn't give me any 
> chance to release the resources that where used in order to produce the 
> rows.

This is what RegisterExprContextCallback is for.
        regards, tom lane

Tom Lane wrote:

>Thomas Hallgren <thhal@mailblocks.com> writes:
>  
>
>>My question is, what happens when the evaluator doesn't need more rows? 
>>Will it:
>>a) call the function with call_cntr >= max_calls?
>>b) continue calling until the set is exhausted anyway?
>>c) simply stop calling?
>>    
>>
>
>(c)
>
>  
>
>>a) seems unlikely since max_calls is set by the user, b) doesn't seem 
>>very optimal, and c) would be very bad since it doesn't give me any 
>>chance to release the resources that where used in order to produce the 
>>rows.
>>    
>>
>
>This is what RegisterExprContextCallback is for.
>
>            regards, tom lane
>  
>
Thanks Tom,
This is exactly what I need. I didn't know that such a callback existed. 
Perhaps it should be mentioned in the documentation chapter that talks 
about SETOF and C-functions?

Regards,
Thomas Hallgren