Pgpool master-slave replication with load balancing - prepared statement "S_380" does not exist

Question

We are using pgpool to run 2 postgresql servers in master-slave mode with load balancing.

Sometimes (it is inconsistent, it works fine other times), I see this exception in the application logs -

09-10-17 01:35:01:627 - {ERROR} util.JDBCExceptionReporter Thread[https-jsse-nio-8443-exec-3]; ERROR: cannot execute UPDATE in a read-only transaction

This is around a call to the write psql function called by the app server. Don't see any issues around other write statement, only this.

Don't know if it's pgpool issue.

Answer 1

You need to tell the Pgpool-II about the usage of write functions in SELECT statements.

The reason is when the load balancing is enabled in master-slave mode, Pgpool sends the write queries to MASTER database and load balances the read queries. More specifically statements such as DELETE, INSERT, UPDATE are routed to Master while SELECT gets load balanced. Now the problem is, when SELECT statement uses a write function, Pgpool still considers the statement as read-only and load balance it. Since it has no visibility of function definition. So in nutshell Pgpool-II needs to know the usage of functions in SELECT statements which can write to database to make sure such SELECTs should not be load balanced.

For that you have two options.

1. Use Pgpool's black_function_list or whitle_function_list configurations. http://www.pgpool.net/docs/latest/en/html/runtime-config-load-balancing.html

2. Use /*NO LOAD BALANCE*/ prefix for queries containing write function.

/*NO LOAD BALANCE*/ SELECT my_writing_function();

But the latter needs modifications in SQL statements of the existing applications which makes it not a very viable option.