Re: Using Expanded Objects other than Arrays from plpgsql

On Wed, Oct 23, 2024 at 9:04 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I wrote:
> > One idea I was toying with is that it doesn't matter if f()
> > throws an error so long as the plpgsql function is not executing
> > within an exception block: if the error propagates out of the plpgsql
> > function then we no longer care about the value of the variable.
> > That would very substantially weaken the requirements on how f()
> > is implemented.
>
> The more I think about this idea the better I like it. We can
> improve on the original concept a bit: the assignment can be
> within an exception block so long as the target variable is too.
> For example, consider
>
> DECLARE x float8[];
> BEGIN
> ...
> DECLARE y float8[];
> BEGIN
> x := array_append(x, 42);
> y := array_append(y, 42);
> END;
> EXCEPTION WHEN ...;
> END;
>
> Currently, both calls of array_append are subject to R/W optimization,
> so that array_append must provide a strong guarantee that it won't
> throw an error after it's begun to change the R/W object. If we
> redefine things so that the optimization is applied only to "y",
> then AFAICS we need nothing from array_append. It only has to be
> sure it doesn't corrupt the object so badly that it can't be freed
> ... but that requirement exists already, for anything dealing with
> expanded objects. So this would put us in a situation where we
> could apply the optimization by default, which'd be a huge win.
>

Great! I can make that same guarantee.

> There is an exception: if we are considering
>
> x := array_cat(x, x);
>
> then I don't think we can optimize because of the aliasing problem
> I mentioned before. So there'd have to be a restriction that the
> target variable is mentioned only once in the function's arguments.
> For stuff like your vxm() function, that'd be annoying. But functions
> that need that and are willing to deal with the aliasing hazard could
> still provide a prosupport function that promises it's okay. What
> we'd accomplish is that a large fraction of interesting functions
> could get the benefit without having to create a prosupport function,
> which is a win all around.
>

I see, I'm not completely clear on the prosupport code so let me repeat it
back just so I know I'm getting it right, it looks like I'll need to write
a C function, that I specify with CREATE FUNCTION ... SUPPORT, that the
planner will call asking me to tell it that it's ok to alias arguments
(which is fine for SuiteSparse so no problem). You also mentioned a couple
emails back about a "type support" feature similar to prosupport, that
would allow me to specify an eager expansion function for my types.

> Also worth noting: in the above example, we could optimize the
> update on "x" too, if we know that "x" is not referenced in the
> block's EXCEPTION handlers. I wouldn't bother with this in the
> first version, but it might be worth doing later.
>
> So if we go this way, the universe of functions that can benefit
> from the optimization enlarges considerably, and the risk of bugs
> that break the optimization drops considerably. The cost is that
> some cases that were optimized before now will not be. But I
> suspect that plpgsql functions where this optimization is key
> probably don't contain EXCEPTION handlers at all, so that they
> won't notice any change.
>

Sounds like a good tradeoff to me! Hopefully if anyone does have concerns
with this approach they'll see this thread and comment.

Thanks,

-Michel

>
> regards, tom lane
>