The following bug has been logged online: Bug reference: 5611 Logged by: Brian Ceccarelli Email address: bceccare...@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(); -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs