MIME-Version: 1.0
References: <CACxu=vJaKFNsYxooSnW1wEgsAO5u_v1XYBacfVJ14wgJV_PYeg@mail.gmail.com>
 <1342498.1729444411@sss.pgh.pa.us> <CACxu=vLXvpzN4X3k+9jsMt6ujuOvFVUSkA80t_cROSsF4y2jQQ@mail.gmail.com>
 <1445998.1729482404@sss.pgh.pa.us> <CACxu=vKEF8Qa-OaADFxf0uMg-xw6gH_CNCWd2s+xaqh-gY4=xg@mail.gmail.com>
 <2062830.1729625620@sss.pgh.pa.us> <2265411.1729699470@sss.pgh.pa.us>
In-Reply-To: <2265411.1729699470@sss.pgh.pa.us>
From: Michel Pelletier <pelletier.michel@gmail.com>
Date: Wed, 23 Oct 2024 19:10:56 -0700
Message-ID: <CACxu=v++HNmss59yGUDkRny7g=M8tZ2YXF07AUXqKVGqcSfxGQ@mail.gmail.com>
Subject: Re: Using Expanded Objects other than Arrays from plpgsql
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000071df7f06252f8531"
Archived-At: <https://www.postgresql.org/message-id/CACxu%3Dv%2B%2BHNmss59yGUDkRny7g%3DM8tZ2YXF07AUXqKVGqcSfxGQ%40mail.gmail.com>
Precedence: bulk

--00000000000071df7f06252f8531
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Oct 23, 2024 at 9:04=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.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 :=3D array_append(x, 42);
>               y :=3D 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 :=3D 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
>

--00000000000071df7f06252f8531
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Oct 23, 2024 at 9:04=E2=80=AFAM T=
om Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; =
wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote=
" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);=
padding-left:1ex">I wrote:<br>
&gt; One idea I was toying with is that it doesn&#39;t matter if f()<br>
&gt; throws an error so long as the plpgsql function is not executing<br>
&gt; within an exception block: if the error propagates out of the plpgsql<=
br>
&gt; function then we no longer care about the value of the variable.<br>
&gt; That would very substantially weaken the requirements on how f()<br>
&gt; is implemented.<br>
<br>
The more I think about this idea the better I like it.=C2=A0 We can<br>
improve on the original concept a bit: the assignment can be<br>
within an exception block so long as the target variable is too.<br>
For example, consider<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 DECLARE x float8[];<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 BEGIN<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0...<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0DECLARE y float8[];<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0BEGIN<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 x :=3D array_append(x, 42)=
;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 y :=3D array_append(y, 42)=
;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0END;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 EXCEPTION WHEN ...;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 END;<br>
<br>
Currently, both calls of array_append are subject to R/W optimization,<br>
so that array_append must provide a strong guarantee that it won&#39;t<br>
throw an error after it&#39;s begun to change the R/W object.=C2=A0 If we<b=
r>
redefine things so that the optimization is applied only to &quot;y&quot;,<=
br>
then AFAICS we need nothing from array_append.=C2=A0 It only has to be<br>
sure it doesn&#39;t corrupt the object so badly that it can&#39;t be freed<=
br>
... but that requirement exists already, for anything dealing with<br>
expanded objects.=C2=A0 So this would put us in a situation where we<br>
could apply the optimization by default, which&#39;d be a huge win.<br></bl=
ockquote><div><br></div><div>Great!=C2=A0 I can make that same guarantee.</=
div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px =
0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
There is an exception: if we are considering<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 x :=3D array_cat(x, x);<br=
>
<br>
then I don&#39;t think we can optimize because of the aliasing problem<br>
I mentioned before.=C2=A0 So there&#39;d have to be a restriction that the<=
br>
target variable is mentioned only once in the function&#39;s arguments.<br>
For stuff like your vxm() function, that&#39;d be annoying.=C2=A0 But funct=
ions<br>
that need that and are willing to deal with the aliasing hazard could<br>
still provide a prosupport function that promises it&#39;s okay.=C2=A0 What=
<br>
we&#39;d accomplish is that a large fraction of interesting functions<br>
could get the benefit without having to create a prosupport function,<br>
which is a win all around.<br></blockquote><div><br></div><div>I see, I&#39=
;m not completely clear on the prosupport code so let me repeat it back jus=
t so I know I&#39;m getting it right, it looks like I&#39;ll need to write =
a C function, that I specify with CREATE FUNCTION ... SUPPORT, that the pla=
nner will call asking me to tell it that it&#39;s ok to alias arguments (wh=
ich is fine for SuiteSparse so no problem).=C2=A0 You also mentioned a coup=
le emails back about a &quot;type support&quot; feature similar to prosuppo=
rt, that would allow me to specify an eager expansion function for my types=
.</div><div>=C2=A0<br></div><blockquote class=3D"gmail_quote" style=3D"marg=
in:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1e=
x">
Also worth noting: in the above example, we could optimize the<br>
update on &quot;x&quot; too, if we know that &quot;x&quot; is not reference=
d in the<br>
block&#39;s EXCEPTION handlers.=C2=A0 I wouldn&#39;t bother with this in th=
e<br>
first version, but it might be worth doing later.<br>
<br>
So if we go this way, the universe of functions that can benefit<br>
from the optimization enlarges considerably, and the risk of bugs<br>
that break the optimization drops considerably.=C2=A0 The cost is that<br>
some cases that were optimized before now will not be.=C2=A0 But I<br>
suspect that plpgsql functions where this optimization is key<br>
probably don&#39;t contain EXCEPTION handlers at all, so that they<br>
won&#39;t notice any change.<br></blockquote><div><br></div><div>Sounds lik=
e a good tradeoff to me!=C2=A0 Hopefully if anyone does have concerns with =
this approach they&#39;ll see this thread and comment.</div><div><br></div>=
<div>Thanks,</div><div><br></div><div>-Michel</div><blockquote class=3D"gma=
il_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,2=
04,204);padding-left:1ex">
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div></div>

--00000000000071df7f06252f8531--