MIME-Version: 1.0
References: 
 <CAGXsc+YizFbO1kOhnNq_oRz9F2iDtjCGGq0oNA+BjcQPqJy8tg@mail.gmail.com>
 <20250710.115535.1767264216072187810.ishii@postgresql.org>
In-Reply-To: <20250710.115535.1767264216072187810.ishii@postgresql.org>
From: Emond Papegaaij <emond.papegaaij@gmail.com>
Date: Thu, 10 Jul 2025 08:34:53 +0200
Message-ID: 
 <CAGXsc+aoabb2xxyfckrHfTx4da8=s1L9ai+Y+uAS4cBPRMQD2A@mail.gmail.com>
Subject: Re: FATAL: simple query "BEGIN" arrived before ending an extended
 query message
To: Tatsuo Ishii <ishii@postgresql.org>
Cc: pgpool-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000ca352806398d64af"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGXsc%2Baoabb2xxyfckrHfTx4da8%3Ds1L9ai%2BY%2BuAS4cBPRMQD2A%40mail.gmail.com>
Precedence: bulk

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

>
> > The problem however, is that previously the error was only triggered when
> > using autosave, which is not a common setup, but now the error is
> triggered
> > even when using default configuration. This makes it impossible to
> combine
> > PgJDBC 42.7.6 and up with pgpool-II. I do not know what the plans are on
> > this from the side of PostgreSQL and PgJDBC, but I just wanted to raise
> > some awareness on this issue here. IMHO PostgreSQL should either
> explicitly
> > allow this (in which case pgpool-II needs to be fixed) or explicitly
> > disallow it (in which case PgJDBC needs to be fixed).
>
> As far as I know about PostgreSQL's side, Tom Lane said about this:
> https://www.postgresql.org/message-id/2069511.1706571615@sss.pgh.pa.us
>
> > I think it's poor practice, at best.  You should end the
> > extended-protocol query cycle before invoking simple query.
> >
> > I'm disinclined to document, or make any promises about,
> > what happens if you mix the protocols.
>
> In my understanding he does not say PostgreSQL explicitely allows this
> (mixing extended and simple protocol message).
>

It's more or less allowed without any errors, but the expected behavior is
not clear nor is it documented. It seems libpq even throws an error on the
client side when you try to do this:

 Jelte Fennema-Nio in
https://www.postgresql.org/message-id/CAGECzQQ1hs2DU9pmQq18Y%3DqK4nZqhXDVg-sGEa5K01Lj4XMmxw%40mail.gmail.com
> I totally agree that it makes sense to throw an error in this case.
> Libpq actually throws an error client side when a caller attempts to
> do this, but this is something that should be checked server side,
> given that the protocol docs specify this:
> docs> At completion of each series of extended-query messages, the
> frontend should issue a Sync message.


> >  The current situation
> > is no good as we now simply cannot upgrade PgJDBC anymore (and the same
> > will be true for all other users of pgpool-II).
>
> Yeah.
>
> What I don't understand is, why PgJDBC decided to make it default
> (sending simple protocol query after extended query protocl without
> sync) even without autosave being set when they update PgJDBC to
> 42.7.7.
>

It wasn't a real decision to make this happen more often. They fixed a bug
that caused many queries to be sent using the extended protocol even when
explicitly asked for a simple query:
https://github.com/pgjdbc/pgjdbc/issues/3724#issuecomment-3051773696

In our case we got sporadic errors. These errors are very hard to
reproduce. Some users were not able to login into our application at all,
others had no trouble whatsoever and some users got an error every once in
a while. Even when tests seem fine, you might get these errors later on.
None of our automated tests failed. On our testing environment we've only
seen the error twice in a period of 3 weeks. When we moved to production,
we triggered the error over 100 times in just 1 day! I would certainly not
recommend using PgJDBC 42.7.6 and up when using pgpool.

Best regards,
Emond

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

