[pgpool-hackers: 3758] Re: Using volatile property instead of black_function_list

[Tatsuo Ishii]

Hi Hou,

>> So I try to add a formatted flag in sql comment to avoid this.
>> A little change in your patch.
> 
> Unfortunately this will not work. If you do not allow snprintf in
> pool_search_relcache, it could use wrong SQL. For example we have
> following query when we call pool_create_relcache:
> 
> /*FORMATTED*/SELECT count(*) FROM pg_catalog.pg_proc AS p, pg_catalog.pg_namespace
> AS n WHERE p.proname = '%s' AND n.oid = p.pronamespace AND n.nspname
> = 'public' AND p.provolatile = 'v"
> 
> Next pool_search_relcache() may be called with
> public.f1. pool_search_relcache() will find a cache entry for
> public.%s and returns the result for public.f1.
> 
> Also my patch was wrong. Suppose we have below at the when relcache is
> first created:
> 
> SELECT count(*) FROM pg_catalog.pg_proc AS p, pg_catalog.pg_namespace
> AS n WHERE p.proname = 'f1' AND n.oid = p.pronamespace AND n.nspname =
> 'public' AND p.provolatile = 'v"
> 
> Next pool_search_relcache() may be called with
> public.f2. pool_search_relcache() will find a cache entry for
> public.f1 and returns the result for public.f2.
> 
> Let me think how to solve these problems.

I have come up with attached patches. Only
0001-check-writing-function-using-volatility.patch has been changed.

My idea is, let SQL template passed to pool_create_relache be "%s",
and pass the actual SQL string created by function_volatile_property
to "table" parameter of pool_search_relcache. So practically
formatting using snprintf happens only once in
function_volatile_property. This way, we can handle function whose
name includes "%" such as "%s".

What do you think?
--
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: v2-0001-check-writing-function-using-volatility.patch
Type: text/x-patch
Size: 6502 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200801/d371be60/attachment.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: v2-0002-document.patch
Type: text/x-patch
Size: 2718 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200801/d371be60/attachment-0001.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: v2-0003-pgpool.conf.patch
Type: text/x-patch
Size: 6937 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200801/d371be60/attachment-0002.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: v2-0004-regression-test.patch
Type: text/x-patch
Size: 1684 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200801/d371be60/attachment-0003.bin>