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

The following bug has been logged online:

Bug reference:      5611
Logged by:          Brian Ceccarelli
Email address:      bceccarelli@net32.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();