Обсуждение: 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)