Обсуждение: tuple return from function

Hi,

    I  really  wonder  if  anybody  ever used functions returning
    tuples or sets of tuples. If so, be careful!

    Let's have a look at the following test:

create table emp (name text, salary money);
create table sal_log (empname text, salchg money, who text, when datetime);

create function change_sal(text, money) returns emp as '
    update emp set salary = salary + $2 where name = $1;
    insert into sal_log values ($1, $2, getpgusername(), ''now'');
    select * from emp where name = $1;' language 'sql';
    -- ****                                       ^^^
    -- ****                                       |||

insert into emp values ('Jan', '0.00');
select change_sal('Jan', '1000.00');
CREATE
CREATE
CREATE
INSERT 18423 1
change_sal
----------
 136044880
(1 row)
    -- **** 136044880 is the memory address of the tuple table slot
    -- **** returned by the function change_sal() - very useful :-)

select * from emp;
name|salary
----+---------
Jan |$1,000.00
(1 row)

select * from sal_log;
empname|salchg   |who  |when
-------+---------+-----+--------------------------------
Jan    |$1,000.00|pgsql|Thu Aug 13 12:46:28 1998 MET DST
(1 row)
    -- **** But the result is OK so far

    -- **** Now we use the so called nested dot notation to get
    -- **** the name from the returned tuple
select name(change_sal('Jan', '1000.00'));
name
----
Jan
(1 row)
    -- **** That's right

select * from emp;
name|salary
----+------
Jan |
(1 row)

select * from sal_log;
empname|salchg   |who  |when
-------+---------+-----+--------------------------------
Jan    |$1,000.00|pgsql|Thu Aug 13 12:46:28 1998 MET DST
Jan    |$1,000.00|pgsql|Thu Aug 13 12:46:28 1998 MET DST
Jan    |         |pgsql|Thu Aug 13 12:46:28 1998 MET DST
(3 rows)
EOF
    -- **** But this isn't what anyone would expect

    Background:

    I'm not really sure if I used the correct syntax to access  a
    field from the returned tuple. If not, please correct me.

    The  reason for the behaviour is the generated querytree.  It
    is a nested function call of two times  change_sal().   There
    is  a  func  node  for change_sal() with one argument that is
    change_sal('Jan', '1000.0') func node.  Both func nodes  have
    the same targetlist about 'name'.

    At  the  beginning  of  ExecMakeFunctionResult() the argument
    from the outer is evaluated and causes the call of the  inner
    one. When the inner one finishes, it returns the name (due to
    the target list).  This single return value then replaces the
    argument    vector    for    the   outer   func   node.   Now
    postquel_function() is called but with only one argument, the
    second  expected  argument now is a NULL value. That explains
    the behaviour above.

    But don't expect

        select salary(change_sal('Jan', '1000.00'))

    to work. It causes an

        ERROR:  Tuple is too big: size 200064

    Even if  you  have  a  function  not  updating  or  inserting
    something,  the generated querytree causes the function to be
    called twice. So a simple function just doing one select  and
    returning a tuple type causes two scans.

    What  I absolutely don't know is, what is it good for? How is
    the correct syntax to access more than  one  field  from  the
    returned tuple?

    Shouldn't  the  call  of a function returning a tuple without
    the surrounding attrname(...) have a targetlist too?  If  so,
    the  complete  targetlist  must  be  used  when  building the
    projection tuple, not only the first TLE as it is implemented
    now.

    And  I think the parser/planner must not generate nested func
    nodes.

    I'm really willing to dive into, but should I do it before or
    after  6.4?  Doing it before would mean 6.4 NOT AT THE END OF
    THIS MONTH!


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

>
>     But don't expect
>
>         select salary(change_sal('Jan', '1000.00'))
>
>     to work. It causes an
>
>         ERROR:  Tuple is too big: size 200064
>
>     Even if  you  have  a  function  not  updating  or  inserting
>     something,  the generated querytree causes the function to be
>     called twice. So a simple function just doing one select  and
>     returning a tuple type causes two scans.
>
>     What  I absolutely don't know is, what is it good for? How is
>     the correct syntax to access more than  one  field  from  the
>     returned tuple?
>
>     Shouldn't  the  call  of a function returning a tuple without
>     the surrounding attrname(...) have a targetlist too?  If  so,
>     the  complete  targetlist  must  be  used  when  building the
>     projection tuple, not only the first TLE as it is implemented
>     now.
>
>     And  I think the parser/planner must not generate nested func
>     nodes.
>
>     I'm really willing to dive into, but should I do it before or
>     after  6.4?  Doing it before would mean 6.4 NOT AT THE END OF
>     THIS MONTH!

I have felt that the 'return set' has only worked in limited cases.
Vadim seems to know there are problems, particularly with free'ing
memory.

My question is whether this relates to the rules system rewrite or the
PL/SQL language module?

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

