[pgpool-hackers: 4551] Re: 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).

It turned out that we have more similar bugs and the previous patch
was not enough to fix them.

Case 1:
execute(0 parameter which means fetch all rows)
execute(0 parameter)

The second execute should return 0 row but will return full rows.

Case 2 (suppose rows to be retrieved are 10):
execute(0 parameter)
execute(5 parameter)

The second execute should return 0 row but will return up to 5 rows
with the previous patch (without the patch it will return 10 rows).

Attached patch should address all possible similar bugs as far as I
know.

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: v1-0001-Fix-yet-another-query-cache-bug-in-streaming-repl.patch
Type: text/x-patch
Size: 18026 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20241211/26c3e0ce/attachment.bin>