Date: Wed, 23 Jul 2025 17:03:20 +0900 (JST)
Message-Id: <20250723.170320.645068218702820010.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: <20250721.131214.1600596684258021437.ishii@postgresql.org>
References: <20250710.115535.1767264216072187810.ishii@postgresql.org>
	<CAGXsc+aoabb2xxyfckrHfTx4da8=s1L9ai+Y+uAS4cBPRMQD2A@mail.gmail.com>
	<20250721.131214.1600596684258021437.ishii@postgresql.org>
Mime-Version: 1.0
Content-Type: Multipart/Mixed;
 boundary="--Next_Part(Wed_Jul_23_17_03_20_2025_065)--"
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/20250723.170320.645068218702820010.ishii%40postgresql.org>
Precedence: bulk

----Next_Part(Wed_Jul_23_17_03_20_2025_065)--
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.

After thinking more, I concluded that it would be better to do the
task in ProcessFrontendReposnse(), rather than in SimpleQuery()
because we usually do not call ProcessBackendResponse() inside
functions such as SimpleQuery() that are responsible for processing
particular frontend message.

Attached is the v2 patch.

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

----Next_Part(Wed_Jul_23_17_03_20_2025_065)--
Content-Type: Application/Octet-Stream
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="v2-0001-Allow-to-accept-simple-query-even-if-extended-que.patch"

