cancel
Showing results for 
Search instead for 
Did you mean: 

pgpool issue

SOLVED
Level 2 Adventurer

pgpool issue

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?
Tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions
EDB Team Member

Re: pgpool issue

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 :
 
This should resolve your issue. In case you still receive the issue please share the function code and pgpool.conf to verify the setup.
3 REPLIES
EDB Team Member

Re: pgpool issue

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 :
 
This should resolve your issue. In case you still receive the issue please share the function code and pgpool.conf to verify the setup.
Level 2 Adventurer

Re: pgpool issue

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 ?
Highlighted
EDB Team Member

Re: pgpool issue

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.