[pgpool-general: 8562] Re: write_function_list in 4.2 - can we use separators?

[zam bak]

On Wed, Jan 25, 2023 at 12:39 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>
> > Hi
> >
> > According to the docs, "write_function_list" in 4.2 is not "schema sensitive"
> > https://www.pgpool.net/docs/42/en/html/runtime-config-load-balancing.html#GUC-WRITE-FUNCTION-LIST
> >
> > This is a change from 4.1  in which the same parameter (called
> > balc_function_list) was not "schema sensitive"
> > https://www.pgpool.net/docs/41/en/html/runtime-config-load-balancing.html#GUC-BLACK-FUNCTION-LIST
> >
> > Since I have a long list of data modifying function prefixes and I
> > also have several schemas where they may exist the following now has
> > to be expanded:
> > write_function_list =
> > 'currval,lastval,nextval,setval,convert_.*,create_.*,delete_.*,handle_.*,insert_.*,mark_.*,move_.*,process_.*,purge_.*,selsert_.*,update_.*,upgrade_.*
> > ,merge_.*'
> >
> > For example, "merge_.*" now has to be
> > "merge_.*,schema1.merge_.*,schema2.merge_.*,schema3.merge_.*"
> > This is going to be rather ....interesting... to manage...
> >
> > My question is:
> > Can I use *spaces*, *tabs* and/or *newlines* to "break apart" this
> > config parameter so its easier to manage in pgpool.conf?
> > Something like following (but only with example for merge_ shown to
> > reduce verbosity):
>
> You can use spaces, tabs, but you cannot use newlines.

Thanks!
What about using regex to specify multiple schemas in 1 pattern.
Something like following for example:
((public|schema1|schema2)\.){0,1}merge_.*
Is that possible?

>
> BTW, why don't use the new feature of 4.2?
>
> -----------------------------------------------------------------
> read_only_function_list (string)
> :
> :
> Note: If this parameter and write_function_list is empty string,
> function's volatile proper will be checked. If the property is
> volatile, the function is regarded as a writing function. This is
> convenient and recommended way. However this requires one extra query
> against system catalog for the first time (in the next time cached
> query result is used and no extra query will be sent). If you don't
> want to send such query, you can keep on using this parameter.
> -----------------------------------------------------------------
>
> By setting:
>
> read_only_function_list = ''
> write_function_list = ''
>
> Pgpool-II will automatically find write functions by looking at the
> function's volatile property. If it is "VOLATILE", that means the
> function writes something and should be sent to primary node only.
>

Yes, I was aware of this and we plan to use it in the future.
However, currently we have a lot of functions which perform "read
only" work but were created without explicitly specifying function
volatility (which then defaults VOLATILE)
Using empty definitions in those 2 params would then cause those
function to be sent to primary always, diluting the effectiveness of
read load balancing (in our case)
We plan to change these functions (there are a lot of them!) but this
will take time and testing so for now we have to rely on the lists...

> Best reagards,
> --
> Tatsuo Ishii
> SRA OSS LLC
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp