Re: BUG #5611: SQL Function STABLE promoting to VOLATILE

"Brian Ceccarelli" <bceccarelli@net32.com> writes:
> --        1.  It seems that STABLE functions called within a SQL language
> get promoted to VOLATILE.

That has got nothing to do with it.  The change in behavior from 8.2
is due to the fact that set-returning SQL functions can now be inlined.
The statement in f_pass_4(),

   select into rows_affected_w count(*)
          from f_do_4(f_return_ver_id_4());

gets expanded (by inlining of f_do_4) into

   select into rows_affected_w count(*)
          from f_get_table_4()
          where ver_id = f_return_ver_id_4();

and then since f_get_table_4() returns multiple rows, the WHERE clause
gets evaluated multiple times.

As near as I can tell, your real complaint is that the side-effects of
f_return_ver_id_4 (ie, the RAISE NOTICE) happen more than once.
However, a function declared STABLE really shouldn't *have* any side
effects, because that marking authorizes the optimizer to assume that it
doesn't.  If you marked it VOLATILE then this optimization wouldn't
happen.

> --        2.  The raise notice in f_return_ver_id_4() also causes a memory
> leak in PGAdmin (on Windows).

Hm, you probably ought to mention that part on the pgadmin mailing
lists.  I don't know whether the appropriate people will notice it here.

            regards, tom lane