[pgpool-hackers: 1620] Re: Redirecting queries to standby as much as possible

[Tatsuo Ishii]

> On Fri, 10 Jun 2016 16:48:19 +0900 (JST)
> Tatsuo Ishii <ishii at postgresql.org> wrote:
> 
>> > On Fri, 10 Jun 2016 15:13:19 +0900 (JST)
>> > Tatsuo Ishii <ishii at postgresql.org> wrote:
>> > 
>> >> Submitter of bug 195 (http://www.pgpool.net/mantisbt/view.php?id=195)
>> >> wants to redirect queries to standby as much as possible if the
>> >> transaction is read only. This brings up interesting conflicts to
>> >> current behavior of pgpool-II:
>> >> 
>> >> - the weight parameters of backend info needs to be ignored if the
>> >>   transaction is read only
>> >> 
>> >> - black and white function list need to be ignored if the transaction
>> >>   is read only
>> >> 
>> >> - database_redirect_preference_list needs to be ignored if the
>> >>   transaction is read only
>> >> 
>> >> - app_name_redirect_preference_list needs to be ignored if the
>> >>   transaction is read only
>> >> 
>> >> After all, if we don't want to confuse existing users in the use cases
>> >> above, we need to invent a new switch to change the redirecting
>> >> behavior depending on whether the transaction is read only or not.
>> > 
>> > I agree that to add new switch is good because it is simple and preserves
>> > compatibility.
>> 
>> Question is, whether it's worth the trouble. Is there any people other
>> than the bug submitter who wants the feature?
> 
> I also doubt the needs, but someone might want a transaction including
> SET to be sent to standby. We might get user's opinions about the feature
> from pgpool-general rather than pgpool-hackers.

If SET commands are sent to standby only unconditionaly, there could
be a problem. Consider following command sequence:

SET statement_timeout TO 1s;	-- sent to standby only
UPDATE .... -- routed to primary

Suppose the UPDATE was very heavy and took over 1 second. No statement
timeout was raised since "SET statement_timeout TO 1s;" had not been
sent to primary. This is definitely against what the user expected.

>> > However, even in the current version, the priority of the above rules is
>> > confusing. I think the priority rule should be summarized in the document
>> > explicitly.
>> 
>> Welcome volunteering the work:-)
> 
> Anyone? ... me? :-}
> 
>> 
>> 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>