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

[Tatsuo Ishii]

> Hello again pgpool hackers,
> 
> 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.

> 2.  If error 40P02 is raised, then automatically retry the statement;
> because of point (1), this will happen on a different server.  There
> is a small limit on the number of retries (configurable).
> 
> I think that would be better than trying to check the
> pg_stat_replication view periodically on the master (the idea I
> mentioned in the earlier email).  What do you think?
> 
> Here is my idea for step 2.  Multi-statement transactions that begin
> with "BEGIN ... READ ONLY" could also be sent to read-only servers.
> If the error 40P02 occurs, then perhaps the transaction could be
> automatically retried on another server, but *only* if it happens when
> running the *first* statement in the transaction.  (It doesn't make
> sense to replay the transaction automatically on another node if the
> user has already seen some results, which is why it's OK to do it if
> the first statement fails but not if later statements fail).  In
> practice, this mean that REPEATABLE READ + READ ONLY transactions
> would benefit from automatic load balancing with automatic transparent
> retry, because REPEATABLE READ transactions can only fail with error
> 40P02 on the first statement.  That would provide end users with a
> nice way to achieve load balancing of multi-statement read only
> transactions, as long as they are prepared to wrap their transactions
> in "BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY".
> Perhaps READ COMMITTED could be supported too, but then we would have
> to send the 40P02 error to the user: automatic retry would not be
> possible, unless it happens to be on the first statement.  Actually
> that it is very likely to occur on the first statement, so maybe
> that's still quite usable.
> 
> Thoughts?
> 
> -- 
> Thomas Munro
> http://www.enterprisedb.com