Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uep4w-00FapN-BG for pgpool-general@arkaria.postgresql.org; Thu, 24 Jul 2025 06:05:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uep4u-006Q18-02 for pgpool-general@arkaria.postgresql.org; Thu, 24 Jul 2025 06:05:16 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uep4t-006Q10-Ga for pgpool-general@lists.postgresql.org; Thu, 24 Jul 2025 06:05:16 +0000 Received: from mail-vk1-xa2d.google.com ([2607:f8b0:4864:20::a2d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uep4q-000VLI-2l for pgpool-general@lists.postgresql.org; Thu, 24 Jul 2025 06:05:14 +0000 Received: by mail-vk1-xa2d.google.com with SMTP id 71dfb90a1353d-5314b486207so206633e0c.2 for ; Wed, 23 Jul 2025 23:05:12 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1753337111; x=1753941911; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=VT2DBs7M2lZL+5qLEyGhHwvr0smZ8rAqipRb+x7/jm8=; b=N9Zgi+sf5AfTcj0p7OVPY9hMHZQSa/zX8j2mwed11p9QYQCaL8s3H9EiPJwyTS4Vvo JZ8V1uDfi/XoHMJ85hv6VQLDboDxLxwnhD+8gwhv407uQCNJJeNVvscM1hIXD4alc+KM 66OhWm2C9V+3OW8NMsbeYvDGw4fHVELcn7XGRLE6h1uDn5cqUT9+xRSpEugoNlxSD9hy oEPZCiC9nZvTtx7BMrEZXwyROUFDZ2DucCF6Bz3OGH97aCcGYnBvaP+Z+6DMofqDq4te i62iposGQZXHEEicNbKBRZjTpSGiBcqMFzoyvTODBXZAocHuNDuFSHjAE5JexYhBLGiI y98Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1753337111; x=1753941911; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=VT2DBs7M2lZL+5qLEyGhHwvr0smZ8rAqipRb+x7/jm8=; b=C8K7XnWvK5ylpCAeCCsAsb8FVbP3fF4iza9sd6zoKXvVwwO+yM51g7m35UiSZQDJc1 U6R1LAk8tqCV3DqjcDPU3zrBY6rYCAP37pUuya4UmeseBRPr/pPADzvZTxxPPqafJU81 Q7DE5GbLmU+u51433eKc7cF0RLZTqNWEkLHqF+xueOq1fafW42cw1SjzmkEn0rJFAf1L 7VDyb5avKBuxHpg347GXQRN8wvgZ2D7tnYrDYNQYHXuC7QBrYmLe8U/S1tkzRZJtI+Mu /m66Ir6+4Lbz1TEVR7Ru/HLOwNk8XqeFIEi1dUK10cyTr8ZHmWIOqvjhmCG/PL8MUbX7 mBgA== X-Gm-Message-State: AOJu0YwiItPrGTNgcd5tKdMWaDa4+XL4FpC0EcZR7AWWeGMAQNSA4di9 0+kQI+E3WBk1aJmEAHa26nRl0MaX0YVJkukQn3TJ0kH5Wd4yC0E4tpq3PoGeGwT9+IR2BcD/K5e KJUCuzJnOPwOav1gwcvJUEsJNGu8JGg0= X-Gm-Gg: ASbGncsqWnFwBoFAnfHUFL9ME5osKNRvk7rTwWutGhvs8BxZvOXZrLvLCc+CX5oALu3 WxInG8/YkT4QTACVDoKNOkYxmJEvm8gmXInVc7LUcU4Og/EzvewD9txr62Q6QA289vO5FQc0XW5 ZjWNPxaVpb9vTry0KEriSJXdbVoLWPVrZcMLHTq5FT6ILY5QiwEZnUHzW30XSDMhzGO4ULPXoNk wb+jJfz/m6ttEQN+g== X-Google-Smtp-Source: AGHT+IGx3tv35R8TqnmQTCfJZn2rxVLp/hiwg27Psco5vEaxheJXov++lyX1jIK6wmsU5HmCpyE8v31LhIwmPLS+2rQ= X-Received: by 2002:a05:6122:8c13:b0:526:19e6:fa34 with SMTP id 71dfb90a1353d-537af435f87mr2645031e0c.1.1753337111085; Wed, 23 Jul 2025 23:05:11 -0700 (PDT) MIME-Version: 1.0 References: <20250721.131214.1600596684258021437.ishii@postgresql.org> <20250723.170320.645068218702820010.ishii@postgresql.org> <20250724.103607.89592995648988686.ishii@postgresql.org> In-Reply-To: <20250724.103607.89592995648988686.ishii@postgresql.org> From: Emond Papegaaij Date: Thu, 24 Jul 2025 08:04:58 +0200 X-Gm-Features: Ac12FXwvW_FkurMsvkus97Zxje-SlYBqqSEqx4_ZGKt_bYH2XQZsd0eSLQ2-yLQ Message-ID: Subject: Re: FATAL: simple query "BEGIN" arrived before ending an extended query message To: Tatsuo Ishii Cc: pgpool-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000ad8a5e063aa69b42" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ad8a5e063aa69b42 Content-Type: text/plain; charset="UTF-8" Hi, There's been some recent development on the side of pgjdbc as well: https://github.com/pgjdbc/pgjdbc/pull/3728 We are currently testing that fix. With that patch, pgjdbc will always send a sync first when in extended mode before sending a simple query. Do you think it's still a good idea to support mixing simple and extended mode in pgpool? It's difficult for me to assess the risk of your proposed patch. Maybe it's better to just not allow it if that makes it easier to implement? Ps. Sorry for the bad quoting. I'm writing this on my phone, as I'm still on vacation. Op do 24 jul 2025, 03:36 schreef Tatsuo Ishii : > >> 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 > --000000000000ad8a5e063aa69b42 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi,

There's been some recent development on the side of pgjdbc as well:= =C2=A0
https://gi= thub.com/pgjdbc/pgjdbc/pull/3728

We are currently testing that fix. With that patch, pgjdbc wil= l always send a sync first when in extended mode before sending a simple qu= ery. Do you think it's still a good idea to support mixing simple and e= xtended mode in pgpool? It's difficult for me to assess the risk of you= r proposed patch. Maybe it's better to just not allow it if that makes = it easier to implement?

= Ps. Sorry for the bad quoting. I'm writing this on my phone, as I'm= still on vacation.=C2=A0


Op do 24 jul 2025, 03:36 schreef Tatsuo Ishi= i <ishii@postgresql.org>:=
>> 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 wh= at the plans are on
>>>> > this from the side of PostgreSQL and PgJDBC, but I ju= st wanted to raise
>>>> > some awareness on this issue here. IMHO PostgreSQL sh= ould 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 sai= d about this:
>>>> ht= tps://www.postgresql.org/message-id/2069511.1706571615@sss.pgh.pa.us >>>>
>>>> > I think it's poor practice, at best.=C2=A0 You sh= ould 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 expe= cted 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:
>>>
>>>=C2=A0 Jelte Fennema-Nio in
>>> https://www.postgresql.org/message-id/CAGEC= zQQ1hs2DU9pmQq18Y%3DqK4nZqhXDVg-sGEa5K01Lj4XMmxw%40mail.gmail.com
>>>> I totally agree that it makes sense to throw an error in t= his case.
>>>> Libpq actually throws an error client side when a caller a= ttempts to
>>>> do this, but this is something that should be checked serv= er side,
>>>> given that the protocol docs specify this:
>>>> docs> At completion of each series of extended-query me= ssages, the
>>>> frontend should issue a Sync message.
>>>
>>>
>>>> >=C2=A0 The current situation
>>>> > is no good as we now simply cannot upgrade PgJDBC any= more (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 protoc= l without
>>>> sync) even without autosave being set when they update PgJ= DBC 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 protoco= l 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 applicat= ion at all,
>>> others had no trouble whatsoever and some users got an error e= very 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 c= ertainly not
>>> recommend using PgJDBC 42.7.6 and up when using pgpool.
>>
>> I created a patch for pgpool to allow a simple query without endin= g 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 an= y
>> replies from backend such as parse complete, bind complete or comm= and
>> complete and so.=C2=A0 After this, SimpleQuery() can process simpl= e 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
--000000000000ad8a5e063aa69b42--