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

wieck(at)debis(dot)com (Jan Wieck) writes:
> 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've said some time (maybe too long) ago, that SQL functions
> returning tuple sets are broken in general.

Indeed they are. Try this on for size (using the regression database):

SELECT p.name, p.hobbies.equipment.name FROM person p;
SELECT p.hobbies.equipment.name, p.name FROM person p;

You get different result sets!?

The problem in this example is that ExecTargetList returns the isDone
flag from the last targetlist entry, regardless of whether there are
incomplete iterations in previous entries. More generally, the buffer
leak problem that I started with only occurs if some Iter nodes are not
run to completion --- but execQual.c has no mechanism to make sure that
they have all reached completion simultaneously.

What we really need to make functions-returning-sets work properly is
an implementation somewhat like aggregate functions. We need to make
a list of all the Iter nodes present in a targetlist and cycle through
the values returned by each in a methodical fashion (run the rightmost
through its full cycle, then advance the next-to-rightmost one value,
run the rightmost through its cycle again, etc etc). Also there needs
to be an understanding of the hierarchy when an Iter appears in the
arguments of another Iter's function. (You cycle the upper one for
*each* set of arguments created by cycling its sub-Iters.)

I am not particularly interested in working on this feature right now,
since AFAIK it's a Berkeleyism not found in SQL92. What I've done
is to hack ExecTargetList so that it behaves semi-sanely when there's
more than one Iter at the top level of the target list --- it still
doesn't really give the right answer, but at least it will keep
generating tuples until all the Iters are done at the same time.
It happens that that's enough to give correct answers for the examples
shown in the misc regress test. Even when it fails to generate all
the possible combinations, there will be no buffer leaks.

So, I'm going to declare victory and go home ;-). We ought to add a
TODO item along the lines of
* Functions returning sets don't really work right
in hopes that someone will feel like tackling this someday.

regards, tom lane