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: | Whole Thread | Raw Message | 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
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 |