Re: Possible bug (or at least unexpected behavior)

I wrote:
> ... It looks like memory management
> in SQL functions is not coping well with expanded arrays, but I'm
> not quite sure where it's going off the rails.

It doesn't seem to be a memory management problem, but rather that
SQL functions are being careless with arguments that are read/write
expanded Datums. A function that is passed such an argument is
allowed to modify it in-place, and array_append does so to reduce
the expense of repeatedly appending to an array value. If you
have two array_append's referencing the same parameter in a SQL
function, and that parameter is passed in as a R/W datum, you
get the wrong answer: the second array_append will see the effects
of the first one. Also, if the SQL function does array_append
first and array_cardinality second, array_cardinality reports the
wrong result.

Now, it doesn't look like your example function does either of those
things, but it turns out that it does after function inlining. The
planner effectively flattens out one level of the recursion, creating
a plan in which we do have these hazards.

The only practical fix I can see is to force SQL function parameter
values to read-only. We could do better if we knew which parameters
are actually multiply referenced in the function, but we don't have
the infrastructure needed to detect that. I'm not convinced that
it'd be appropriate to expend a lot of effort here --- non-inlined
execution of a SQL function is a pretty low-performance situation in
any case. So I've just gone for the simplest possible fix in the
attached.

regards, tom lane