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

[Yugo Nagata]

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

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