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

[Tatsuo Ishii]

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

Yes, of course.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp