[Pgpool-general] Using SELECT + nextval + load balance with pgpool

[Tatsuo Ishii]

> Hello,
> 
> I think we have a problem with pgpool.
> 
> Environment: pgpool+2 PostgreSQL Servers + load balancing and
> replication enabled.
> 
> Case: The application runs nextval to get the next id of the sequence.
> Since this is a SELECT query, pgpool tries to send this query to only 1
> server. Then, the application runs INSERT; however this results with
> failure since the values of the sequences do not match between two
> PostgreSQL servers.
> 
> I think this is not an application-leve bug; but it is a bug of pgpool.
> pgpool must ignore load balancing if the query includes nextval or
> setval.
> 
> Comments? Tatsuo, can you patch this?

It's documented restriction in pgpool README:

   Please note that a SELECT may modify databases by calling a
   updatable function. In this case you should NOT use the load
   balancing. Othewise pgpool will fail due to the contents difference
   between the master and the secondary database. You can avoid the
   load balancing by putting spaces or comments in the begining of the
   query.

Since nextval() is kind of updatable function, you need to avoid use
the function with load balancing.

To solve the problem in a more sophisticated way, pgpool needs to know
1) there's a function call in the qeuery 2) if the function has
some side effects. #1 needs SQL parser which is possible with
pgpool-II. #2 is more challenging. To judge it, pgpool needs to access
the query plan. Any idea?
--
Tatsuo Ishii
SRA OSS, Inc. Japan