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

[Bo Peng]

Ishii-san,

> >> > > 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.

Yes. I know the setting of app_name takes precedence if both of database and app_name are matched.

If we set:

  database_redirect_preference_list = 'test1:standby'
  app_name_redirect_preference_list = 'app1:primary'

The following query will be sent to primary because the query matched both conditions
and app_name takes precedence

    PGAPPNAME=app1 psql -d test1 -c "select 1"

However, users can configure the behavior of both database and app_name at the same time.

For example, the following query will be sent to standby,

    PGAPPNAME=app2 psql -d test1 -c "select 1"

and the following query will be sent to primary.

    PGAPPNAME=app1 psql -d test2 -c "select 1"


I agree to unify the three settings of database, app_name and user,
but we should mention that user can't set them at the same time.

-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS LLC
https://www.sraoss.co.jp/