[pgpool-hackers: 4552] 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.

Patch with slight modifications have been pushed to master through
v4.2. Thanks.

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp