[pgpool-hackers: 3705] Query mis cached

[Hou, Zhijie]

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.



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.



Best regards,

houzj


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200709/e4c62ed5/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 0005-Update-gram_minimal.y.patch
Type: application/octet-stream
Size: 721 bytes
Desc: 0005-Update-gram_minimal.y.patch
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200709/e4c62ed5/attachment.obj>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 0004-Update-pool_select_walker.c.patch
Type: application/octet-stream
Size: 820 bytes
Desc: 0004-Update-pool_select_walker.c.patch
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200709/e4c62ed5/attachment-0001.obj>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 0003-Update-pool_memqcache.h.patch
Type: application/octet-stream
Size: 1030 bytes
Desc: 0003-Update-pool_memqcache.h.patch
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200709/e4c62ed5/attachment-0002.obj>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 0002-Update-pool_memqcache.c.patch
Type: application/octet-stream
Size: 5516 bytes
Desc: 0002-Update-pool_memqcache.c.patch
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200709/e4c62ed5/attachment-0003.obj>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 0001-Update-pool_proto_modules.c.patch
Type: application/octet-stream
Size: 835 bytes
Desc: 0001-Update-pool_proto_modules.c.patch
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200709/e4c62ed5/attachment-0004.obj>