[pgpool-hackers: 3706] Re: Query mis cached

[Tatsuo Ishii]

> Hi pgpool hackers,
> 
> 
> 
> I found the following SQL will be cached, when enabled memory query cache,
> 
> 
> 
>         "with cte as (insert into test2 values(1) returning *) select * from test2;"
> 
> 
> 
> I think the SQL has Data-Modifying Statements in with clause should not be cached.
> 
> Because Once cached, the Data-Modifying Statements will not be executed again which is not expected.

You are right.

> It seems to be better to analyze the with clause like the following:
> 
> 
> 
> --------For UPDATE/INSERT/DELETE type SQL:
> 
>                 extract oids from its with clause which will be invalidated.
> 
> 
> 
> --------For SELECT type SQL:
> 
>                 If Data-Modifying Statements in its with clause, the select will not be cached,
> 
>                 and the oids from with clause will be invalidated too.
> 
> 
> 
> What do you think?
> 
> 
> 
> I try to solve the problem and made a patch for the code.

Thank you for the patch! I will look into this.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp