[pgpool-general: 8577] 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.


Just a follow up on this.
I tried using "space" after the comma just to try to get some
readability in a config...
When I did that, all the functions were being executed even on replica
nodes (generating errors because they are read only)
After collapsing the list to contain no spaces, it worked...

# does not work (space after comma as separator)
# SQL being executed on read replicas also resulting in errors
#
write_function_list = 'currval, lastval, nextval, setval,
public.upgrade_.*, upgrade_.*, public.update_.*, update_.*,
public.selsert_.*, selsert_.*, public.purge_.*, purge_.*,
public.process_.*, process_.*, public.move_.*, move_.*,
public.merge_.*, merge_.*, public.mark_.*, mark_.*,
public.lcm_purge_.*, lcm_purge_.*, public.insert_.*, insert_.*,
public.delete_.*, delete_.*, public.create_.*, create_.*,
public.convert_.*, convert_.*, public.apply_.*, apply_.*,
schema2.get_messages_as_csv, schema1.update_.*, schema1.purge_.*,
schema1.insert_.*, schema1.create_.*, schema1.convert_.*,
schema1.pfx_ohip_create_.*, schema1.pfx_mhs_update_.*,
schema1.pfx_mhs_create_.*, schema1.pfx_mhs_apply_.*,
schema1.pfx_bc_process_.*, schema1.pfx_sah_wcb_apply_.*,
schema1.pfx_sah_update_.*, schema1.pfx_sah_handle_.*,
schema1.pfx_sah_create_.*, schema1.pfx_sah_invoice_charge,
schema1.pfx_sah_apply_.*'


# works (no spaces)
#
write_function_list =
'currval,lastval,nextval,setval,public.upgrade_.*,upgrade_.*,public.update_.*,update_.*,public.selsert_.*,selsert_.*,public.purge_.*,purge_.*,public.process_.*,process_.*,public.move_.*,move_.*,public.merge_.*,merge_.*,public.mark_.*,mark_.*,public.lcm_purge_.*,lcm_purge_.*,public.insert_.*,insert_.*,public.delete_.*,delete_.*,public.create_.*,create_.*,public.convert_.*,convert_.*,public.apply_.*,apply_.*,schema2.get_messages_as_csv,schema1.update_.*,schema1.purge_.*,schema1.insert_.*,schema1.create_.*,schema1.convert_.*,schema1.pfx_ohip_create_.*,schema1.pfx_mhs_update_.*,schema1.pfx_mhs_create_.*,schema1.pfx_mhs_apply_.*,schema1.pfx_bc_process_.*,schema1.pfx_sah_wcb_apply_.*,schema1.pfx_sah_update_.*,schema1.pfx_sah_handle_.*,schema1.pfx_sah_create_.*,schema1.pfx_sah_invoice_charge,schema1.pfx_sah_apply_.*'



>
> 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.
>
> Best reagards,
> --
> Tatsuo Ishii
> SRA OSS LLC
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp