Re: FATAL: simple query "BEGIN" arrived before ending an extended query message

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