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

Dear Tom,

Thanks for getting back to me so soon.

I see that SQL functions seem to inline their calling arguments.

My complaint remains. That inlined function f_return_ver_id_4() is a STABLE function, inlined or not. Postgres now calls it multiple times during the transaction, even though the arguments to f_return_ver_id_4() have not changed.

STABLE no longer means STABLE. This behavior is killing my performance. I am getting 500% to 30000% increase in latency.

The problem does not stop with just this. Functions that I have no control over, like now(), behave like this too. The function now() gets called multiple times in a transaction. See the example below.

All this behavior seems only to happen when I call the SQL function from a PL/PGSQL function. It doesn't happen when I call the SQL function directly from the psql command line.

For example, below, look what I had to do with the now() function. I had to change:

<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>

create or replace function f_get_current_price_all_keys_by_mp_rank(culture_in text, ver_id_min_in int8, ver_id_max_in int8, query_in tsquery)
returns setof type_product_price_key
as
$BODY$
---------------------------------------------------------------------------------
--
-- This function retrieves the all primary keys to the product_price table
-- which point to the current prices of vendor products for a specific
-- tsearch query.
---------------------------------------------------------------------------------
select pp.vp_id,
pp.ver_id,
pp.pm_id,
max(pp.pp_price_eff_time) as pp_price_eff_time,
pp.pp_min_qty
from product_price pp
inner join vendor_product vp on vp.vp_id = pp.vp_id
inner join manufacturer_product_tsv tsv on tsv.mp_id = vp.mp_id and tsv.ver_id = pp.ver_id
inner join manufacturer_product mp on mp.mp_id = vp.mp_id
inner join brand b on b.brand_id = mp.brand_id
inner join manufacturer m on m.man_id = b.man_id
inner join vendor_vertical vv on vv.vdr_id = vp.vdr_id and vv.ver_id = pp.ver_id
inner join vendor v on v.vdr_id = vv.vdr_id
inner join promotion pm on pm.pm_id = pp.pm_id
left join promo_vendor_behavior vb on vb.pm_id = pp.pm_id and vb.vdr_id = vp.vdr_id
left join promo_type_attrs pa on pa.pmta_id = vb.pmta_id
inner join time_zone t on t.tz_id = pp.tz_id
where pp.active_cd = 1
and tsv.search_vector @@@ $4
and tsv.culture = $1
and pp.ver_id between $2 and $3
and vp.active_cd = 1
and vv.active_cd = 1
and v.active_cd = 1
and mp.active_cd = 1
and b.active_cd = 1
and m.active_cd = 1
and pm.active_cd = 1
and ((pa.pmta_id is null) or (pa.pmta_id is not null and pa.active_cd = 1))
and ((pp.pp_end_time is null) or (pp.pp_end_time > now()))
and pp.pp_price_eff_time <= now()
and (pp.days_of_week & (1 << (extract(dow from now() at time zone t.name)::int4))) <> 0
and (pp.days_of_month & (1 << (extract(day from now() at time zone t.name)::int4))) <> 0
and (pp.months_of_year & (1 << (extract(month from now() at time zone t.name)::int4))) <> 0
group by pp.vp_id, pp.ver_id, pp.pm_id, pp.pp_min_qty
$BODY$
language 'SQL' STABLE;

<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>

To this PL/PGSQL function because now() gets called multiple times. The function above runs in 1.8 seconds. The function below runs in 0.25 seconds.

create or replace function f_get_current_price_all_keys_by_mp_rank(culture_in text, ver_id_min_in int8, ver_id_max_in int8, query_in tsquery)
returns setof type_product_price_key
as
$BODY$
---------------------------------------------------------------------------------
--
-- This function retrieves the all primary keys to the product_price table
-- which point to the current prices of vendor products for a specific
-- tsearch query.
---------------------------------------------------------------------------------
declare
now_w timestamp with time zone;
begin

now_w := now();

return query
select pp.vp_id,
pp.ver_id,
pp.pm_id,
max(pp.pp_price_eff_time) as pp_price_eff_time,
pp.pp_min_qty
from product_price pp
inner join vendor_product vp on vp.vp_id = pp.vp_id
inner join manufacturer_product_tsv tsv on tsv.mp_id = vp.mp_id and tsv.ver_id = pp.ver_id
inner join manufacturer_product mp on mp.mp_id = vp.mp_id
inner join brand b on b.brand_id = mp.brand_id
inner join manufacturer m on m.man_id = b.man_id
inner join vendor_vertical vv on vv.vdr_id = vp.vdr_id and vv.ver_id = pp.ver_id
inner join vendor v on v.vdr_id = vv.vdr_id
inner join promotion pm on pm.pm_id = pp.pm_id
left join promo_vendor_behavior vb on vb.pm_id = pp.pm_id and vb.vdr_id = vp.vdr_id
left join promo_type_attrs pa on pa.pmta_id = vb.pmta_id
inner join time_zone t on t.tz_id = pp.tz_id
where pp.active_cd = 1
and tsv.search_vector @@@ $4
and tsv.culture = $1
and pp.ver_id between $2 and $3
and vp.active_cd = 1
and vv.active_cd = 1
and v.active_cd = 1
and mp.active_cd = 1
and b.active_cd = 1
and m.active_cd = 1
and pm.active_cd = 1
and ((pa.pmta_id is null) or (pa.pmta_id is not null and pa.active_cd = 1))
and ((pp.pp_end_time is null) or (pp.pp_end_time > now_w))
and pp.pp_price_eff_time <= now()
and (pp.days_of_week & (1 << (extract(dow from now_w at time zone t.name)::int4))) <> 0
and (pp.days_of_month & (1 << (extract(day from now_w at time zone t.name)::int4))) <> 0
and (pp.months_of_year & (1 << (extract(month from now_w at time zone t.name)::int4))) <> 0
group by pp.vp_id, pp.ver_id, pp.pm_id, pp.pp_min_qty;
return;
end;
$BODY$
language 'plpgsql' STABLE
rows 1000;

But that is not all that's going on. It turns out that in a SQL function, now() also gets called multiple times.

The function f_return_ver_id_4() is a STABLE function. Even when inlined, the behavior should still be that the function gets called only once during a transaction

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Tuesday, August 10, 2010 10:55 PM
To: Brian Ceccarelli
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] 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