Skip site navigation (1) Skip section navigation (2)

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

From: Brian Ceccarelli <bceccarelli(at)net32(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #5611: SQL Function STABLE promoting to VOLATILE
Date: 2010-08-11 15:01:57
Message-ID: 9DF775F4321E6544B0480342D35DC49533DA086EB2@cs2.ad2.net32.net (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

Responses

pgsql-bugs by date

Next:From: Kevin GrittnerDate: 2010-08-11 15:10:45
Subject: Re: BUG #5612: Database Integrity
Previous:From: Robert HaasDate: 2010-08-11 13:32:59
Subject: Re: BUG #5604: Setting NOT NULL on inherited column turns to real column in dump

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group