[pgpool-hackers: 4547] Bug in query cache

[Tatsuo Ishii]

If query cache is enabled and query is operated in extended query mode
and pgpool is running in streaming replication mode, Execute message
could return incorrect result. An example session is below.

FE=> Parse(stmt="", query="SELECT * FROM (VALUES(1),(2))")
FE=> Bind(stmt="", portal="")
FE=> Describe(portal="")
FE=> Execute(portal="")
FE=> Sync
<= BE ParseComplete
<= BE BindComplete
<= BE RowDescription
<= BE DataRow
<= BE DataRow
<= BE CommandComplete(SELECT 2)
<= BE ReadyForQuery(I)

# at this point query cache including 2 rows for the SELECT is created.

FE=> Parse(stmt="", query="SELECT * FROM (VALUES(1),(2))")
FE=> Bind(stmt="", portal="")
FE=> Describe(portal="")
FE=> Execute(portal="") <-- This Execute message has request to return up to 1 row
FE=> Sync
<= BE ParseComplete
<= BE BindComplete
<= BE RowDescription
<= BE DataRow	<-- 2 rows returned instead of 1 row
<= BE DataRow	
<= BE CommandComplete(SELECT 2)	<-- Also this should be "PortalSuspended"
<= BE ReadyForQuery(I)
FE=> Terminate

To fix this, I propose attached patch, which do not try to fetch query
cache if the execute message does not have a request to return
unlimited number of rows (which is indicated by 0).

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
-------------- next part --------------
A non-text attachment was scrubbed...
Name: fix_query_cache.patch
Type: text/x-patch
Size: 1377 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20241205/bea3c278/attachment.bin>