non-{immutable,cacheable} function being treated as if they're immutable ?

Поиск
Список
Период
Сортировка
От Greg Stark
Тема non-{immutable,cacheable} function being treated as if they're immutable ?
Дата
Msg-id 87d6ldmzm2.fsf@stark.dyndns.tv
обсуждение исходный текст
Список pgsql-general
I'm trying to seed a table with random data for foreign key references. Some
keys are missing so I have to check if they exist before inserting. Postgres
seems to be optimizing out the random() call. I thought it would only do that
for cacheable/immutable functions?

This is happening on 7.3.1 as well as on CVS built about a week ago.

db=> create function r(integer,integer) returns integer as 'select (random()*($2 - $1))::integer + $1' language sql;

db=> explain insert into foo_bar (
         foo_id, bar_id
     ) (
         select foo_id, bar_id
           from (select foo_id, r(1,13512) as bar_id
                   from foo
                ) as x
          where exists (select 1 from bar where bar_id = x.bar_id)
     );

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Result  (cost=442.53..1564.49 rows=1 width=4)
   One-Time Filter: $0
   InitPlan
     ->  Seq Scan on bar  (cost=0.00..442.53 rows=1 width=0)
           Filter: (bar_id = (((random() * 13511::double precision))::integer + 1))
   ->  Seq Scan on foo  (cost=0.00..1121.95 rows=1 width=4)

--
greg

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

Предыдущее
От: Dennis Gearon
Дата:
Сообщение: Re: selective copy
Следующее
От: Diogo de Oliveira Biazus
Дата:
Сообщение: Problems with pgxml and encoding.