[pgpool-hackers: 2424] Re: Load balancing with synchronous replication

[Tatsuo Ishii]

> On Sun, Jul 2, 2017 at 10:39 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>>> I have now renamed that proposed feature to "synchronous replay".  I
>>> am wondering if it would be a good idea to do a proof-of-concept patch
>>> for pgpool.  One of the central ideas of my patch is that it should be
>>> easy to use for end users, which is where pgpool can help.
>>>
>>> Here is my idea for step 1.  Use the regular master/slave load
>>> balancing mode, but with a couple of small modifications:
>>>
>>> 1.  If error 40P02 is raised (the new error introduced by my patch,
>>> "synchronous replay is not available"), then remember not to pick that
>>> server again for N seconds (configurable).
>>
>> Does "my patch" mean a patch for PostgreSQL or Pgpool-II?
>> I am a little bit confused.
> 
> Sorry for the confusing language... I mean that error 40P02 is raised
> by the synchronous replay patch[1].

Thank you for the explanation. So the patch is for PostgreSQL 11, I guess.

> I don't have a patch for pgpool,
> I'm just speculating about what change would be needed for pgpool to
> make it easy for end users.  I think it should recognise this new
> error, "ban" that slave server for N seconds and retry the query on a
> different one.  Do you think that would be very difficult?  Is there
> any architectural reason why it would be difficult for pgool to "ban"
> servers or to re-run queries on another servers?  I would be grateful
> for any ideas or feedback!

Probably "ban" serves is not very difficult. We already do it in
different situation: within an explicit transaction, SELECTs can be
load balanced. However once write query is issued, subsequent SELECTs
are sent to primary only until the transaction commits or aborts.

Re-run a query would be much more difficult, especially if we need to
deal with extended queries. I will explain it in a different email.

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