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(at)debis(dot)com (Jan Wieck) #