Unpredicatable behavior of volatile functions used in cursors

From: Aleksander Kmetec <aleksander(dot)kmetec(at)intera(dot)si>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Unpredicatable behavior of volatile functions used in cursors
Date: 2007-01-15 15:15:08
Message-ID: 45AB9A7C.4000301@intera.si
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2007-01-15 15:44:48 Re: Runtime error when calling function from .NET ( Function returns record)
Previous Message Shane Ambler 2007-01-15 15:10:06 Re: Performance with very large tables