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

[Hou, Zhijie]

Hi Ishii san

> Hou, Zhijie,
> 
> I have modified is_immutable_function() to make it more generaly checks
> not only "immutable" but others including "volatile". While doing this I
> noticed below in non-schema-qualified function name case.
> 
> The format string given to snprintf is:
> SELECT count(*) FROM pg_catalog.pg_proc AS p, pg_catalog.pg_namespace AS
> n WHERE p.proname %s '%s' AND n.oid = p.pronamespace AND n.nspname = '%s'
> AND p.provolatile = 'i'"
> 
> with:
> 
> arg1 = "~"
> arg2 = ".*"
> arg3 = function name (for example "f1")
> 
> So the final SELECT query string will be:
> 
> SELECT count(*) FROM pg_catalog.pg_proc AS p, pg_catalog.pg_namespace AS
> n WHERE p.proname ~ '.*' AND n.oid = p.pronamespace AND n.nspname = 'f1'
> AND p.provolatile = 'i'"
> 
> So it seems the schema name and function name in this SELECT. The fix is
> included in the 0001 patch but it would be nice if you could review this.
> 

The patch looks good.

However, I found a bug in my old patch which will affect your patch.

Currently, the sql is formatted in function function_volatile_property
and will be formatted in pool_search_relcache again.

If arg3 or arg2 contains '%s'(such as : create or REPLACE  function "%s"(integer)), 
the second format will break the sql.

So I try to add a formatted flag in sql comment to avoid this.
A little change in your patch.

Best regards,



-------------- next part --------------
A non-text attachment was scrubbed...
Name: 0005-check-if-sql-formated.patch
Type: application/octet-stream
Size: 1115 bytes
Desc: 0005-check-if-sql-formated.patch
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200731/0619d5a7/attachment.obj>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 0001-check-writing-function-using-volatility_n.patch
Type: application/octet-stream
Size: 6420 bytes
Desc: 0001-check-writing-function-using-volatility_n.patch
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200731/0619d5a7/attachment-0001.obj>