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

Yugo Nagata nagata at sraoss.co.jp
Thu Nov 30 18:15:56 JST 2017


On Thu, 30 Nov 2017 18:00:10 +0900
Yugo Nagata <nagata at sraoss.co.jp> wrote:

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

..and, In this case, the feature will be implemented only in the new major
version but never in minor versions in the previous series. Is this also 
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>
> _______________________________________________
> pgpool-hackers mailing list
> pgpool-hackers at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-hackers


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


More information about the pgpool-hackers mailing list