<div dir="auto">Yes I meant remove the S message. </div><div dir="auto">And you this is the expected behaviour the fronted should send the Sync message.</div><div dir="auto"><br></div><div dir="auto">My pgproto looks like this </div><div dir="auto"><br></div><div dir="auto"><span style="border-color:rgb(0,0,0) rgb(0,0,0) rgb(0,0,0) rgb(204,204,204);color:rgb(0,0,0)">‘P' "" "SELECT 1" 0</span><br style="font-family:-apple-system,HelveticaNeue"><span style="border-color:rgb(0,0,0) rgb(0,0,0) rgb(0,0,0) rgb(204,204,204);color:rgb(0,0,0)">'B' "" "" 0 0 0</span><br style="font-family:-apple-system,HelveticaNeue"><span style="border-color:rgb(0,0,0) rgb(0,0,0) rgb(0,0,0) rgb(204,204,204);color:rgb(0,0,0)">'D' 'P' ""</span><br style="font-family:-apple-system,HelveticaNeue"><span style="border-color:rgb(0,0,0) rgb(0,0,0) rgb(0,0,0) rgb(204,204,204);color:rgb(0,0,0)">'E' "" 0</span><br style="font-family:-apple-system,HelveticaNeue"><span style="border-color:rgb(0,0,0) rgb(0,0,0) rgb(0,0,0) rgb(204,204,204);color:rgb(0,0,0)">'Y'</span><br style="font-family:-apple-system,HelveticaNeue"><span style="border-color:rgb(0,0,0) rgb(0,0,0) rgb(0,0,0) rgb(204,204,204);color:rgb(0,0,0)">'X'</span><br></div><div dir="auto"><span style="border-color:rgb(0,0,0) rgb(0,0,0) rgb(0,0,0) rgb(204,204,204);color:rgb(0,0,0)"><br></span></div><div dir="auto"><span style="border-color:rgb(0,0,0) rgb(0,0,0) rgb(0,0,0) rgb(204,204,204);color:rgb(0,0,0)">I Remove the ‘S’ because I want to prove that the state change from active to idle only if we send ‘S’ message. </span></div><div dir="auto"><span style="border-color:rgb(0,0,0) rgb(0,0,0) rgb(0,0,0) rgb(204,204,204);color:rgb(0,0,0)">When I remove like I show above the state stays active.</span></div><div dir="auto"><span style="border-color:rgb(0,0,0) rgb(0,0,0) rgb(0,0,0) rgb(204,204,204);color:rgb(0,0,0)"><br></span></div><div dir="auto"><span style="border-color:rgb(0,0,0) rgb(0,0,0) rgb(0,0,0) rgb(204,204,204);color:rgb(0,0,0)">And that’s what I am saying when I use extended query and I get miss from cache execute forward to BE and then, </span></div><div dir="auto"><span style="border-color:rgb(0,0,0) rgb(0,0,0) rgb(0,0,0) rgb(204,204,204);color:rgb(0,0,0)">Pgpool immediately read FE next message which is ‘S’ and forward to BE. </span></div><div dir="auto"><span style="border-color:rgb(0,0,0) rgb(0,0,0) rgb(0,0,0) rgb(204,204,204);color:rgb(0,0,0)"><br></span></div><div dir="auto"><span style="border-color:rgb(0,0,0) rgb(0,0,0) rgb(0,0,0) rgb(204,204,204);color:rgb(0,0,0)">But when I get hit from cache pgpool not forward execute to BE and skip reading the next frontend message which is ‘S’ and instead start read backend response.</span></div><div dir="auto"><span style="border-color:rgb(0,0,0) rgb(0,0,0) rgb(0,0,0) rgb(204,204,204);color:rgb(0,0,0)"><br></span></div><div dir="auto"><span style="border-color:rgb(0,0,0) rgb(0,0,0) rgb(0,0,0) rgb(204,204,204);color:rgb(0,0,0)">This case make the state stay on active until pgpool finish to read everything from BE.</span></div><div dir="auto"><span style="border-color:rgb(0,0,0) rgb(0,0,0) rgb(0,0,0) rgb(204,204,204);color:rgb(0,0,0)"><br></span></div><div dir="auto"><span style="border-color:rgb(0,0,0) rgb(0,0,0) rgb(0,0,0) rgb(204,204,204);color:rgb(0,0,0)">Thanks,</span></div><div dir="auto"><span style="border-color:rgb(0,0,0) rgb(0,0,0) rgb(0,0,0) rgb(204,204,204);color:rgb(0,0,0)"><br></span></div><div dir="auto"><span style="border-color:rgb(0,0,0) rgb(0,0,0) rgb(0,0,0) rgb(204,204,204);color:rgb(0,0,0)">Avi</span></div><div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sun, 18 Sep 2022 at 12:35 Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;padding-left:1ex;border-left-color:rgb(204,204,204)">> Ok,<br>
> <br>
> In your example you explicitly add the ‘S’ message in your<br>
> extended-query-cache.data file.<br>
> So in that case off course the state changed from active to idle.<br>
> <br>
> I use the pgproto also in my lab and I found that if I moved the ‘S’<br>
> message the connection stay on active.<br>
<br>
You mean "remove"? Anyway, that's an expected behavior. Frontend needs<br>
to explicitely issue Sync message.<br>
> <br>
> That’s what I mentioned in my latest mails.<br>
> That in case pgpool read the extended query from cache (means that pgpool<br>
> not forward execute message)<br>
> I can see when debug that instead of read the next message from frontend<br>
> which is ‘S’ message and forward to BE.<br>
> What happened is that we read a BE response which will be ‘1’ which is<br>
> ParseComplete.<br>
> <br>
> This scenario not happened when the query not in cache, here after execute<br>
> send from FE to BE<br>
> pgpool read the next message from frontend which is ‘S’ message and forward<br>
> to BE what change the state from active to idle.<br>
> <br>
> Which that make sense because when we send ‘S’ to BE we actually tell him<br>
> that I got everything I need and I am ready for query.<br>
> <br>
> Let me know if more information needed.<br>
<br>
Can you share the exact pgproto data file for query cache enabled<br>
case? I am asking because I use the same extended-query-cache.data<br>
file and got exactly the same result as to when query cache is<br>
disabled.<br>
<br>
'P' "" "SELECT 1" 0<br>
'B' "" "" 0 0 0<br>
'D' 'P' ""<br>
'E' "" 0<br>
'S'<br>
'Y'<br>
'X'<br>
<br>
pgproto -d test -p 11000 -f ../extended-query-cache.data<br>
FE=> Parse(stmt="", query="SELECT 1")<br>
FE=> Bind(stmt="", portal="")<br>
FE=> Describe(portal="")<br>
FE=> Execute(portal="")<br>
FE=> Sync<br>
<= BE ParseComplete<br>
<= BE BindComplete<br>
<= BE RowDescription<br>
<= BE DataRow<br>
<= BE CommandComplete(SELECT 1)<br>
<= BE ReadyForQuery(I)<br>
FE=> Terminate<br>
<br>
(query cache is enabled and the data is fetchd from query cache)<br>
<br>
> Thanks,<br>
> <br>
> Avi.<br>
> <br>
> On Sat, 17 Sep 2022 at 12:34 Avi Raboah <<a href="mailto:avi.raboah@gmail.com" target="_blank">avi.raboah@gmail.com</a>> wrote:<br>
> <br>
>> Hi,<br>
>><br>
>> I’ll try to do a demo later today. And i’ll send the flow as I see it when<br>
>> debugging. For sure when sync message forward to backend the state change<br>
>> from active to idle.<br>
>><br>
>> And I agree in case sync not forwarded readyForQuery change the state from<br>
>> active to idle.<br>
>><br>
>> Thanks,<br>
>><br>
>> Avi.<br>
>><br>
>><br>
>><br>
>> On Sat, 17 Sep 2022 at 12:26 Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> wrote:<br>
>><br>
>>> > Hi,<br>
>>> ><br>
>>> > So I check that, after pgpool read the result from cache I found the<br>
>>> next<br>
>>> > step to be was message type ‘1’ parse complete from backend and not sync<br>
>>> > message from frontend as I expected.<br>
>>> ><br>
>>> > Notice that after sync message send to the backend the connection<br>
>>> becomes<br>
>>> > idle. In case the sync message didn’t read from frontend to backend,<br>
>>> then<br>
>>> > the connection becomes idle only after command complete.<br>
>>><br>
>>> Probably you misunderstand here. Command complete message never makes<br>
>>> PostgreSQL in idle state. PostgreSQL becomes idle (including idle in<br>
>>> transaction) state only after issuing Ready for Query message.<br>
>>><br>
>>> > As I mentioned earlier Sync message not send from frontend to backend in<br>
>>> > case pgpool read the result from cache.<br>
>>> ><br>
>>> > Could you check that please ? Because what I saw it’s not what you<br>
>>> > described.<br>
>>><br>
>>> That's not what I saw here. Here's log emitted by pgproto (testing tool<br>
>>> provided by pgpool distribution).<br>
>>><br>
>>> t-ishii$ cat ../extended-query-cache.data<br>
>>> 'P' "" "SELECT 1" 0<br>
>>> 'B' "" "" 0 0 0<br>
>>> 'D' 'P' ""<br>
>>> 'E' "" 0<br>
>>> 'S'<br>
>>> 'Y'<br>
>>> 'X'<br>
>>><br>
>>> t-ishii$ pgproto -d test -p 11000 -f ../extended-query-cache.data<br>
>>> FE=> Parse(stmt="", query="SELECT 1")<br>
>>> FE=> Bind(stmt="", portal="")<br>
>>> FE=> Describe(portal="")<br>
>>> FE=> Execute(portal="")<br>
>>> FE=> Sync<br>
>>> <= BE ParseComplete<br>
>>> <= BE BindComplete<br>
>>> <= BE RowDescription<br>
>>> <= BE DataRow<br>
>>> <= BE CommandComplete(SELECT 1)<br>
>>> <= BE ReadyForQuery(I)<br>
>>> FE=> Terminate<br>
>>><br>
>>> "FE=> " means that a message sent from frontend to pgpool.<br>
>>> "<= BE" means that a message sent from pgpool to frontend.<br>
>>><br>
>>> Note that this output is exactly same as the case when query cache is<br>
>>> disabled or pgproto directly connects to PostgreSQL.<br>
>>><br>
>>> Also here's output from pg_stat_activity.<br>
>>><br>
>>> test=# select * from pg_stat_activity where application_name = 'pgproto';<br>
>>> -[ RECORD 1 ]----+------------------------------<br>
>>> datid | 16392<br>
>>> datname | test<br>
>>> pid | 65254<br>
>>> leader_pid |<br>
>>> usesysid | 10<br>
>>> usename | t-ishii<br>
>>> application_name | pgproto<br>
>>> client_addr | 127.0.0.1<br>
>>> client_hostname |<br>
>>> client_port | 56758<br>
>>> backend_start | 2022-09-17 18:16:30.854156+09<br>
>>> xact_start |<br>
>>> query_start |<br>
>>> state_change | 2022-09-17 18:16:30.856935+09<br>
>>> wait_event_type | Client<br>
>>> wait_event | ClientRead<br>
>>> state | idle<br>
>>> backend_xid |<br>
>>> backend_xmin |<br>
>>> query_id |<br>
>>> query |<br>
>>> backend_type | client backend<br>
>>><br>
>>> > Thanks,<br>
>>> ><br>
>>> > Avi.<br>
>>> ><br>
>>> ><br>
>>> > On Sat, 17 Sep 2022 at 4:05 Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> wrote:<br>
>>> ><br>
>>> >> > The issue with the aws performance insights<br>
>>> >> > Is a result of the way pgpool read frontend packets when cache<br>
>>> enabled<br>
>>> >> and<br>
>>> >> > disabled.<br>
>>> >> > If we querying pgpool in extended query mode and cache disabled I<br>
>>> can see<br>
>>> >> > the following -<br>
>>> >> > 1. Read parse from frontend and send to backend.<br>
>>> >> > 2. Read bind message and send to the backend<br>
>>> >> > 3. Read describe message and send to the backend<br>
>>> >> > 4. Read execute message and send to the backend —><br>
>>> >> ><br>
>>> >> > Until here the connection is in active state under pg_stats_activity<br>
>>> >> ><br>
>>> >> > 5. Read sync message and send to backend. —><br>
>>> >> > Now the connection moved to idle state.<br>
>>> >> ><br>
>>> >> > When cache enabled and the query was found in cache :<br>
>>> >> ><br>
>>> >> > 1. Read parse from frontend and send to backend.<br>
>>> >> > 2. Read bind message and send to the backend<br>
>>> >> > 3. Read describe message and send to the backend<br>
>>> >> > 4. Read execute message and send to the backend —><br>
>>> >> ><br>
>>> >> > Now pgpool found the query in cache so execute message not send to<br>
>>> the<br>
>>> >> > backend.<br>
>>> >> > In that case instead of read sync from frontend and update the<br>
>>> backend in<br>
>>> >> > order to move the connection state from active to idle . Pgpool start<br>
>>> >> read<br>
>>> >> > packet from backend which will be ‘1’, ‘2’ and so on untill<br>
>>> >> > commandComplete. This behaviour cause the connection to be in active<br>
>>> >> state<br>
>>> >> > until command complete.<br>
>>> >><br>
>>> >> Really? pgpool does not try to read command complete from backend in<br>
>>> >> this case because the command complete message is part of the cache<br>
>>> >> data.<br>
>>> >><br>
>>> >> After 4 (actually pgpool does not send the execute message to backend<br>
>>> >> because the cache is there)<br>
>>> >><br>
>>> >> 1) pgpool read Sync message from frontend and forward to backend.<br>
>>> >><br>
>>> >> 2) pgpool read from backend and returns '1' (command complete,<br>
>>> >> response to parse message), '2' (bind complete, response to bind<br>
>>> >> message), Row description (response to describe message) to<br>
>>> >> frontend.<br>
>>> >><br>
>>> >> 3) pgpool returns (without reading from backend) Command Complete and<br>
>>> row<br>
>>> >> data (if any).<br>
>>> >><br>
>>> >> 4) pgpool read ready for query message from backend and returns it to<br>
>>> >> frontend.<br>
>>> >> at this point, pg_stat_activity should show 'idle' state.<br>
>>> >><br>
>>> >> > I am trying to understand if it’s a bug or it should be like that.<br>
>>> >> ><br>
>>> >> > Thanks,<br>
>>> >> ><br>
>>> >> > Avi.<br>
>>> >> ><br>
>>> >> > On Thu, 15 Sep 2022 at 7:34 Avi Raboah <<a href="mailto:avi.raboah@gmail.com" target="_blank">avi.raboah@gmail.com</a>> wrote:<br>
>>> >> ><br>
>>> >> >> Thank you for the clarification.<br>
>>> >> >><br>
>>> >> >> On Thu, 15 Sep 2022 at 3:47 Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>><br>
>>> wrote:<br>
>>> >> >><br>
>>> >> >>> > Hi,<br>
>>> >> >>> > I am not sure I understood what you mean when you mentioned<br>
>>> “close<br>
>>> >> >>> message”<br>
>>> >> >>> ><br>
>>> >> >>> > But i’ll try to ask you in a different way.<br>
>>> >> >>> ><br>
>>> >> >>> > Let look on the following example:<br>
>>> >> >>> ><br>
>>> >> >>> > 1. Select * from users where id = $1; using extended query. —><br>
>>> miss<br>
>>> >> >>> ><br>
>>> >> >>> > 2. Now in case i’ll run the same query i’ll get it back from<br>
>>> cache.<br>
>>> >> >>> ><br>
>>> >> >>> > So I am understanding why the parse message should be pass to the<br>
>>> >> >>> backend<br>
>>> >> >>> > and not read the result from cache due to that Parse message<br>
>>> didn’t<br>
>>> >> >>> > contains the query params. That’s make sense.<br>
>>> >> >>> ><br>
>>> >> >>> > But in Bind message the packet already contains the query<br>
>>> params. So<br>
>>> >> why<br>
>>> >> >>> > you can’t read the result from cache here and we need to wait to<br>
>>> the<br>
>>> >> >>> > execute message?<br>
>>> >> >>><br>
>>> >> >>> Bind message does not return the "result". It just returns it<br>
>>> succeeded<br>
>>> >> >>> (bind complete message) or failed (error response). The actual<br>
>>> result<br>
>>> >> >>> (which is called "portal") is only in PostgreSQL's memory. So<br>
>>> "caching<br>
>>> >> >>> result of bind message" is almost meaningless.<br>
>>> >> >>><br>
>>> >> >>> > One more question is why we need to send the bind message anyway<br>
>>> to<br>
>>> >> the<br>
>>> >> >>> > backend in case we have already the result of the execute<br>
>>> message in<br>
>>> >> >>> cache?<br>
>>> >> >>><br>
>>> >> >>> Think about this scenario:<br>
>>> >> >>><br>
>>> >> >>> 1. pgpool receives a bind message but finds that the corresponding<br>
>>> >> >>> query cache exists. So pgpool does not send the bind message to<br>
>>> >> >>> backend.<br>
>>> >> >>><br>
>>> >> >>> 2. In other session the table used in the query has been modified<br>
>>> and<br>
>>> >> >>> the query is gone.<br>
>>> >> >>><br>
>>> >> >>> 3. pgpool receives an execute message and tries to extract the<br>
>>> query<br>
>>> >> >>> cache, but it has already gone. However pgpool cannot send the<br>
>>> execute<br>
>>> >> >>> message to backend because the result of bind message (portal) does<br>
>>> >> >>> not exist in the backend.<br>
>>> >> >>><br>
>>> >> >>> > I am asking the above questions because I am looking in the rds<br>
>>> >> >>> performance<br>
>>> >> >>> > insights.<br>
>>> >> >>> > And I can see that in case I am sending a lot of queries<br>
>>> concurrently<br>
>>> >> >>> when<br>
>>> >> >>> > I have cache enabled I can see a very big wait because of a lot<br>
>>> of<br>
>>> >> >>> > connections in idle ClientRead wait event.<br>
>>> >> >>><br>
>>> >> >>> I am not familiar with performance insights and cannot comment on<br>
>>> it.<br>
>>> >> >>><br>
>>> >> >>> > And I am asking myself why the db needs to know about queries we<br>
>>> >> already<br>
>>> >> >>> > have in cache?<br>
>>> >> >>> ><br>
>>> >> >>> > Hope it was clear.<br>
>>> >> >>> ><br>
>>> >> >>> ><br>
>>> >> >>> > Thanks a lot,<br>
>>> >> >>> ><br>
>>> >> >>> > Avi<br>
>>> >> >>> ><br>
>>> >> >>> ><br>
>>> >> >>> ><br>
>>> >> >>> > On Wed, 14 Sep 2022 at 2:42 Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>><br>
>>> wrote:<br>
>>> >> >>> ><br>
>>> >> >>> >> > Hi,<br>
>>> >> >>> >> ><br>
>>> >> >>> >> > In case query already cached, why in extended query mode the<br>
>>> db<br>
>>> >> knows<br>
>>> >> >>> >> about<br>
>>> >> >>> >> > that query?<br>
>>> >> >>> >> > Because in that case pgpool should return the result from the<br>
>>> >> cache<br>
>>> >> >>> in<br>
>>> >> >>> >> > order to save db resources.<br>
>>> >> >>> >> > But I found that although we have the query in cache pgpool<br>
>>> still<br>
>>> >> >>> send<br>
>>> >> >>> >> > parse and bind request to the db.<br>
>>> >> >>> >> ><br>
>>> >> >>> >> > Please share your thoughts 🙏<br>
>>> >> >>> >><br>
>>> >> >>> >> Yes, Pgpool-II only caches the result of execute message. The<br>
>>> main<br>
>>> >> >>> >> reason is, to not return stale cache. After receiving a close<br>
>>> >> message<br>
>>> >> >>> >> for the statement or the portal that is bound to the execute<br>
>>> >> message,<br>
>>> >> >>> >> the cache for the execute message should not be returned. When<br>
>>> >> >>> >> Pgpool-II receives close messages, they remove the internal<br>
>>> record<br>
>>> >> of<br>
>>> >> >>> >> previpusly received statement or portal. When an execute message<br>
>>> >> >>> >> arrives, Pgpool-II checks whether the record for the execute<br>
>>> message<br>
>>> >> >>> >> exists. If does not, the request fails. This strategy is simple<br>
>>> but<br>
>>> >> >>> >> works well.<br>
>>> >> >>> >><br>
>>> >> >>> >> I think the saving by caching parse message is not small for<br>
>>> >> >>> >> especially complex queries. But in this case users already<br>
>>> reuse the<br>
>>> >> >>> >> prepared statement anyway.<br>
>>> >> >>> >><br>
>>> >> >>> >> I think the saving of bind message is usually small because it<br>
>>> does<br>
>>> >> >>> >> not involve planning in most cases.<br>
>>> >> >>> >><br>
>>> >> >>> >> Best reagards,<br>
>>> >> >>> >> --<br>
>>> >> >>> >> Tatsuo Ishii<br>
>>> >> >>> >> SRA OSS LLC<br>
>>> >> >>> >> English: <a href="http://www.sraoss.co.jp/index_en/" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en/</a><br>
>>> >> >>> >> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>>> >> >>> >><br>
>>> >> >>><br>
>>> >> >><br>
>>> >><br>
>>><br>
>><br>
</blockquote></div></div>