Date: Mon, 21 Jul 2025 13:12:14 +0900 (JST)
Message-Id: <20250721.131214.1600596684258021437.ishii@postgresql.org>
To: emond.papegaaij@gmail.com
Cc: pgpool-general@lists.postgresql.org
Subject: Re: FATAL: simple query "BEGIN" arrived before ending an extended
 query message
From: Tatsuo Ishii <ishii@postgresql.org>
In-Reply-To: 
 <CAGXsc+aoabb2xxyfckrHfTx4da8=s1L9ai+Y+uAS4cBPRMQD2A@mail.gmail.com>
References: 
 <CAGXsc+YizFbO1kOhnNq_oRz9F2iDtjCGGq0oNA+BjcQPqJy8tg@mail.gmail.com>
	<20250710.115535.1767264216072187810.ishii@postgresql.org>
	<CAGXsc+aoabb2xxyfckrHfTx4da8=s1L9ai+Y+uAS4cBPRMQD2A@mail.gmail.com>
Mime-Version: 1.0
Content-Type: Multipart/Mixed;
 boundary="--Next_Part(Mon_Jul_21_13_12_14_2025_691)--"
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/20250721.131214.1600596684258021437.ishii%40postgresql.org>
Precedence: bulk

----Next_Part(Mon_Jul_21_13_12_14_2025_691)--
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi Emond,

>> > 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.

I created a patch for pgpool to allow a simple query without ending a
sequence of extended query protocol message by a sync message.

The idea is, within SimpleQuery() (a function responsible for
processing simple protocol query), check whether extended query
protocol ends. If not, call ProcessBackendReposnse() to process any
replies from backend such as parse complete, bind complete or command
complete and so.  After this, SimpleQuery() can process simple query
as usual. I hope this solves the issue with PgJDBC 42.7.6.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

----Next_Part(Mon_Jul_21_13_12_14_2025_691)--
Content-Type: Application/Octet-Stream
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="allow_simple_query_after_extended_query.patch"

