Re: BUG #5611: SQL Function STABLE promoting to VOLATILE - Mailing list pgsql-bugs

Dear Tom,

     Thanks for getting back to me so soon.

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

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

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

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

     All this behavior seems only to happen when I call the SQL function fr=
om a PL/PGSQL function.   It doesn't happen when I call the SQL function di=
rectly 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=20
--   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   =3D pp.vp_i=
d=20
          inner join manufacturer_product_tsv tsv on tsv.mp_id  =3D vp.mp_i=
d and tsv.ver_id =3D pp.ver_id=20
          inner join manufacturer_product     mp  on mp.mp_id   =3D vp.mp_id
          inner join brand                    b   on b.brand_id =3D mp.bran=
d_id
          inner join manufacturer             m   on m.man_id   =3D b.man_id
          inner join vendor_vertical          vv  on vv.vdr_id  =3D vp.vdr_=
id and vv.ver_id =3D pp.ver_id
          inner join vendor                   v   on v.vdr_id   =3D vv.vdr_=
id
          inner join promotion                pm  on pm.pm_id   =3D pp.pm_i=
d=20=20=20=20=20=20=20
          left  join promo_vendor_behavior    vb  on vb.pm_id   =3D pp.pm_i=
d and vb.vdr_id =3D vp.vdr_id
          left  join promo_type_attrs         pa  on pa.pmta_id =3D vb.pmta=
_id
          inner join time_zone                t   on t.tz_id    =3D pp.tz_i=
d=20=20
    where pp.active_cd  =3D 1
      and tsv.search_vector @@@ $4
      and tsv.culture   =3D $1
      and pp.ver_id between $2 and $3
      and vp.active_cd  =3D 1
      and vv.active_cd  =3D 1
      and v.active_cd   =3D 1
      and mp.active_cd  =3D 1
      and b.active_cd   =3D 1
      and m.active_cd   =3D 1
      and pm.active_cd  =3D 1
      and ((pa.pmta_id is null) or (pa.pmta_id is not null and pa.active_cd=
 =3D 1))=20=20=20=20=20=20=20=20
      and ((pp.pp_end_time is null) or (pp.pp_end_time > now()))
      and pp.pp_price_eff_time <=3D 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;
=20=20

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

To this PL/PGSQL function because now() gets called multiple times.  The fu=
nction 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=20
--   tsearch query.
---------------------------------------------------------------------------=
------
declare
    now_w    timestamp with time zone;
begin

   now_w :=3D now();
=20=20=20
   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   =3D pp.vp_i=
d=20
          inner join manufacturer_product_tsv tsv on tsv.mp_id  =3D vp.mp_i=
d and tsv.ver_id =3D pp.ver_id=20
          inner join manufacturer_product     mp  on mp.mp_id   =3D vp.mp_id
          inner join brand                    b   on b.brand_id =3D mp.bran=
d_id
          inner join manufacturer             m   on m.man_id   =3D b.man_id
          inner join vendor_vertical          vv  on vv.vdr_id  =3D vp.vdr_=
id and vv.ver_id =3D pp.ver_id
          inner join vendor                   v   on v.vdr_id   =3D vv.vdr_=
id
          inner join promotion                pm  on pm.pm_id   =3D pp.pm_i=
d=20=20=20=20=20=20=20
          left  join promo_vendor_behavior    vb  on vb.pm_id   =3D pp.pm_i=
d and vb.vdr_id =3D vp.vdr_id
          left  join promo_type_attrs         pa  on pa.pmta_id =3D vb.pmta=
_id
          inner join time_zone                t   on t.tz_id    =3D pp.tz_i=
d=20=20
    where pp.active_cd  =3D 1
      and tsv.search_vector @@@ $4
      and tsv.culture   =3D $1
      and pp.ver_id between $2 and $3
      and vp.active_cd  =3D 1
      and vv.active_cd  =3D 1
      and v.active_cd   =3D 1
      and mp.active_cd  =3D 1
      and b.active_cd   =3D 1
      and m.active_cd   =3D 1
      and pm.active_cd  =3D 1
      and ((pa.pmta_id is null) or (pa.pmta_id is not null and pa.active_cd=
 =3D 1))=20=20=20=20=20=20=20=20
      and ((pp.pp_end_time is null) or (pp.pp_end_time > now_w))
      and pp.pp_price_eff_time <=3D 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;

=20

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




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


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
Sent: Tuesday, August 10, 2010 10:55 PM
To: Brian Ceccarelli
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #5611: SQL Function STABLE promoting to VOLATILE=20

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

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 =3D 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