Re: [HACKERS] Progress report: buffer refcount bugs and SQL functions

Tom Lane wrote:

> [...]
>
> What I am wondering, though, is whether this addition is actually
> necessary, or is it a bug that the functions aren't run to completion
> in the first place?  I don't really understand the semantics of this
> "nested dot notation".  I suppose it is a Berkeleyism; I can't find
> anything about it in the SQL92 document.  The test cases shown in the
> misc regress test seem peculiar, not to say wrong.  For example:
>
> [...]
>
> Is the regression test's expected output wrong, or am I misunderstanding
> what this query is supposed to do?  Is there any documentation anywhere
> about how SQL functions returning multiple tuples are supposed to
> behave?

    I've  said some time (maybe too long) ago, that SQL functions
    returning tuple sets are broken in general. This  nested  dot
    notation  (which  I  think  is  an artefact from the postquel
    querylanguage) is implemented via set functions.

    Set functions have total different semantics from  all  other
    functions.   First  they  don't  really return a tuple set as
    someone might think  -  all  that  screwed  up  code  instead
    simulates  that  they  return  something you could consider a
    scan of the last SQL statement in  the  function.   Then,  on
    each  subsequent call inside of the same command, they return
    a "tupletable slot" containing the next found  tuple  (that's
    why their Func node is mangled up after the first call).

    Second  they  have  a  targetlist what I think was originally
    intended to extract attributes out  of  the  tuples  returned
    when  the above scan is asked to get the next tuple. But as I
    read the code it invokes the function again  and  this  might
    cause the resource leakage you see.

    Third,   all  this  seems  to  never  have  been  implemented
    (thought?) to the end. A targetlist  doesn't  make  sense  at
    this place because it could at max contain a single attribute
    - so a single attno would have the same  power.  And  if  set
    functions  could appear in the rangetable (FROM clause), than
    they would be treated as that and regular Var  nodes  in  the
    query would do it.

    I  think  you  shouldn't really care for that regression test
    and maybe we should disable set  functions  until  we  really
    implement stored procedures returning sets in the rangetable.

    Set  functions  where  planned  by  Stonebraker's   team   as
    something  that  today is called stored procedures. But AFAIK
    they never reached the useful state because even in  Postgres
    4.2  you haven't been able to get more than one attribute out
    of a  set  function.   It  was  a  feature  of  the  postquel
    querylanguage  that  you  could  get one attribute from a set
    function via

        RETRIEVE (attributename(setfuncname()))

    While working on the constraint  triggers  I've  came  across
    another  regression test (triggers :-) that's errorneous too.
    The funny_dup17 trigger proc executes an INSERT into the same
    relation  where it get fired for by a previous INSERT. And it
    stops this recursion only if it reaches a  nesting  level  of
    17,  which  could  only  occur  if  it  is  fired  DURING the
    execution of it's own SPI_exec(). After  Vadim  quouted  some
    SQL92  definitions  about when constraint checks and triggers
    are to be executed, I decided to fire regular triggers at the
    end  of  a  query  too.  Thus, there is absolutely no nesting
    possible for AFTER triggers resulting in an endless loop.


Jan

--

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