ZGlmZiAtLWdpdCBhL3NyYy9wcm90b2NvbC9wb29sX3Byb3RvX21vZHVsZXMuYyBiL3NyYy9wcm90
b2NvbC9wb29sX3Byb3RvX21vZHVsZXMuYwppbmRleCAxMWJlZmU5NzkuLmUwMzBlN2I2ZCAxMDA2
NDQKLS0tIGEvc3JjL3Byb3RvY29sL3Bvb2xfcHJvdG9fbW9kdWxlcy5jCisrKyBiL3NyYy9wcm90
b2NvbC9wb29sX3Byb3RvX21vZHVsZXMuYwpAQCAtMjIyLDIwICsyMjIsMzkgQEAgU2ltcGxlUXVl
cnkoUE9PTF9DT05ORUNUSU9OICpmcm9udGVuZCwKIAlzdHJsY3B5KHF1ZXJ5X3N0cmluZ19idWZm
ZXIsIGNvbnRlbnRzLCBzaXplb2YocXVlcnlfc3RyaW5nX2J1ZmZlcikpOwogCiAJLyoKLQkgKiBD
aGVjayBpZiBleHRlbmRlZCBxdWVyeSBwcm90b2NvbCBtZXNzYWdlIGVuZGVkLiAgSWYgbm90LCBy
ZWplY3QgdGhlCi0JICogcXVlcnkgYW5kIHJhaXNlIGFuIGVycm9yIHRvIHRlcm1pbmF0ZSB0aGUg
c2Vzc2lvbiB0byBhdm9pZCBoYW5naW5nIHVwLgotCSAqIEhvd2V2ZXIgaWYgd2UgYXJlIHByb2Nl
c3NpbmcgYSByZXNldCBxdWVyeSAoZnJvbnRlbmQgPT0gTlVMTCksIHdlIHNraXAKLQkgKiB0aGUg
Y2hlY2sgYXMgd2UgZG9uJ3Qgd2FudCB0byByYWlzZSBhIGVycm9yLgorCSAqIENoZWNrIGlmIGV4
dGVuZGVkIHF1ZXJ5IHByb3RvY29sIG1lc3NhZ2UgZW5kZWQuICBJZiBub3QsIHByb2Nlc3MgYW55
CisJICogcGVuZGluZyByZXNwb25zZSBmcm9tIGJhY2tlbmQgdXNpbmcgUHJvY2Vzc0JhY2tlbmRS
ZXNwb25zZSgpLiAgSG93ZXZlcgorCSAqIGlmIHdlIGFyZSBwcm9jZXNzaW5nIGEgcmVzZXQgcXVl
cnkgKGZyb250ZW5kID09IE5VTEwpLCB3ZSBza2lwIHRoZQorCSAqIGNoZWNrIGFzIHdlIGRvbid0
IG5lZWQgdG8gY2FyZSBhYm91dCBhbnkgcGVuZGluZyByZXNwb25zZSBmcm9tIGJhY2tlbmQuCiAJ
ICovCiAJaWYgKFNMX01PREUpCiAJeworCQlpbnQJc3RhdGU7CisJCXNob3J0CW51bV9maWVsZHM7
CisKIAkJaWYgKGZyb250ZW5kICE9IE5VTEwgJiYKIAkJCShwb29sX2lzX2RvaW5nX2V4dGVuZGVk
X3F1ZXJ5X21lc3NhZ2UoKSB8fAotCQkJIHBvb2xfcGVuZGluZ19tZXNzYWdlX2hlYWRfbWVzc2Fn
ZSgpKSkKLQotCQkJZXJlcG9ydChGQVRBTCwKKwkJCSBwb29sX3BlbmRpbmdfbWVzc2FnZV9leGlz
dHMoKSkpCisJCXsKKwkJCWVyZXBvcnQoREVCVUcxLAogCQkJCQkoZXJybXNnKCJzaW1wbGUgcXVl
cnkgXCIlc1wiIGFycml2ZWQgYmVmb3JlIGVuZGluZyBhbiBleHRlbmRlZCBxdWVyeSBtZXNzYWdl
IiwKIAkJCQkJCQlxdWVyeV9zdHJpbmdfYnVmZmVyKSkpOworCisJCQkvKiBpZiBwZW5kaW5nIG1l
c3NhZ2UgZXhpc3RzLCBwcm9jZXNzIGl0ICovCisJCQl3aGlsZSAocG9vbF9wZW5kaW5nX21lc3Nh
Z2VfZXhpc3RzKCkpCisJCQl7CisJCQkJLyoKKwkJCQkgKiByZWFkX2tpbmRfZnJvbV9iYWNrZW5k
IHJlcXVpcmVzIHRoYXQgcXVlcnkgaXMgaW5wcm9ncmVzcworCQkJCSAqIGFuZCBkb2luZyBleHRl
bmRlZCBxdWVyeSBzdGF0ZSBiZWNhdXNlIGl0IG5lZWRzIHRvIHJlZmVyIHRvCisJCQkJICogcHJv
cGVyIHF1ZXJ5IGNvbnRleHQgaW4gc2Vzc2lvbiBjb250ZXh0LgorCQkJCSAqLworCQkJCXBvb2xf
c2V0X3F1ZXJ5X2luX3Byb2dyZXNzKCk7CisJCQkJcG9vbF9zZXRfZG9pbmdfZXh0ZW5kZWRfcXVl
cnlfbWVzc2FnZSgpOworCQkJCS8qIHByb2Nlc3MgcGVuZGluZyByZXNwb25zZXMgZnJvbSBiYWNr
ZW5kICovCisJCQkJUHJvY2Vzc0JhY2tlbmRSZXNwb25zZShmcm9udGVuZCwgYmFja2VuZCwgJnN0
YXRlLCAmbnVtX2ZpZWxkcyk7CisJCQl9CisJCX0KKwkJcG9vbF91bnNldF9kb2luZ19leHRlbmRl
ZF9xdWVyeV9tZXNzYWdlKCk7CiAJfQogCiAJLyogc2hvdyBwcyBzdGF0dXMgKi8KQEAgLTMwODAs
NiArMzA5OSwxMiBAQCBQcm9jZXNzRnJvbnRlbmRSZXNwb25zZShQT09MX0NPTk5FQ1RJT04gKmZy
b250ZW5kLAogCXJldHVybiBzdGF0dXM7CiB9CiAKKy8qCisgKiBSZWFkIG9uZSBiYWNrZW5kIHJl
c3BvbnNlIGFuZCBwcm9jZXNzIGl0LgorICoKKyAqIHN0YXRlOiB1c2VkIGZvciBwcm9jZXNzaW5n
IHJlc2V0IHF1ZXJ5CisgKiBudW1fZmllbGRzOiB1c2VkIGluIFYyIHByb3RvY29sCisgKi8KIFBP
T0xfU1RBVFVTCiBQcm9jZXNzQmFja2VuZFJlc3BvbnNlKFBPT0xfQ09OTkVDVElPTiAqZnJvbnRl
bmQsCiAJCQkJCSAgIFBPT0xfQ09OTkVDVElPTl9QT09MICpiYWNrZW5kLAo=

----Next_Part(Mon_Jul_21_13_12_14_2025_691)----