Обсуждение: Unpredicatable behavior of volatile functions used in cursors

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

Aleksander Kmetec <aleksander.kmetec@intera.si> writes:
> 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.

This isn't gonna work very well if your query involves sorting, because
the SELECT-list is evaluated before the sort step ...

            regards, tom lane

Tom Lane wrote:
> This isn't gonna work very well if your query involves sorting, because
> the SELECT-list is evaluated before the sort step ...
>
>             regards, tom lane
>

Thanks, this seems to solve my problem.

Some quick testing shows that util.row_number() only gets re-evaluated at every call if the subquery contains an ORDER
BY clause. Now we can predict whether we need to compensate for that just by looking at the original query.

Regards,
Aleksander

Aleksander Kmetec <aleksander.kmetec@intera.si> writes:
> Some quick testing shows that util.row_number() only gets re-evaluated at every call if the subquery contains an
ORDER 
> BY clause. Now we can predict whether we need to compensate for that just by looking at the original query.

If you're going to use a cursor, I don't understand why you don't just
MOVE FORWARD ALL and look at the returned rowcount to determine how
many rows in the query.  This won't be any more expensive than fetching
the last row ...

            regards, tom lane

Tom Lane wrote:
> Aleksander Kmetec <aleksander.kmetec@intera.si> writes:
>> Some quick testing shows that util.row_number() only gets re-evaluated at every call if the subquery contains an
ORDER 
>> BY clause. Now we can predict whether we need to compensate for that just by looking at the original query.
>
> If you're going to use a cursor, I don't understand why you don't just
> MOVE FORWARD ALL and look at the returned rowcount to determine how
> many rows in the query.  This won't be any more expensive than fetching
> the last row ...

That's a very good question...

It's more than a year now since I originally wrote that code, and I remember testing MOVE FORWARD ALL, but being unable

to get my hands on the row count for that operation. At that time I just accepted it as the way things work and created

that row_number() workaround instead.

But after your last message I went to investigate why that had happened in the first place. And sure enough, there was
a 
bug in the 3rd party database library we're using. After a quick fix everything is working as it should and I can
remove 
  row_number() altogether.

Thank you for your patience. :-)

Aleksander