<div dir=3D"ltr"><div class=3D"gmail_quote gmail_quote_container"><blockquo=
te class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px =
solid rgb(204,204,204);padding-left:1ex">&gt; The problem however, is that =
previously the error was only triggered when<br>
&gt; using autosave, which is not a common setup, but now the error is trig=
gered<br>
&gt; even when using default configuration. This makes it impossible to com=
bine<br>
&gt; PgJDBC 42.7.6 and up with pgpool-II. I do not know what the plans are =
on<br>
&gt; this from the side of PostgreSQL and PgJDBC, but I just wanted to rais=
e<br>
&gt; some awareness on this issue here. IMHO PostgreSQL should either expli=
citly<br>
&gt; allow this (in which case pgpool-II needs to be fixed) or explicitly<b=
r>
&gt; disallow it (in which case PgJDBC needs to be fixed).<br>
<br>
As far as I know about PostgreSQL&#39;s side, Tom Lane said about this:<br>
<a href=3D"https://www.postgresql.org/message-id/2069511.1706571615@sss.pgh=
.pa.us" rel=3D"noreferrer" target=3D"_blank">https://www.postgresql.org/mes=
sage-id/2069511.1706571615@sss.pgh.pa.us</a><br>
<br>
&gt; I think it&#39;s poor practice, at best.=C2=A0 You should end the<br>
&gt; extended-protocol query cycle before invoking simple query.<br>
&gt; <br>
&gt; I&#39;m disinclined to document, or make any promises about,<br>
&gt; what happens if you mix the protocols.<br>
<br>
In my understanding he does not say PostgreSQL explicitely allows this<br>
(mixing extended and simple protocol message).<br></blockquote><div><br></d=
iv><div>It&#39;s more or less allowed without any errors, but the expected =
behavior is not clear nor is it documented. It seems libpq even throws an e=
rror on the client side when you try to do this:</div><div><br></div><div>=
=C2=A0Jelte Fennema-Nio in=C2=A0<a href=3D"https://www.postgresql.org/messa=
ge-id/CAGECzQQ1hs2DU9pmQq18Y%3DqK4nZqhXDVg-sGEa5K01Lj4XMmxw%40mail.gmail.co=
m">https://www.postgresql.org/message-id/CAGECzQQ1hs2DU9pmQq18Y%3DqK4nZqhXD=
Vg-sGEa5K01Lj4XMmxw%40mail.gmail.com</a></div><div>&gt; I totally agree tha=
t it makes sense to throw an error in this case.<br>&gt; Libpq actually thr=
ows an error client side when a caller attempts to<br>&gt; do this, but thi=
s is something that should be checked server side,<br>&gt; given that the p=
rotocol docs specify this:<br>&gt; docs&gt; At completion of each series of=
 extended-query messages, the<br>&gt; frontend should issue a Sync message.=
</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0p=
x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
&gt;=C2=A0 The current situation<br>
&gt; is no good as we now simply cannot upgrade PgJDBC anymore (and the sam=
e<br>
&gt; will be true for all other users of pgpool-II).<br>
<br>
Yeah.<br>
<br>
What I don&#39;t understand is, why PgJDBC decided to make it default<br>
(sending simple protocol query after extended query protocl without<br>
sync) even without autosave being set when they update PgJDBC to<br>
42.7.7.<br></blockquote><div><br></div><div>It wasn&#39;t a real decision t=
o make this happen more often. They fixed a bug that caused many queries to=
 be sent using the extended protocol even when explicitly asked for a simpl=
e query:=C2=A0<a href=3D"https://github.com/pgjdbc/pgjdbc/issues/3724#issue=
comment-3051773696">https://github.com/pgjdbc/pgjdbc/issues/3724#issuecomme=
nt-3051773696</a></div><div><br></div><div>In our case we got sporadic erro=
rs. These errors are very hard to reproduce. Some users were not able to lo=
gin into our application at all, others had no trouble whatsoever and some =
users got an error every once in a while. Even when tests seem fine, you mi=
ght get these errors later on. None of our automated tests failed. On our t=
esting environment we&#39;ve only seen the error twice in a period of 3 wee=
ks. When we moved to production, we triggered the error over 100 times in j=
ust 1 day! I would certainly not recommend using PgJDBC 42.7.6 and up when =
using pgpool.</div><div><br></div><div>Best regards,</div><div>Emond</div><=
/div></div>

--000000000000ca352806398d64af--