<div dir="auto">Ok, </div><div dir="auto"><br></div><div dir="auto">In your example you explicitly add the ‘S’ message in your extended-query-cache.data file. </div><div dir="auto">So in that case off course the state changed from active to idle.</div><div dir="auto"><br></div><div dir="auto">I use the pgproto also in my lab and I found that if I moved the ‘S’ message the connection stay on active.</div><div dir="auto"><br></div><div dir="auto">That’s what I mentioned in my latest mails.</div><div dir="auto">That in case pgpool read the extended query from cache (means that pgpool not forward execute message) </div><div dir="auto">I can see when debug that instead of read the next message from frontend which is ‘S’ message and forward to BE. </div><div dir="auto">What happened is that we read a BE response which will be ‘1’ which is ParseComplete.</div><div dir="auto"><br></div><div dir="auto">This scenario not happened when the query not in cache, here after execute send from FE to BE </div><div dir="auto">pgpool read the next message from frontend which is ‘S’ message and forward to BE what change the state from active to idle.</div><div dir="auto"><br></div><div dir="auto">Which that make sense because when we send ‘S’ to BE we actually tell him that I got everything I need and I am ready for query.</div><div dir="auto"><br></div><div dir="auto">Let me know if more information needed.</div><div dir="auto"><br></div><div dir="auto">Thanks,</div><div dir="auto"><br></div><div dir="auto">Avi.</div><div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sat, 17 Sep 2022 at 12:34 Avi Raboah <<a href="mailto:avi.raboah@gmail.com">avi.raboah@gmail.com</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)"><div dir="auto">Hi, </div><div dir="auto"><br></div><div dir="auto">I’ll try to do a demo later today. And i’ll send the flow as I see it when debugging. For sure when sync message forward to backend the state change from active to idle.</div><div dir="auto"><br></div><div dir="auto">And I agree in case sync not forwarded readyForQuery change the state from active to idle.</div><div dir="auto"><br></div><div dir="auto">Thanks,</div><div dir="auto"><br></div><div dir="auto">Avi.</div><div dir="auto"><br></div><div dir="auto"><br></div><div dir="auto"><br></div><div><div class="gmail_quote"><div dir="ltr" class="gmail_attr">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></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)">> Hi,<br>
> <br>
> So I check that, after pgpool read the result from cache I found the 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 becomes<br>
> idle. In case the sync message didn’t read from frontend to backend, 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 enabled<br>
>> and<br>
>> > disabled.<br>
>> > If we querying pgpool in extended query mode and cache disabled I 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 the<br>
>> > backend.<br>
>> > In that case instead of read sync from frontend and update the 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 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>> wrote:<br>
>> >><br>
>> >>> > Hi,<br>
>> >>> > I am not sure I understood what you mean when you mentioned “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. —> miss<br>
>> >>> ><br>
>> >>> > 2. Now in case i’ll run the same query i’ll get it back from 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 didn’t<br>
>> >>> > contains the query params. That’s make sense.<br>
>> >>> ><br>
>> >>> > But in Bind message the packet already contains the query params. So<br>
>> why<br>
>> >>> > you can’t read the result from cache here and we need to wait to the<br>
>> >>> > execute message?<br>
>> >>><br>
>> >>> Bind message does not return the "result". It just returns it succeeded<br>
>> >>> (bind complete message) or failed (error response). The actual result<br>
>> >>> (which is called "portal") is only in PostgreSQL's memory. So "caching<br>
>> >>> result of bind message" is almost meaningless.<br>
>> >>><br>
>> >>> > One more question is why we need to send the bind message anyway to<br>
>> the<br>
>> >>> > backend in case we have already the result of the execute 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 and<br>
>> >>> the query is gone.<br>
>> >>><br>
>> >>> 3. pgpool receives an execute message and tries to extract the query<br>
>> >>> cache, but it has already gone. However pgpool cannot send the 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 concurrently<br>
>> >>> when<br>
>> >>> > I have cache enabled I can see a very big wait because of a lot of<br>
>> >>> > connections in idle ClientRead wait event.<br>
>> >>><br>
>> >>> I am not familiar with performance insights and cannot comment on 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>> wrote:<br>
>> >>> ><br>
>> >>> >> > Hi,<br>
>> >>> >> ><br>
>> >>> >> > In case query already cached, why in extended query mode the 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 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 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 record<br>
>> of<br>
>> >>> >> previpusly received statement or portal. When an execute message<br>
>> >>> >> arrives, Pgpool-II checks whether the record for the execute message<br>
>> >>> >> exists. If does not, the request fails. This strategy is simple 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 reuse the<br>
>> >>> >> prepared statement anyway.<br>
>> >>> >><br>
>> >>> >> I think the saving of bind message is usually small because it 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>
</blockquote></div></div>
</blockquote></div></div>