1

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.

Mahesh H Viraktamath
  • 818
  • 3
  • 14
  • 34

3 Answers3

4

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.

Muhammad Usama
  • 2,797
  • 1
  • 17
  • 14
0

I found the cause, this was for a psql function which updated the DB, such functions should be included in the black_function_list in pgpool configuration.

Mahesh H Viraktamath
  • 818
  • 3
  • 14
  • 34
0

Add the write functions name list to the black_function_list parameter in the pgpool configuration.

Anusha C
  • 61
  • 4