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

BUG #5611: SQL Function STABLE promoting to VOLATILE

From: "Brian Ceccarelli" <bceccarelli(at)net32(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5611: SQL Function STABLE promoting to VOLATILE
Date: 2010-08-10 20:30:50
Message-ID: 201008102030.o7AKUo5E022936@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      5611
Logged by:          Brian Ceccarelli
Email address:      bceccarelli(at)net32(dot)com
PostgreSQL version: 8.4.4
Operating system:   Windows XP 32 bit and Red Hat 5.4 64 bit
Description:        SQL Function STABLE promoting to VOLATILE
Details: 

----------------------------------------------------------------------------
-----
--
--    Demonstration of a PL/PGSQL stable-scoped function not working.
--
--        To see the problem, run this entire script from PGAdmin.   Bring
up Task Manager too.
--        After you the run this entire script, you can repeat the specific
problem by running:
--
--                select * from f_pass_4();
--
--        Note that "I am in f_return_ver_id_4() repeats once for every row
returned from f_get_table_4().
--        Postgres should only call f_return_ver_id_4() once.

--    The Problems:
--
--        1.  It seems that STABLE functions called within a SQL language
get promoted to VOLATILE. 
--            Even though I declare a function STABLE, Postgres calls it
multiple times within a tranasaction.
--
--        2.  The raise notice in f_return_ver_id_4() also causes a memory
leak in PGAdmin (on Windows).
--
--    Related Problems:
--
--         Even the now() function gets called repeatedly within a stable
SQL function.
--
--    Postgres Version:
--
--        I am running Postgres 8.4.4-1 on Windows.   (Windows XP 32 bit)
--        Same problem occurs on Postgres 8.4.4 on Linux-64.   Red Hat 5.4.
--
--    Problem NOT in Postgres 8.2.
--
--         This problem does not happen in Postgres 8.2.
-- 
----------------------------------------------------------------------------
-----

drop type if exists type_pass_test cascade;
create type type_pass_test as (ver_id int8);

CREATE OR REPLACE FUNCTION f_get_table_4()
   RETURNS setof type_pass_test AS
$BODY$
----------------------------------------------------------------------------
-----
--
--
----------------------------------------------------------------------------
-----
declare
     r          type_pass_test;
     i          int8;
begin

   for i in 1..5
   loop
      r.ver_id := i;
      return next r;
   end loop;

   return;
end;
$BODY$
  language 'plpgsql' volatile;



CREATE OR REPLACE FUNCTION f_return_ver_id_4()
   RETURNS int8 AS
$BODY$
----------------------------------------------------------------------------
-----
--
--
----------------------------------------------------------------------------
-----
declare
begin
   raise notice 'I am in f_return_ver_id_4()'; 
   return 1;
end;
$BODY$
  language 'plpgsql' stable;



CREATE OR REPLACE FUNCTION f_do_4(ver_id_in  int8)
   RETURNS setof type_pass_test AS
$BODY$
----------------------------------------------------------------------------
-----
--
--   When you run f_pass_4(), f_pass_4() calls f_do_4() passing ver_id_in as
f_return_ver_id_4().
--   
--   The error:
--
--       Even though f_return_ver_id_4() is a STABLE function, the select
--   statement below calls f_return_ver_id_4() once for every row coming
back from
--   f_get_table_4(). 
--
--       The repeat appears when I write the function in the SQL language.
--       The repeat disappears when I write the function in the PL/PGSQL
language.
--       If I add now() to the where clause, you will even see that Postgres
calls now() multiple times.
--       
----------------------------------------------------------------------------
-----

 select *
   from f_get_table_4()
  where ver_id = $1;

$BODY$
  language 'SQL' stable;


/*
CREATE OR REPLACE FUNCTION f_do_4(ver_id_in  int8)
   RETURNS setof type_pass_test AS
$BODY$
----------------------------------------------------------------------------
-----
--   
--   If I make the f_do_4(ver_id_in int8) a PL/PGSQL function, the problem
goes away. 
--
----------------------------------------------------------------------------
-----
begin

   return query
     select *
       from f_get_table_4()
      where ver_id = $1;

   return;

end;
$BODY$
  language 'plpgsql' stable;

*/

CREATE OR REPLACE FUNCTION f_pass_4()
   RETURNS int4 AS
$BODY$
----------------------------------------------------------------------------
-----
--
--    Example:
--
--    select * from f_pass_4();
--
----------------------------------------------------------------------------
-----
declare
   rows_affected_w  int4; 
begin



   select into rows_affected_w
               count(*)
          from f_do_4(f_return_ver_id_4());
   
   return rows_affected_w;
end;
$BODY$
  language 'plpgsql' stable;

----------------------------------------

select * from f_pass_4();

Responses

pgsql-bugs by date

Next:From: Mark KirkwoodDate: 2010-08-11 02:42:40
Subject: Re: Measuring execution time
Previous:From: Alex HunsakerDate: 2010-08-10 18:30:07
Subject: Re: BUG #5601: cannot create language plperl;

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