[pgpool-hackers: 362] Re: Auto-learning database-modifying functions

[Bruce Momjian]

On Wed, Sep 11, 2013 at 08:31:30PM +0500, Ahsan Hadi wrote:
>     > I know you can specify a pattern to identify database-modifying
>     > functions, but I am wondering if you could send queries containing
>     > questionable functions to the read-only server, and if you get a
>     > read-only error, then send them to the write server.  A more interesting
>     > approach would be to remember which functions cause these failures and
>     > then auto-route them to the write server when we see them again. 
> 
> 
> How would we decide which functions are questionable?

Every function would be questionable until you tried it.  Because you
cache your failures, you know which ones not to retry.  The performance
impact would be minimal.

>     Interesting idea. Even we could overcome current limitation of
>     pgpool-II: cannot detect write functions used in a view by using the
>     technique.
> 
>     > I believe this would give PgPool the ability to _learn_ which functions
>     > need to go to the write server.  Is this already implemented?
> 
> 
> Are you suggesting that we do this in addition to technique currently available
> for load balancing? 

This would be used in addition to our load-balancing code.  Right now
the parser can't know if a function call is going to cause a write. 
Doing the write on a read-only server, getting the failure, and
remembering that, would allow adaptive learning of which functions need
a write server, and which functions can be load-balanced.

-- 
  Bruce Momjian  <bruce at momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +