Unpredicatable behavior of volatile functions used in cursors

Поиск
Список
Период
Сортировка
От Aleksander Kmetec
Тема Unpredicatable behavior of volatile functions used in cursors
Дата
Msg-id 45AB9A7C.4000301@intera.si
обсуждение исходный текст
Ответы Re: Unpredicatable behavior of volatile functions used in cursors  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Shane Ambler
Дата:
Сообщение: Re: Performance with very large tables
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Runtime error when calling function from .NET ( Function returns record)