Обсуждение: Bug with function returning composite types.
Functions that return composite types are called more times than expected. Example script: --------------- begin; create table composite_types_test ( a integer, b integer, c integer, d integer, e integer, f integer, g integer, h integer, i integer, j integer ); insert into composite_types_test values (0,1,2,3,4,5,6,7,8,9); create function bug_function () returns composite_types_test volatile language plpgsql as $$ declare r composite_types_test; begin select * into r from composite_types_test; raise notice 'in bug_function'; return r; end; $$; select bug_function(); select (bug_function()).*; rollback; --------------- Example Output: --------------- BEGIN CREATE TABLE INSERT 0 1 CREATE FUNCTION psql:sql/bug_example.sql:30: NOTICE: in bug_function bug_function=20=20=20=20=20=20 ----------------------- (0,1,2,3,4,5,6,7,8,9) (1 row) psql:sql/bug_example.sql:32: NOTICE: in bug_function psql:sql/bug_example.sql:32: NOTICE: in bug_function psql:sql/bug_example.sql:32: NOTICE: in bug_function psql:sql/bug_example.sql:32: NOTICE: in bug_function psql:sql/bug_example.sql:32: NOTICE: in bug_function psql:sql/bug_example.sql:32: NOTICE: in bug_function psql:sql/bug_example.sql:32: NOTICE: in bug_function psql:sql/bug_example.sql:32: NOTICE: in bug_function psql:sql/bug_example.sql:32: NOTICE: in bug_function psql:sql/bug_example.sql:32: NOTICE: in bug_function a | b | c | d | e | f | g | h | i | j=20 ---+---+---+---+---+---+---+---+---+--- 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 (1 row) ROLLBACK --------------- Kyle Butt
>>> Kyle Butt <kylebutt@gmail.com> wrote: > select (bug_function()).*; > psql:sql/bug_example.sql:32: NOTICE: in bug_function > psql:sql/bug_example.sql:32: NOTICE: in bug_function > psql:sql/bug_example.sql:32: NOTICE: in bug_function > psql:sql/bug_example.sql:32: NOTICE: in bug_function > psql:sql/bug_example.sql:32: NOTICE: in bug_function > psql:sql/bug_example.sql:32: NOTICE: in bug_function > psql:sql/bug_example.sql:32: NOTICE: in bug_function > psql:sql/bug_example.sql:32: NOTICE: in bug_function > psql:sql/bug_example.sql:32: NOTICE: in bug_function > psql:sql/bug_example.sql:32: NOTICE: in bug_function > a | b | c | d | e | f | g | h | i | j > ---+---+---+---+---+---+---+---+---+--- > 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 > (1 row) For completeness: cir=# select * from bug_function(); NOTICE: in bug_function a | b | c | d | e | f | g | h | i | j ---+---+---+---+---+---+---+---+---+--- 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 (1 row) -Kevin
On Mon, Mar 09, 2009 at 10:03:34AM -0500, Kevin Grittner wrote: > >>> Kyle Butt <kylebutt@gmail.com> wrote: >=20=20 > > select (bug_function()).*; >=20=20 > > psql:sql/bug_example.sql:32: NOTICE: in bug_function > > psql:sql/bug_example.sql:32: NOTICE: in bug_function > > psql:sql/bug_example.sql:32: NOTICE: in bug_function > > psql:sql/bug_example.sql:32: NOTICE: in bug_function > > psql:sql/bug_example.sql:32: NOTICE: in bug_function > > psql:sql/bug_example.sql:32: NOTICE: in bug_function > > psql:sql/bug_example.sql:32: NOTICE: in bug_function > > psql:sql/bug_example.sql:32: NOTICE: in bug_function > > psql:sql/bug_example.sql:32: NOTICE: in bug_function > > psql:sql/bug_example.sql:32: NOTICE: in bug_function > > a | b | c | d | e | f | g | h | i | j=20 > > ---+---+---+---+---+---+---+---+---+--- > > 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 > > (1 row) >=20=20 > For completeness: >=20=20 > cir=3D# select * from bug_function(); > NOTICE: in bug_function > a | b | c | d | e | f | g | h | i | j > ---+---+---+---+---+---+---+---+---+--- > 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 > (1 row) >=20=20 > -Kevin I noticed that it has to do with the volatility. If I declare the function = as stable (which it is) then it's executed multiple times. If I declare it as volatile, it's only executed once. I'm more interested in something like the following (output placed inline): create function bug_function_2 (j_p integer) returns composite_types_test volatile language plpgsql as $$ declare r composite_types_test; begin select * into r from composite_types_test where j =3D j_p; raise notice 'in bug_function_2'; return r; end; $$; select (bf2).* from ( select bug_function_2(j) as bf2 from composite_types_test ) as foo; psql:sql/bug_example.sql:54: NOTICE: in bug_function_2 a | b | c | d | e | f | g | h | i | j=20 ---+---+---+---+---+---+---+---+---+--- 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 (1 row) create or replace function bug_function_2 (j_p integer) returns composite_t= ypes_test stable language plpgsql as $$ declare r composite_types_test; begin select * into r from composite_types_test where j =3D j_p; raise notice 'in bug_function_2'; return r; end; $$; select (bf2).* from ( select bug_function_2(j) as bf2 from composite_types_test ) as foo; psql:sql/bug_example.sql:70: NOTICE: in bug_function_2 psql:sql/bug_example.sql:70: NOTICE: in bug_function_2 psql:sql/bug_example.sql:70: NOTICE: in bug_function_2 psql:sql/bug_example.sql:70: NOTICE: in bug_function_2 psql:sql/bug_example.sql:70: NOTICE: in bug_function_2 psql:sql/bug_example.sql:70: NOTICE: in bug_function_2 psql:sql/bug_example.sql:70: NOTICE: in bug_function_2 psql:sql/bug_example.sql:70: NOTICE: in bug_function_2 psql:sql/bug_example.sql:70: NOTICE: in bug_function_2 psql:sql/bug_example.sql:70: NOTICE: in bug_function_2 a | b | c | d | e | f | g | h | i | j=20 ---+---+---+---+---+---+---+---+---+--- 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 (1 row)