MIME-Version: 1.0
References: 
 <CAHgTRfcg2jGnjSqq1NVuMDtCjRU3aOXWrhXn1L=92Ka_Ug6euw@mail.gmail.com>
 <60ab210d02748b5da813007ab2504606d0fecaa1.camel@cybertec.at>
 <CAHgTRfe+REHqYoQJ_ykj_p416PbdHz+UN06-R3PVr6auMx4w2g@mail.gmail.com>
 <CAHgTRfeXGN9d7vgg8LtwpNYw_+f-zmMYsOc1U9rhXkGcwWtNng@mail.gmail.com>
 <CADK3HHLkuDpfVKfzckH5z-e1_Z8DH-9WdOWaafbJ4HRuRqnsdg@mail.gmail.com>
 <2950.1711729266@sss.pgh.pa.us>
In-Reply-To: <2950.1711729266@sss.pgh.pa.us>
From: Dave Cramer <davecramer@postgres.rocks>
Date: Fri, 29 Mar 2024 12:48:26 -0400
Message-ID: 
 <CADK3HHLx1u-RbR_v5UbmK_v4_sOfzHxdYwxtn5QRRWV-=Re6MQ@mail.gmail.com>
Subject: Re: cached plan must not change result type
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: James Pang <jamespang886@gmail.com>, pgsql-jdbc@lists.postgresql.org,
	Laurenz Albe <laurenz.albe@cybertec.at>
Content-Type: multipart/alternative; boundary="000000000000aefb9b0614cf69d1"
Archived-At: 
 <https://www.postgresql.org/message-id/CADK3HHLx1u-RbR_v5UbmK_v4_sOfzHxdYwxtn5QRRWV-%3DRe6MQ%40mail.gmail.com>
Precedence: bulk

--000000000000aefb9b0614cf69d1
Content-Type: text/plain; charset="UTF-8"

On Fri, 29 Mar 2024 at 12:21, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Dave Cramer <davecramer@postgres.rocks> writes:
> > This is really an issue that needs to be solved in the backend. The error
> > is coming from PostgreSQL and what should happen is that when you alter a
> > table that a server prepared statement relies on the backend should send
> a
> > message to tell us that all of the prepared statements that rely on are
> now
> > invalid and we can reprepare them.
>
> This is something that can't change without a wire protocol change.
> There is nothing in the protocol that allows the backend to send out
> a message like "oh, that Describe I sent you awhile back?  It might
> be a lie now" at random times.


I agree, but it's a known issue. I'm just pointing that it would be nice to
have.
We'd have to figure out the details.


> Also, what do you want to do about
> race conditions --- that is, what if you fire off an Execute only
> to find that one of those messages was already in flight to you?


> A non-racy way to handle it might be for Bind/Execute to refuse to
> run the query if its output has changed since the last Describe,
> which we could check after acquiring table locks during Bind.
> But we'd want to define "refuse" in a way that doesn't abort the
> transaction, and that's a concept that doesn't exist in the
> protocol at all.
>

This actually sounds like the best option as we wouldn't have to fire off a
message, just refuse to run the Execute in a way that doesn't abort the
transaction.

Dave

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Fri, 29 Mar 2024 at 12:21, Tom Lan=
e &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:=
<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8=
ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Dave Cramer &lt=
;davecramer@postgres.rocks&gt; writes:<br>
&gt; This is really an issue that needs to be solved in the backend. The er=
ror<br>
&gt; is coming from PostgreSQL and what should happen is that when you alte=
r a<br>
&gt; table that a server prepared statement relies on the backend should se=
nd a<br>
&gt; message to tell us that all of the prepared statements that rely on ar=
e now<br>
&gt; invalid and we can reprepare them.<br>
<br>
This is something that can&#39;t change without a wire protocol change.<br>
There is nothing in the protocol that allows the backend to send out<br>
a message like &quot;oh, that Describe I sent you awhile back?=C2=A0 It mig=
ht<br>
be a lie now&quot; at random times.=C2=A0 </blockquote><div><br></div><div>=
I agree, but it&#39;s a known=C2=A0issue. I&#39;m just pointing that it wou=
ld be nice to have.</div><div>We&#39;d have to figure out the details.</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">Also, w=
hat do you want to do about<br>
race conditions --- that is, what if you fire off an Execute only<br>
to find that one of those messages was already in flight to you?</blockquot=
e><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;borde=
r-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
A non-racy way to handle it might be for Bind/Execute to refuse to<br>
run the query if its output has changed since the last Describe,<br>
which we could check after acquiring table locks during Bind.<br>
But we&#39;d want to define &quot;refuse&quot; in a way that doesn&#39;t ab=
ort the<br>
transaction, and that&#39;s a concept that doesn&#39;t exist in the<br>
protocol at all.<br></blockquote><div><br></div><div>This actually sounds l=
ike the best option as we wouldn&#39;t have to fire off a message, just ref=
use to run the Execute in a way that doesn&#39;t abort the transaction.</di=
v><div><br></div><div>Dave</div></div></div>

--000000000000aefb9b0614cf69d1--