RnJvbSBhMmVjMGQyMzI2YTU2MjU3NjkwNWYxNGQ3NTM5MWQyMmEwYTA3MGIzIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBUYXRzdW8gSXNoaWkgPGlzaGlpQHBvc3RncmVzcWwub3JnPgpE
YXRlOiBXZWQsIDIzIEp1bCAyMDI1IDE2OjUzOjAzICswOTAwClN1YmplY3Q6IFtQQVRDSCB2Ml0g
QWxsb3cgdG8gYWNjZXB0IHNpbXBsZSBxdWVyeSBldmVuIGlmIGV4dGVuZGVkIHF1ZXJpZXMgZG8K
IG5vdCBlbmQuCgpSZWNlbnRseSBwZ0pEQkMgaGFzIHN0YXJ0ZWQgdG8gaXNzdWUgc2ltcGxlIHF1
ZXJ5IHdpdGhvdXQgZW5kaW5nCmV4dGVuZGVkIHF1ZXJ5IHByb3RvY29sIHNlcXVlbmNlcyB3aXRo
IHN5bmMuIFRoaXMgYnJvdWdodCBhIGRpc2FzdGVyCnRvIHBncG9vbC4gU28gcGdwb29sIHJlZnVz
ZXMgdG8gYWNjZXB0IGl0LgpodHRwczovL2dpdC5wb3N0Z3Jlc3FsLm9yZy9naXR3ZWIvP3A9cGdw
b29sMi5naXQ7YT1jb21taXQ7aD0yNDBjNjY4ZDEyMDA2NTUzNGIxZDI5OGQ2ZmFjYzg2ODM5ZmNi
YWI5CgpIb3dldmVyIHRoZSBzaXR1YXRpb24gZ290IHdvcnNlLiBQcmV2aW91c2x5IHBnSkRCQyBp
c3N1ZWQgYSBzaW1wbGUKcXVlcnkgd2l0aG91dCBlbmRpbmcgZXh0ZW5kZWQgcHJvdG9jb2wgb25s
eSB3aGVuICJhdXRvc2F2ZT1hbHdheXMiCm9wdGlvbiBpcyBnaXZlbi4gQnV0IGl0IHdhcyByZXBv
cnRlZCB0aGF0IHBnSkRCQyBoYXMgc3RhcnRlZCB0byB1c2UgaXQKZXh0ZW5zaXZlbHkuCmh0dHBz
Oi8vd3d3LnBvc3RncmVzcWwub3JnL21lc3NhZ2UtaWQvQ0FHWHNjJTJCYW9hYmIyeHh5ZmNrckhm
VHg0ZGE4JTNEczFMOWFpJTJCWSUyQnVBUzRjQlBSTVFEMkElNDBtYWlsLmdtYWlsLmNvbQoKU28g
dGhpcyBjb21taXQgZGVhbHMgd2l0aCB0aGUgc2l0dWF0aW9uLiBXaGVuIGEgc2ltcGxlIHF1ZXJ5
IGFycml2ZXMKd2l0aG91dCBmaW5pc2hpbmcgZXh0ZW5kZWQgcHJvdG9jb2wgc2VxdWVuY2VzLCBT
aW1wbGVRdWVyeSgpIGNhbGxzClByb2Nlc3NCYWNrZW5kUmVzcG9uc2UoKSB0byBwcm9jZXNzIHBl
bmRpbmcgZXh0ZW5kZWQgcXVlcnkgcHJvdG9jb2wKcmVwbGllcyBmcm9tIGJhY2tlbmQgc3VjaCBh
cyBwYXJzZSwgYmluZCBvciBjb21tYW5kIGNvbXBsZXRlIG1lc3NhZ2VzLAppbnN0ZWFkIG9mIHJl
amVjdGluZyB0aGUgc2ltcGxlIHF1ZXJ5LiBBZnRlciB0aGlzLCBTaW1wbGVRdWVyeSgpCnByb2Nl
c3MgYSBzaW1wbGUgcXVlcnkgYXMgdXN1YWwuCgpEaXNjdXNzaW9uOiBodHRwczovL3d3dy5wb3N0
Z3Jlc3FsLm9yZy9tZXNzYWdlLWlkL0NBR1hzYyUyQmFvYWJiMnh4eWZja3JIZlR4NGRhOCUzRHMx
TDlhaSUyQlklMkJ1QVM0Y0JQUk1RRDJBJTQwbWFpbC5nbWFpbC5jb20KLS0tCiBzcmMvcHJvdG9j
b2wvcG9vbF9wcm90b19tb2R1bGVzLmMgfCA2MSArKysrKysrKysrKysrKysrKysrKysrLS0tLS0t
LS0tCiAxIGZpbGUgY2hhbmdlZCwgNDQgaW5zZXJ0aW9ucygrKSwgMTcgZGVsZXRpb25zKC0pCgpk
aWZmIC0tZ2l0IGEvc3JjL3Byb3RvY29sL3Bvb2xfcHJvdG9fbW9kdWxlcy5jIGIvc3JjL3Byb3Rv
Y29sL3Bvb2xfcHJvdG9fbW9kdWxlcy5jCmluZGV4IDExYmVmZTk3OS4uZmRiNDFiYmYwIDEwMDY0
NAotLS0gYS9zcmMvcHJvdG9jb2wvcG9vbF9wcm90b19tb2R1bGVzLmMKKysrIGIvc3JjL3Byb3Rv
Y29sL3Bvb2xfcHJvdG9fbW9kdWxlcy5jCkBAIC0yMjEsMjMgKzIyMSw2IEBAIFNpbXBsZVF1ZXJ5
KFBPT0xfQ09OTkVDVElPTiAqZnJvbnRlbmQsCiAJLyogc2F2ZSBsYXN0IHF1ZXJ5IHN0cmluZyBm
b3IgbG9nZ2luZyBwdXJwb3NlICovCiAJc3RybGNweShxdWVyeV9zdHJpbmdfYnVmZmVyLCBjb250
ZW50cywgc2l6ZW9mKHF1ZXJ5X3N0cmluZ19idWZmZXIpKTsKIAotCS8qCi0JICogQ2hlY2sgaWYg
ZXh0ZW5kZWQgcXVlcnkgcHJvdG9jb2wgbWVzc2FnZSBlbmRlZC4gIElmIG5vdCwgcmVqZWN0IHRo
ZQotCSAqIHF1ZXJ5IGFuZCByYWlzZSBhbiBlcnJvciB0byB0ZXJtaW5hdGUgdGhlIHNlc3Npb24g
dG8gYXZvaWQgaGFuZ2luZyB1cC4KLQkgKiBIb3dldmVyIGlmIHdlIGFyZSBwcm9jZXNzaW5nIGEg
cmVzZXQgcXVlcnkgKGZyb250ZW5kID09IE5VTEwpLCB3ZSBza2lwCi0JICogdGhlIGNoZWNrIGFz
IHdlIGRvbid0IHdhbnQgdG8gcmFpc2UgYSBlcnJvci4KLQkgKi8KLQlpZiAoU0xfTU9ERSkKLQl7
Ci0JCWlmIChmcm9udGVuZCAhPSBOVUxMICYmCi0JCQkocG9vbF9pc19kb2luZ19leHRlbmRlZF9x
dWVyeV9tZXNzYWdlKCkgfHwKLQkJCSBwb29sX3BlbmRpbmdfbWVzc2FnZV9oZWFkX21lc3NhZ2Uo
KSkpCi0KLQkJCWVyZXBvcnQoRkFUQUwsCi0JCQkJCShlcnJtc2coInNpbXBsZSBxdWVyeSBcIiVz
XCIgYXJyaXZlZCBiZWZvcmUgZW5kaW5nIGFuIGV4dGVuZGVkIHF1ZXJ5IG1lc3NhZ2UiLAotCQkJ
CQkJCXF1ZXJ5X3N0cmluZ19idWZmZXIpKSk7Ci0JfQotCiAJLyogc2hvdyBwcyBzdGF0dXMgKi8K
IAlxdWVyeV9wc19zdGF0dXMoY29udGVudHMsIGJhY2tlbmQpOwogCkBAIC0yOTMwLDYgKzI5MTMs
NDQgQEAgUHJvY2Vzc0Zyb250ZW5kUmVzcG9uc2UoUE9PTF9DT05ORUNUSU9OICpmcm9udGVuZCwK
IAkJCQllcmVwb3J0KExPRywKIAkJCQkJCShlcnJtc2coIlF1ZXJ5IG1lc3NhZ2UgZnJvbSBmcm9u
dGVuZC4iKSwKIAkJCQkJCSBlcnJkZXRhaWwoInF1ZXJ5OiBcIiVzXCIiLCBjb250ZW50cykpKTsK
KworCQkJLyoKKwkJCSAqIENoZWNrIGlmIGV4dGVuZGVkIHF1ZXJ5IHByb3RvY29sIG1lc3NhZ2Ug
ZW5kZWQuIElmIG5vdCwgcHJvY2VzcworCQkJICogYW55IHBlbmRpbmcgcmVzcG9uc2UgZnJvbSBi
YWNrZW5kIHVzaW5nCisJCQkgKiBQcm9jZXNzQmFja2VuZFJlc3BvbnNlKCkuIEhvd2V2ZXIgaWYg
d2UgYXJlIHByb2Nlc3NpbmcgYSByZXNldAorCQkJICogcXVlcnkgKGZyb250ZW5kID09IE5VTEwp
LCB3ZSBza2lwIHRoZSBjaGVjayBhcyB3ZSBkb24ndCBuZWVkIHRvCisJCQkgKiBjYXJlIGFib3V0
IGFueSBwZW5kaW5nIHJlc3BvbnNlIGZyb20gYmFja2VuZC4KKwkJCSAqLworCQkJaWYgKFNMX01P
REUpCisJCQl7CisJCQkJaW50CQkJc3RhdGU7CisJCQkJc2hvcnQJCW51bV9maWVsZHM7CisKKwkJ
CQlpZiAoZnJvbnRlbmQgIT0gTlVMTCAmJgorCQkJCQkocG9vbF9pc19kb2luZ19leHRlbmRlZF9x
dWVyeV9tZXNzYWdlKCkgfHwKKwkJCQkJIHBvb2xfcGVuZGluZ19tZXNzYWdlX2V4aXN0cygpKSkK
KwkJCQl7CisJCQkJCWVyZXBvcnQoREVCVUcxLAorCQkJCQkJCShlcnJtc2coInNpbXBsZSBxdWVy
eSBcIiVzXCIgYXJyaXZlZCBiZWZvcmUgZW5kaW5nIGFuIGV4dGVuZGVkIHF1ZXJ5IG1lc3NhZ2Ui
LAorCQkJCQkJCQkJY29udGVudHMpKSk7CisKKwkJCQkJLyogaWYgcGVuZGluZyBtZXNzYWdlIGV4
aXN0cywgcHJvY2VzcyBpdCAqLworCQkJCQl3aGlsZSAocG9vbF9wZW5kaW5nX21lc3NhZ2VfZXhp
c3RzKCkpCisJCQkJCXsKKwkJCQkJCS8qCisJCQkJCQkgKiByZWFkX2tpbmRfZnJvbV9iYWNrZW5k
IHJlcXVpcmVzIHRoYXQgcXVlcnkgaXMKKwkJCQkJCSAqIGlucHJvZ3Jlc3MgYW5kIGRvaW5nIGV4
dGVuZGVkIHF1ZXJ5IHN0YXRlIGJlY2F1c2UKKwkJCQkJCSAqIGl0IG5lZWRzIHRvIHJlZmVyIHRv
IHByb3BlciBxdWVyeSBjb250ZXh0IGluCisJCQkJCQkgKiBzZXNzaW9uIGNvbnRleHQuCisJCQkJ
CQkgKi8KKwkJCQkJCXBvb2xfc2V0X3F1ZXJ5X2luX3Byb2dyZXNzKCk7CisJCQkJCQlwb29sX3Nl
dF9kb2luZ19leHRlbmRlZF9xdWVyeV9tZXNzYWdlKCk7CisJCQkJCQkvKiBwcm9jZXNzIHBlbmRp
bmcgcmVzcG9uc2VzIGZyb20gYmFja2VuZCAqLworCQkJCQkJUHJvY2Vzc0JhY2tlbmRSZXNwb25z
ZShmcm9udGVuZCwgYmFja2VuZCwgJnN0YXRlLCAmbnVtX2ZpZWxkcyk7CisJCQkJCX0KKwkJCQl9
CisJCQkJcG9vbF91bnNldF9kb2luZ19leHRlbmRlZF9xdWVyeV9tZXNzYWdlKCk7CisJCQl9CiAJ
CQlzdGF0dXMgPSBTaW1wbGVRdWVyeShmcm9udGVuZCwgYmFja2VuZCwgbGVuLCBjb250ZW50cyk7
CiAJCQlicmVhazsKIApAQCAtMzA4MCw2ICszMTAxLDEyIEBAIFByb2Nlc3NGcm9udGVuZFJlc3Bv
bnNlKFBPT0xfQ09OTkVDVElPTiAqZnJvbnRlbmQsCiAJcmV0dXJuIHN0YXR1czsKIH0KIAorLyoK
KyAqIFJlYWQgb25lIGJhY2tlbmQgcmVzcG9uc2UgYW5kIHByb2Nlc3MgaXQuCisgKgorICogc3Rh
dGU6IHVzZWQgZm9yIHByb2Nlc3NpbmcgcmVzZXQgcXVlcnkKKyAqIG51bV9maWVsZHM6IHVzZWQg
aW4gVjIgcHJvdG9jb2wKKyAqLwogUE9PTF9TVEFUVVMKIFByb2Nlc3NCYWNrZW5kUmVzcG9uc2Uo
UE9PTF9DT05ORUNUSU9OICpmcm9udGVuZCwKIAkJCQkJICAgUE9PTF9DT05ORUNUSU9OX1BPT0wg
KmJhY2tlbmQsCi0tIAoyLjI1LjEKCg==

----Next_Part(Wed_Jul_23_17_03_20_2025_065)----