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

[Hou, Zhijie]

Hi Ishii san

> The patch looks good except following warnings:

Thanks for pointing it out, I removed the trailing whitespace and made a new patch.

> Also I think it'd be better to add a regression test for this. Probably add to 006.memqcache?

Yes, you are right, I added the regression test to 006.memqcache, patch attatched.
If the regression test is not enough, Please let me know, I am glad to add more testcase.

Best regards,
houzj

-----Original Message-----
From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp] 
Sent: Monday, July 13, 2020 10:15 PM
To: Hou, Zhijie <houzj.fnst at cn.fujitsu.com>
Cc: pgpool-hackers at pgpool.net
Subject: Re: [pgpool-hackers: 3706] Re: Query mis cached

>> 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.

The patch looks good except following warnings:

git apply ~/0002-Update-pool_memqcache.c.patch
/home/t-ishii/0002-Update-pool_memqcache.c.patch:165: trailing whitespace.
				/*
/home/t-ishii/0002-Update-pool_memqcache.c.patch:175: trailing whitespace.
					POOL_SETMASK(&oldmask);					
warning: 2 lines add whitespace errors.

Also I think it'd be better to add a regression test for this. Probably add to 006.memqcache?

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




-------------- next part --------------
A non-text attachment was scrubbed...
Name: Update-test.sh.patch
Type: application/octet-stream
Size: 1676 bytes
Desc: Update-test.sh.patch
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200714/5671ed71/attachment.obj>
-------------- 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/20200714/5671ed71/attachment-0001.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/20200714/5671ed71/attachment-0002.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/20200714/5671ed71/attachment-0003.obj>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 0002-Update-pool_memqcache.c.patch
Type: application/octet-stream
Size: 5510 bytes
Desc: 0002-Update-pool_memqcache.c.patch
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200714/5671ed71/attachment-0004.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/20200714/5671ed71/attachment-0005.obj>