pgpool issue

[chandang]

Hi,
I am getting below jdbc exception in my logs and the function call is failing:

JDBCException reporter: Error: Cannot execute UPDATE in read-only transaction.

I have enabled pgpool load balancing, is there any issues in my setup or this is postgres issue?

[chaitalirs · Accepted solution]

Hi chandang,

The load balancing mode of pgpool sends read-write (CREATE/INSERT/UPDATE/DELETE) queries to master and SELECT queries to the slave servers.

After looking at the error you are facing, it seems that your function call is executing some UPDATE operation. As the call to the function is SELECT function_name();, the load balancer sends it to slave as it DETECTS select and the update operation cannot be completed.

You can :

1. Before calling the function add comment /*NO LOAD BALANCE*/

2. Force the function call to master server i.e with -h option.

3. Add the function name in the parameter "black_function_list" in pgpool.conf file.

Refer document :

http://www.pgpool.net/pgpool-web/contrib_docs/simple_sr_setting_3.1/pgpool.conf

This should resolve your issue. In case you still receive the issue please share the function code and pgpool.conf to verify the setup.

[chandang]

Thanks chaitalirs.

If I add the function name in pgpool.conf will it require restart of pgpool and will I be able to call the function normally without adding any comments ?

[chaitalirs]

Hi chandang,

Yes you can use either of the options which were specified earlier. If you add the function name in the black_function_list parameter then you can execute your fucntion normally. Just a reload of pgpool will work for this parameter to take the new value, there is no need of pgpool restart.