> I have felt that the 'return set' has only worked in limited cases.
> Vadim seems to know there are problems, particularly with free'ing
> memory.
>
> My question is whether this relates to the rules system rewrite or the
> PL/SQL language module?
>
> --
> Bruce Momjian                          |  830 Blythe Avenue

    It does not relate to the rule system.

    But it relates to the procedural language functions. It would
    be nice to enable tuples and setof tuples as return type  for
    PL/pgSQL and other languages. Currently this is restricted to
    'sql' functions and the return type of tuple (not  setof)  is
    broken.

    In  the  case  of  a  function  returning SETOF tupletype, it
    works.  The function

        create function f1(int4) returns setof t1 as '
            insert into t2 values (''now'');
            select * from t1 where a < $1;
        ' language 'sql';

    when called as

        select a(f1(5));

    produces a RESULT plan with one  entry  in  the  qptargetlist
    that  contains  an ITER->EXPR->FUNC where the FUNC node has a
    targetlist with one entry for attribute a.  The  function  is
    only executed once.

    But when called as

        select a(f1(5)), b(f1(5));

    produces  a  RESULT plan with two entries in the qptargetlist
    where each contains an ITER->EXPR->FUNC where the  FUNC  node
    has  again  a  targetlist  with  one  entry. The result looks
    right, but the function is executed two times (this time  two
    new  entries  in  t2).  This  might  be  right,  because  the
    arguments to f1() could differ. What I haven't found  is  the
    ability  to  evaluate  the  function  once  and  use multiple
    attributes from the return set.

    So you cannot do something like

        insert into t3 select f1(5);

    What this demonstrates clearly to me is that return types  of
    tuple or sets don't really do what they should.

    First I think the call to a function returning one tuple with
    attribute specification must be fixed. It must  not  generate
    the  same  function  call twice (one time inside the argument
    list of the outer one).

    Second the plain tuple return value must  be  useful  in  any
    way.  Currently you cannot have functions f1(int4) returns t1
    and f2(t1) and then do something like f2(f1(5)). It tells 'f2
    has bad return type ...'.

    Third  there  should be an enhancement to specify some or all
    of the attributes like

        select F.a, F.c, F.e from f1(5) F;

    or

        select * from f1(5);

    If this is done, ExecMakeFunctionResult() can be extended. If
    there  is  a targetlist in the func node of a C function (for
    ExecMake...  there is no  difference  between  C,  PL/TCL  or
    PL/pgSQL),  it  knows  that  the  return  value is a tuple or
    tupletable or temp relation or whatever and it can manage for
    the  requested  projection and for the iteration (if function
    isset).

    But should we do that all (and the rule stuff) before 6.4?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

>     there  is  a targetlist in the func node of a C function (for
>     ExecMake...  there is no  difference  between  C,  PL/TCL  or
>     PL/pgSQL),  it  knows  that  the  return  value is a tuple or
>     tupletable or temp relation or whatever and it can manage for
>     the  requested  projection and for the iteration (if function
>     isset).
>
>     But should we do that all (and the rule stuff) before 6.4?

Sorry to say this, but I think we need the rewrite stuff done for 6.4.
Too many bugs and limited features.

The PL/pgSQL perhaps can be started now, but not ready until 6.5?  I
don't think we should delay 6.4 for PL/pgSQL, do you?



--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Bruce Momjian wrote:

> Sorry to say this, but I think we need the rewrite stuff done for 6.4.
> Too many bugs and limited features.

    Thats right. Do you also agree in that only the instead rules
    should be left?

>
> The PL/pgSQL perhaps can be started now, but not ready until 6.5?  I
> don't think we should delay 6.4 for PL/pgSQL, do you?

    It is in a good, stable state now. I would like  to  have  it
    shipped with 6.4, because removing the non-instead rules is a
    loss of functionality and at least we  must  provide  another
    way.  Someone  might  currently  use  rules  for  some  extra
    actions. But this is subject to trigger procedures. Up to now
    there's only C and Tcl available for it. Not anyone likes Tcl
    -  and  writing  the  triggers  in  C  isn't  fun   for   SQL
    programmers.

    I'll  pack  it  and  send it to you after a last check of the
    code.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

> Bruce Momjian wrote:
>
> > Sorry to say this, but I think we need the rewrite stuff done for 6.4.
> > Too many bugs and limited features.
>
>     Thats right. Do you also agree in that only the instead rules
>     should be left?

Beats me.  Whatever the other developers think.

>
> >
> > The PL/pgSQL perhaps can be started now, but not ready until 6.5?  I
> > don't think we should delay 6.4 for PL/pgSQL, do you?
>
>     It is in a good, stable state now. I would like  to  have  it
>     shipped with 6.4, because removing the non-instead rules is a
>     loss of functionality and at least we  must  provide  another
>     way.  Someone  might  currently  use  rules  for  some  extra
>     actions. But this is subject to trigger procedures. Up to now
>     there's only C and Tcl available for it. Not anyone likes Tcl
>     -  and  writing  the  triggers  in  C  isn't  fun   for   SQL
>     programmers.
>
>     I'll  pack  it  and  send it to you after a last check of the
>     code.

Sure, we will ship whatever you have, even if it is not perfect.

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)