View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0000607||Pgpool-II||Enhancement||public||2020-04-28 15:29||2020-05-18 13:51|
|Target Version||Fixed in Version|
|Summary||0000607: Executing Procedures containing DML on primary node only|
We have a 3 node PostgreSQL cluster with SR. We are using 3 node Pgpool for Load balancing and Connection Pooling.All reads to standby and only writes to master using backend_weight.
We have some functions and stored procedures in our PostgreSQL database that contains a mix of SELECT and INSERT/UPDATE queries. We have added a regular expression in black_function_list='[A-z][a-z]*' to route all functions to primary node only. We are calling these functions using SELECT. Ex: If Function name - dept_details, then SELECT dept_details(1990);
We would like to know how we can load balance stored procedures to primary node only, which are being called using EXEC. EX: If Procedure name - emp_details, then we call it using EXEC emp_details(8776);
|Steps To Reproduce||Creating a Procedure with some insert and select statements;|
Calling the procedure using EXEC from PgPool
|Tags||pgpool in load balancing mode|
||How environment and client application do you use with 'EXEC' command?|
After using the latest version of Pgpool 4.1, now we can run "EXEC and CALL" on Procedures and it gets executed only on the primary server.
We still have one issue, in that when we run "SELECT anyfunction()" then it gets executed on primary but when we run "SELECT anyprocedure()" then it gives error that "insert cannot be run on a read only node"
We have written a black_function_list = '[a-z A-z].*'. We know that this only works for functions but can any new parameters be added in pgpool.conf to do the same for procedures because procedures can also be called using SELECT.
> We still have one issue, in that when we run "SELECT anyfunction()" then it gets executed on primary but when we run "SELECT anyprocedure()" then it gives error that "insert cannot be run on a read only node"
Did you execute procedure with SELECT statement? I thought that Procedure can only execute CALL statement.
Please reproduce your case with 'log_client_messages = on', and share pgpool.log.
And, Could you share procedure definition, I will test it on my machine too.
|2020-04-28 15:29||Nikhil||New Issue|
|2020-04-28 15:29||Nikhil||Tag Attached: pgpool in load balancing mode|
|2020-04-30 10:31||hoshiai||Assigned To||=> hoshiai|
|2020-04-30 10:31||hoshiai||Status||new => assigned|
|2020-04-30 10:33||hoshiai||Status||assigned => feedback|
|2020-04-30 10:33||hoshiai||Note Added: 0003345|
|2020-05-05 04:26||Nikhil||Note Added: 0003356|
|2020-05-05 04:26||Nikhil||Status||feedback => assigned|
|2020-05-18 13:51||hoshiai||Status||assigned => feedback|
|2020-05-18 13:51||hoshiai||Note Added: 0003369|