Hi,
I'm running into some inconsistent behavior when using volatile functions with cursors under PG 8.1.
We're using the following technique for counting the number of rows in a cursor:
-----------
DECLARE instance_cur_1 SCROLL CURSOR FOR
SELECT util.row_number(), *
FROM (
$LONG_RUNNING_QUERY
) ss
FETCH LAST IN instance_cur_1;
-----------
util.row_number() is a volatile function written in C which simply returns "++internal_counter" every time it is
called.
What's unusual is that for some queries FETCH LAST returns a row_number value which matches the actual number of rows,
while for others it returns the actual number +1 (and adds +1 for each consecutive call). It seems that under some
conditions util.row_number() gets re-evaluated for every call.
Could someone explain why and under which conditions this happens?
Is there a way to make this behavior more consistent?
Regards,
Aleksander
