pgpool issue

SOLVED
Highlighted
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?

Archived Discussions


Effective March 31st, we will no longer engage on PostgresRocks.


How to engage with us further?


  • Thought Leadership: EDB Blogs

  • Tips and Tricks: Postgres Tutorials

  • Customer Support: Create a Case Please note: Only customers with an active EDB support subscription and support portal authorization can create support ticket

  • Engage on Stackoverflow While engaging on Stackoverflow tag the question with EDB or EnterpriseDB.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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.

View solution in original post

3 REPLIES 3
Highlighted
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.

View solution in original post

Highlighted
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.

© 2019 EnterpriseDB Corporation. All rights reserved.   |   Privacy Policy   |  Terms of Use   |   Trademarks