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

[Tatsuo Ishii]

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

Ok. Anyway both of your proposals are clearly feature additions, so
even if we decided to implement them, we will not back patch them to
stable branches. That's the strict project's policy.

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