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

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

This is the v3 patch. Fix regression test 082.guard_against_bad_protocol.
Slightly modify commit message.

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

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

RnJvbSA4NDQ0ZTI4N2MxMjY3NmFkZWU2YmQyNzMyOGJiOWNlYTI2MWY0NmQxIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBUYXRzdW8gSXNoaWkgPGlzaGlpQHBvc3RncmVzcWwub3JnPgpE
YXRlOiBUaHUsIDI0IEp1bCAyMDI1IDEwOjM0OjU5ICswOTAwClN1YmplY3Q6IFtQQVRDSCB2M10g
QWxsb3cgdG8gYWNjZXB0IHNpbXBsZSBxdWVyeSBldmVuIGlmIGV4dGVuZGVkIHF1ZXJpZXMgZG8K
IG5vdCBlbmQuCgpSZWNlbnRseSBwZ0pEQkMgaGFzIHN0YXJ0ZWQgdG8gaXNzdWUgc2ltcGxlIHF1
ZXJ5IHdpdGhvdXQgZW5kaW5nCmV4dGVuZGVkIHF1ZXJ5IHByb3RvY29sIHNlcXVlbmNlcyB3aXRo
IHN5bmMuIFRoaXMgYnJvdWdodCBhIGRpc2FzdGVyCnRvIHBncG9vbC4gU28gcGdwb29sIHJlZnVz
ZWQgdG8gYWNjZXB0IGl0LgpodHRwczovL2dpdC5wb3N0Z3Jlc3FsLm9yZy9naXR3ZWIvP3A9cGdw
b29sMi5naXQ7YT1jb21taXQ7aD0yNDBjNjY4ZDEyMDA2NTUzNGIxZDI5OGQ2ZmFjYzg2ODM5ZmNi
YWI5CgpIb3dldmVyIHRoZSBzaXR1YXRpb24gZ290IHdvcnNlLiBQcmV2aW91c2x5IHBnSkRCQyBp
c3N1ZWQgYSBzaW1wbGUKcXVlcnkgd2l0aG91dCBlbmRpbmcgZXh0ZW5kZWQgcHJvdG9jb2wgb25s
eSB3aGVuICJhdXRvc2F2ZT1hbHdheXMiCm9wdGlvbiBpcyBnaXZlbi4gQnV0IGl0IHdhcyByZXBv
cnRlZCB0aGF0IHBnSkRCQyBoYXMgc3RhcnRlZCB0byB1c2UgaXQKZXh0ZW5zaXZlbHkuCmh0dHBz
Oi8vd3d3LnBvc3RncmVzcWwub3JnL21lc3NhZ2UtaWQvQ0FHWHNjJTJCYW9hYmIyeHh5ZmNrckhm
VHg0ZGE4JTNEczFMOWFpJTJCWSUyQnVBUzRjQlBSTVFEMkElNDBtYWlsLmdtYWlsLmNvbQoKU28g
dGhpcyBjb21taXQgZGVhbHMgd2l0aCB0aGUgc2l0dWF0aW9uLgoKV2hlbiBhIHNpbXBsZSBxdWVy
eSBhcnJpdmVzIHdpdGhvdXQgZmluaXNoaW5nIGV4dGVuZGVkIHByb3RvY29sCnNlcXVlbmNlcywg
YmVmb3JlIGNhbGxpbmcgU2ltcGxlUXVlcnkoKSwgUHJvY2Vzc0JhY2tlbmRSZXNwb25zZSgpIGlz
CmNhbGxlZCB0byBwcm9jZXNzIHBlbmRpbmcgZXh0ZW5kZWQgcXVlcnkgcHJvdG9jb2wgcmVwbGll
cyBmcm9tIGJhY2tlbmQKc3VjaCBhcyBwYXJzZSwgYmluZCBvciBjb21tYW5kIGNvbXBsZXRlIG1l
c3NhZ2VzLCBpbnN0ZWFkIG9mIHJlamVjdGluZwp0aGUgc2ltcGxlIHF1ZXJ5LiBBZnRlciB0aGlz
LCBTaW1wbGVRdWVyeSgpIGNhbiBwcm9jZXNzIHRoZSBzaW1wbGUKcXVlcnkgYXMgdXN1YWwuCgpB
dXRob3I6IFRhdHN1byBJc2hpaSA8aXNoaWlAcG9zdGdyZXNxbC5vcmc+ClJlcG9ydGVkLWJ5OiBF
bW9uZCBQYXBlZ2FhaWogPGVtb25kLnBhcGVnYWFpakBnbWFpbC5jb20+CkRpc2N1c3Npb246IGh0
dHBzOi8vd3d3LnBvc3RncmVzcWwub3JnL21lc3NhZ2UtaWQvQ0FHWHNjJTJCYW9hYmIyeHh5ZmNr
ckhmVHg0ZGE4JTNEczFMOWFpJTJCWSUyQnVBUzRjQlBSTVFEMkElNDBtYWlsLmdtYWlsLmNvbQot
LS0KIHNyYy9wcm90b2NvbC9wb29sX3Byb3RvX21vZHVsZXMuYyAgICAgICAgICAgICB8IDYxICsr
KysrKysrKysrKystLS0tLS0KIC4uLi8wODIuZ3VhcmRfYWdhaW5zdF9iYWRfcHJvdG9jb2wvdGVz
dC5zaCAgICB8ICA1ICstCiAyIGZpbGVzIGNoYW5nZWQsIDQ3IGluc2VydGlvbnMoKyksIDE5IGRl
bGV0aW9ucygtKQoKZGlmZiAtLWdpdCBhL3NyYy9wcm90b2NvbC9wb29sX3Byb3RvX21vZHVsZXMu
YyBiL3NyYy9wcm90b2NvbC9wb29sX3Byb3RvX21vZHVsZXMuYwppbmRleCAxMWJlZmU5NzkuLmZk
YjQxYmJmMCAxMDA2NDQKLS0tIGEvc3JjL3Byb3RvY29sL3Bvb2xfcHJvdG9fbW9kdWxlcy5jCisr
KyBiL3NyYy9wcm90b2NvbC9wb29sX3Byb3RvX21vZHVsZXMuYwpAQCAtMjIxLDIzICsyMjEsNiBA
QCBTaW1wbGVRdWVyeShQT09MX0NPTk5FQ1RJT04gKmZyb250ZW5kLAogCS8qIHNhdmUgbGFzdCBx
dWVyeSBzdHJpbmcgZm9yIGxvZ2dpbmcgcHVycG9zZSAqLwogCXN0cmxjcHkocXVlcnlfc3RyaW5n
X2J1ZmZlciwgY29udGVudHMsIHNpemVvZihxdWVyeV9zdHJpbmdfYnVmZmVyKSk7CiAKLQkvKgot
CSAqIENoZWNrIGlmIGV4dGVuZGVkIHF1ZXJ5IHByb3RvY29sIG1lc3NhZ2UgZW5kZWQuICBJZiBu
b3QsIHJlamVjdCB0aGUKLQkgKiBxdWVyeSBhbmQgcmFpc2UgYW4gZXJyb3IgdG8gdGVybWluYXRl
IHRoZSBzZXNzaW9uIHRvIGF2b2lkIGhhbmdpbmcgdXAuCi0JICogSG93ZXZlciBpZiB3ZSBhcmUg
cHJvY2Vzc2luZyBhIHJlc2V0IHF1ZXJ5IChmcm9udGVuZCA9PSBOVUxMKSwgd2Ugc2tpcAotCSAq
IHRoZSBjaGVjayBhcyB3ZSBkb24ndCB3YW50IHRvIHJhaXNlIGEgZXJyb3IuCi0JICovCi0JaWYg
KFNMX01PREUpCi0JewotCQlpZiAoZnJvbnRlbmQgIT0gTlVMTCAmJgotCQkJKHBvb2xfaXNfZG9p
bmdfZXh0ZW5kZWRfcXVlcnlfbWVzc2FnZSgpIHx8Ci0JCQkgcG9vbF9wZW5kaW5nX21lc3NhZ2Vf
aGVhZF9tZXNzYWdlKCkpKQotCi0JCQllcmVwb3J0KEZBVEFMLAotCQkJCQkoZXJybXNnKCJzaW1w
bGUgcXVlcnkgXCIlc1wiIGFycml2ZWQgYmVmb3JlIGVuZGluZyBhbiBleHRlbmRlZCBxdWVyeSBt
ZXNzYWdlIiwKLQkJCQkJCQlxdWVyeV9zdHJpbmdfYnVmZmVyKSkpOwotCX0KLQogCS8qIHNob3cg
cHMgc3RhdHVzICovCiAJcXVlcnlfcHNfc3RhdHVzKGNvbnRlbnRzLCBiYWNrZW5kKTsKIApAQCAt
MjkzMCw2ICsyOTEzLDQ0IEBAIFByb2Nlc3NGcm9udGVuZFJlc3BvbnNlKFBPT0xfQ09OTkVDVElP
TiAqZnJvbnRlbmQsCiAJCQkJZXJlcG9ydChMT0csCiAJCQkJCQkoZXJybXNnKCJRdWVyeSBtZXNz
YWdlIGZyb20gZnJvbnRlbmQuIiksCiAJCQkJCQkgZXJyZGV0YWlsKCJxdWVyeTogXCIlc1wiIiwg
Y29udGVudHMpKSk7CisKKwkJCS8qCisJCQkgKiBDaGVjayBpZiBleHRlbmRlZCBxdWVyeSBwcm90
b2NvbCBtZXNzYWdlIGVuZGVkLiBJZiBub3QsIHByb2Nlc3MKKwkJCSAqIGFueSBwZW5kaW5nIHJl
c3BvbnNlIGZyb20gYmFja2VuZCB1c2luZworCQkJICogUHJvY2Vzc0JhY2tlbmRSZXNwb25zZSgp
LiBIb3dldmVyIGlmIHdlIGFyZSBwcm9jZXNzaW5nIGEgcmVzZXQKKwkJCSAqIHF1ZXJ5IChmcm9u
dGVuZCA9PSBOVUxMKSwgd2Ugc2tpcCB0aGUgY2hlY2sgYXMgd2UgZG9uJ3QgbmVlZCB0bworCQkJ
ICogY2FyZSBhYm91dCBhbnkgcGVuZGluZyByZXNwb25zZSBmcm9tIGJhY2tlbmQuCisJCQkgKi8K
KwkJCWlmIChTTF9NT0RFKQorCQkJeworCQkJCWludAkJCXN0YXRlOworCQkJCXNob3J0CQludW1f
ZmllbGRzOworCisJCQkJaWYgKGZyb250ZW5kICE9IE5VTEwgJiYKKwkJCQkJKHBvb2xfaXNfZG9p
bmdfZXh0ZW5kZWRfcXVlcnlfbWVzc2FnZSgpIHx8CisJCQkJCSBwb29sX3BlbmRpbmdfbWVzc2Fn
ZV9leGlzdHMoKSkpCisJCQkJeworCQkJCQllcmVwb3J0KERFQlVHMSwKKwkJCQkJCQkoZXJybXNn
KCJzaW1wbGUgcXVlcnkgXCIlc1wiIGFycml2ZWQgYmVmb3JlIGVuZGluZyBhbiBleHRlbmRlZCBx
dWVyeSBtZXNzYWdlIiwKKwkJCQkJCQkJCWNvbnRlbnRzKSkpOworCisJCQkJCS8qIGlmIHBlbmRp
bmcgbWVzc2FnZSBleGlzdHMsIHByb2Nlc3MgaXQgKi8KKwkJCQkJd2hpbGUgKHBvb2xfcGVuZGlu
Z19tZXNzYWdlX2V4aXN0cygpKQorCQkJCQl7CisJCQkJCQkvKgorCQkJCQkJICogcmVhZF9raW5k
X2Zyb21fYmFja2VuZCByZXF1aXJlcyB0aGF0IHF1ZXJ5IGlzCisJCQkJCQkgKiBpbnByb2dyZXNz
IGFuZCBkb2luZyBleHRlbmRlZCBxdWVyeSBzdGF0ZSBiZWNhdXNlCisJCQkJCQkgKiBpdCBuZWVk
cyB0byByZWZlciB0byBwcm9wZXIgcXVlcnkgY29udGV4dCBpbgorCQkJCQkJICogc2Vzc2lvbiBj
b250ZXh0LgorCQkJCQkJICovCisJCQkJCQlwb29sX3NldF9xdWVyeV9pbl9wcm9ncmVzcygpOwor
CQkJCQkJcG9vbF9zZXRfZG9pbmdfZXh0ZW5kZWRfcXVlcnlfbWVzc2FnZSgpOworCQkJCQkJLyog
cHJvY2VzcyBwZW5kaW5nIHJlc3BvbnNlcyBmcm9tIGJhY2tlbmQgKi8KKwkJCQkJCVByb2Nlc3NC
YWNrZW5kUmVzcG9uc2UoZnJvbnRlbmQsIGJhY2tlbmQsICZzdGF0ZSwgJm51bV9maWVsZHMpOwor
CQkJCQl9CisJCQkJfQorCQkJCXBvb2xfdW5zZXRfZG9pbmdfZXh0ZW5kZWRfcXVlcnlfbWVzc2Fn
ZSgpOworCQkJfQogCQkJc3RhdHVzID0gU2ltcGxlUXVlcnkoZnJvbnRlbmQsIGJhY2tlbmQsIGxl
biwgY29udGVudHMpOwogCQkJYnJlYWs7CiAKQEAgLTMwODAsNiArMzEwMSwxMiBAQCBQcm9jZXNz
RnJvbnRlbmRSZXNwb25zZShQT09MX0NPTk5FQ1RJT04gKmZyb250ZW5kLAogCXJldHVybiBzdGF0
dXM7CiB9CiAKKy8qCisgKiBSZWFkIG9uZSBiYWNrZW5kIHJlc3BvbnNlIGFuZCBwcm9jZXNzIGl0
LgorICoKKyAqIHN0YXRlOiB1c2VkIGZvciBwcm9jZXNzaW5nIHJlc2V0IHF1ZXJ5CisgKiBudW1f
ZmllbGRzOiB1c2VkIGluIFYyIHByb3RvY29sCisgKi8KIFBPT0xfU1RBVFVTCiBQcm9jZXNzQmFj
a2VuZFJlc3BvbnNlKFBPT0xfQ09OTkVDVElPTiAqZnJvbnRlbmQsCiAJCQkJCSAgIFBPT0xfQ09O
TkVDVElPTl9QT09MICpiYWNrZW5kLApkaWZmIC0tZ2l0IGEvc3JjL3Rlc3QvcmVncmVzc2lvbi90
ZXN0cy8wODIuZ3VhcmRfYWdhaW5zdF9iYWRfcHJvdG9jb2wvdGVzdC5zaCBiL3NyYy90ZXN0L3Jl
Z3Jlc3Npb24vdGVzdHMvMDgyLmd1YXJkX2FnYWluc3RfYmFkX3Byb3RvY29sL3Rlc3Quc2gKaW5k
ZXggZjFkMTZlNmYzLi5jNGY2ZTkwYjQgMTAwNzU1Ci0tLSBhL3NyYy90ZXN0L3JlZ3Jlc3Npb24v
dGVzdHMvMDgyLmd1YXJkX2FnYWluc3RfYmFkX3Byb3RvY29sL3Rlc3Quc2gKKysrIGIvc3JjL3Rl
c3QvcmVncmVzc2lvbi90ZXN0cy8wODIuZ3VhcmRfYWdhaW5zdF9iYWRfcHJvdG9jb2wvdGVzdC5z
aApAQCAtMjIsNiArMjIsNyBAQCBlY2hvIC1uICJjcmVhdGluZyB0ZXN0IGVudmlyb25tZW50Li4u
IgogJFBHUE9PTF9TRVRVUCB8fCBleGl0IDEKIGVjaG8gImRvbmUuIgogZWNobyAiYmFja2VuZF93
ZWlnaHQxPTAiID4+IGV0Yy9wZ3Bvb2wuY29uZgorZWNobyAiY2xpZW50X21pbl9tZXNzYWdlcz1k
ZWJ1ZzEiID4+IGV0Yy9wZ3Bvb2wuY29uZgogc291cmNlIC4vYmFzaHJjLnBvcnRzCiAuL3N0YXJ0
YWxsCiB3YWl0X2Zvcl9wZ3Bvb2xfc3RhcnR1cApAQCAtMjksOCArMzAsOCBAQCB3YWl0X2Zvcl9w
Z3Bvb2xfc3RhcnR1cAogIyB0ZXN0MToKICMgV2FpdCBmb3IgMSBzZWNvbmRzIGJlZm9yZSBwZ3By
b3RvIGVuZGVkLgogIyBVc3VhbGx5IDEgc2Vjb25kcyBzaG91bGQgYmUgZW5vdWdoIHRvIGZpbmlz
aCBwZ3Byb3RvLgotIyBJZiB0ZXN0IHN1Y2VlZGVkLCBwZ3Bvb2wgZW1pdHMgYW4gZXJyb3IgbWVz
c2FnZToKLSMgIkZBVEFMOiAgc2ltcGxlIHF1ZXJ5ICJTQVZFUE9JTlQgUEdKREJDX0FVVE9TQVZF
IiBhcnJpdmVkIGJlZm9yZSBlbmRpbmcgYW4gZXh0ZW5kZWQgcXVlcnkgbWVzc2FnZSIKKyMgSWYg
dGhlIHRlc3Qgc3VjZWVkcywgcGdwb29sIGVtaXRzIGEgZGVidWcgbWVzc2FnZToKKyMgInNpbXBs
ZSBxdWVyeSAiU0FWRVBPSU5UIFBHSkRCQ19BVVRPU0FWRSIgYXJyaXZlZCBiZWZvcmUgZW5kaW5n
IGFuIGV4dGVuZGVkIHF1ZXJ5IG1lc3NhZ2UiCiAjIGdyZXAgY29tbWFuZCBiZWxvdyBzaG91bGQg
Y2F0Y2ggdGhlIG1lc3NhZ2UuCiB0aW1lb3V0IDEgJFBHUFJPVE8gLWQgJFBHREFUQUJBU0UgLXAg
JFBHUE9PTF9QT1JUIC1mIC4uL3BncHJvdG8uZGF0YSB8JiBncmVwICdzaW1wbGUgcXVlcnkgIlNB
VkVQT0lOVCBQR0pEQkNfQVVUT1NBVkUiIGFycml2ZWQgJwogCi0tIAoyLjI1LjEKCg==

----Next_Part(Thu_Jul_24_10_36_07_2025_642)----