[pgpool-hackers: 3722] Re: problem when getting function name

[Tatsuo Ishii]

Hi Zhijie,

> 1.when enabled memory query cache, if I call function like the following, the immutable function seems not to be recognized.
> 
> 	postgres=# select postgres.public.immutable_func();
> 
> 
> Currently in pgpool, The process of getting funcname is as follows:
> ----------------------------------------------------------------
> if (length == 1)	/* no schema qualification? */
> {
> 	fname = strVal(linitial(fcall->funcname));
> }
> else
> {
> 	fname = strVal(lsecond(fcall->funcname));	/* with schema
> 							 * qualification */
> }
> ----------------------------------------------------------------
> 
> If function call is in the form of "Catalogname.schemaname.funcname()",
> The function name is the third one in the list.
> 
> Since function name is always the last one in the list, I think we can use "llast(fcall->funcname)"
> to get the function name.
> What do you think?

Consider this more, I think current way to determine whether a
function appearing in a query is immutable is not very
accurate. Suppose we have function f1 in "public" and "myschema"
schema.  When Pgpool-II looks into the query:

SELECT * FROM table where i = f1();

it may find public.f1() or myshcema.f1() depending the schema search
path. This is because current code works like this:

1. In non_immutable_function_call_walker() get function name from
parse tree (in this process catalog or schema name part is omitted).

2. Then execute following in is_immutable_function():
SELECT count(*) FROM pg_catalog.pg_proc AS p WHERE p.proname = '%s' AND p.provolatile = 'i'"
to know whether the function is immutable or not.

I think we should pass function name with schema (and catalog name) if
any to is_immutable_function() and is_immutable_function() should call
to_regproc() to get function oid, then query against pg_proc using the
oid. Since to_regproc() considers the schema search path, it should
return proper oid we are looking for. Note that, however, older
versions of PostgreSQL do not have to_regproc. In this case we have to
fall back to the way we are currently doing.

What do you think?

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