[pgpool-general: 8968] Re: Can we define a function func_set_search_path so that select func_set_search_path will execute to both primary and secondary nodes ?

[Achilleas Mantzios - cloud]

Thank you

the point of my set_search_path function is to be dynamic, it depends on 
current_user, I cannot declare this as immutable, and I don't want this 
to be cached by pgpool (I have caching enabled).

I am wondering :

a) How easy it could be to implement a new feature in pgpool to declare 
functions that will have to be routed in both primary and replica 
(POOL_BOTH in src/context/pool_query_context.c ).

b) if there was a mechanism in postgresql that a SET foo_guc_var="val" 
would result in calling a function.

On 11/14/23 02:18, Tatsuo Ishii wrote:
> Hi,
>
>> Hello pgpool world
>>
>> pgpool thankfully sends all SET commands to both primary and secondary
>> nodes. Hence, it handles correctly SET search_path TO ...;  But what
>> if we have a more complex function or procedure to handle the setting
>> search_path? How can we tell pgpool to execute this function/procedure
>> to all load balancing nodes ?
> Unfortunately there's no such a feature in Pgpool-II.
>
> Maybe you could create wrapper functions calling the search_path
> setting function, say f1 and f2, then call f1 and f2. f1 is declared
> as a volatile function, thus will be sent to primary. On the other
> hand f2 is declared as immutable and will be sent to the load balance
> node (of course you can use write_function_list or read_function_list
> instead).
>
> Best reagards,
> --
> Tatsuo Ishii
> SRA OSS LLC
> English:http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20231114/df513dc2/attachment.htm>