View Issue Details

IDProjectCategoryView StatusLast Update
0000607Pgpool-IIEnhancementpublic2020-05-18 13:51
ReporterNikhilAssigned Tohoshiai 
PrioritynormalSeveritytweakReproducibilityalways
Status feedbackResolutionopen 
Product Version4.0.7 
Target VersionFixed in Version 
Summary0000607: Executing Procedures containing DML on primary node only
DescriptionHi,

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 ReproduceCreating a Procedure with some insert and select statements;

Calling the procedure using EXEC from PgPool
Tagspgpool in load balancing mode

Activities

hoshiai

2020-04-30 10:33

developer   ~0003345

How environment and client application do you use with 'EXEC' command?

Nikhil

2020-05-05 04:26

reporter   ~0003356

Hi,

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.

hoshiai

2020-05-18 13:51

developer   ~0003369

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

Issue History

Date Modified Username Field Change
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