[pgpool-hackers: 4340] Re: Proposal: Add user_redirect_preference_list

[Tatsuo Ishii]

> Ishii-san,
> 
> Thank you!
>  
>> > > I would like to add a new GUC parameter "user_redirect_preference_list" to 4.5
>> > > to allow routing queries to a specific backend node for a specific user connection.
>> > > 
>> > > Currently, we have database_redirect_preference_list and app_name_redirect_preference_list,
>> > > which allow routing queries to a specific backend node for a specific database connection or
>> > > a specific client application connection.
>> > > 
>> > > I think the use case, which uses a connection user based load balance, is also needed.
>> > > 
>> > > For example, an application has GUI management console and server daemon,
>> > > which connect to the same database. GUI management console mainly issues SELECT
>> > > and server daemon mainly issues INSERT.
>> > > In such case, users may want to route all queries issued by GUI management console
>> > > to standby servers to reduce the load on primary server.
>> > > 
>> > > To handle such case, normally we can use app_name_redirect_preference_list.
>> > > However, in some applications the application name may not be configurable.
>> > > 
>> > > That is the reason why I want to add this feature.
>> > > 
>> > > Any comments?
>> > 
>> > Sounds like a useful feature. However, if it's implemented, now we
>> > have
>> > database_redirect_preference_list. app_name_redirect_preference_list
>> > and user_redirect_preference_list. I feel a little bit redudant. I
>> > think we can unify all of them. For example:
>> > 
>> > redirect_preference_list = 'selector:user,user0:primary,user[1-4]:1,user[5-9]:standby'
>> > 
>> > where "selector" is one of "user", "app_name" and "database". In the
>> > example above, it means that:
>> > 
>> > - user0 is redirected to primary
>> > - user1 to user4 are redirected to db node 1
>> > - user5 to user9 are redirected to one of standby servers
>> > 
>> > What do you think?
>> 
>> Thank you!
>> 
>> Yes. I think it is a good idea to unify them.
>> 
>> If we add this new parameter like "redirect_preference_list",
>> I think we should also keep the backward compatibility to make sure
>> the old parameters can work with the new major version.
> 
> I think if we unify the setteing by using a single parameter, such as  "redirect_preference_list", only one of database, user, app_name can be set.
> 
> Currently, we can set both of datanase and app_name.

No. See the manual:

    Note: app_name_redirect_preference_list takes precedence over the
    database_redirect_preference_list.

    For example, if you set database_redirect_preference_list =
    'postgres:standby(1.0)' and app_name_redirect_preference_list =
    'myapp1:primary(1.0)', all SELECT from application myapp1 on
    postgres database will be sent to primary backend node.

> For example:
> 
>   database_redirect_preference_list = 'test:standby'
>   app_name_redirect_preference_list = 'psql:primary'
> 
> For compatibility with this feature, I think we need to
> add a new parameter "user_redirect_preference_list".

Why? We don't have user name based preference list in the stable
releases.

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