[pgpool-hackers: 2625] Re: Load-balancing control for specific queries

[Yugo Nagata]

On Wed, 15 Nov 2017 21:16:54 +0900
Yugo Nagata <nagata at sraoss.co.jp> wrote:

> On Mon, 13 Nov 2017 08:16:45 +0900 (JST)
> Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 
> > > Hi Pgpool-II developers,
> > > 
> > > One of our clients wants a new feature that force specific 
> > > queries to be sent to the primary rather than load-balancing.
> > > 
> > > Even though we can do this by adding the /*NO LOAD BALANCE*/ comment
> > > to queries, this requires modifying application codes and
> > > this is not always possible.
> > > 
> > > We can think of at least two possible designs for this feature.
> > > 
> > > (1) Defining blacklist
> > > 
> > > Like black_function_list, specifying queries not to be load-balanced.
> > > 
> > > (2) Specifiying desitination nodes
> > > 
> > > Like database_redirect_preference_list, specify pairs of queries and node id
> > > (or primary/standby).
> > > 
> > > In both cases, the target queries can be specified by regexp pettern or tables
> > > included in the queries. (The client's preference is using regexp.)
> > > 
> > > How about introducing this new feature for Pgpool-II 3.8?
> > 
> > Why the query needs to be executed on the primary?
> 
> The application firstly issues read queries, and then issues
> write queries based on the result of the read queries. However,
> since the result of the read queries issued for the standby node
> can be old, so the write query can fail due to unique constrain
> conflict, for example.
> 
> The customer thought that if the read query is issued to the primary,
> the result of read queries will be latest, so this problem can be avoided.
> 
> 
> I have realized that this problem cannot be resolved totally without
> taking row locks when issueing the read query. Even if all queries are
> sent to the primary, other transaction may update the tables.
> However, the feature can reduce the problem, when there are not so many
> concurrency transactions....

By the way, if this proposal is accepted and the feature is implemented, 
I think this will appear, at the earliest, in the next major release 3.8 
and it is in Autumn next year, right?

> 
> > 
> > Best regards,
> > --
> > Tatsuo Ishii
> > SRA OSS, Inc. Japan
> > English: http://www.sraoss.co.jp/index_en.php
> > Japanese:http://www.sraoss.co.jp
> 
> 
> -- 
> Yugo Nagata <nagata at sraoss.co.jp>
> _______________________________________________
> pgpool-hackers mailing list
> pgpool-hackers at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-hackers


-- 
Yugo Nagata <nagata at sraoss.co.jp>