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

"Brian Ceccarelli" <bceccarelli(at)net32(dot)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