[Pgpool-hackers] Master-Save Load-Balancing: Bind / Execute can be sent to different backends - causes failures.

[Bryan Varner]

Hi all,

Over the last couple weeks, I've been earnestly working to get pgpool-II 
working underneath an enterprise java application in a master-slave & 
load-balance configuration. The potential up-side for balancing select 
queries across my nodes would be very beneficial to my environment.

This message is a continuation (and cry for help?!) of my posts to the 
pgpool-general list, where I was trolling for help / assistance.

My backends have been occasionally reporting,
    portal "" cannot be run
or
    prepared statement "S_xxxx" does not exist


I did some digging through the pgpool code on Friday and I was pretty 
certain I'd identified the issue -- now I just have to figure out the 
best (proper) solution.


Based on the way pgpool is determining where to send select queries 
during load_balance with extended protocol support, it's possible for a 
query to Bind() to the standby, and then Execute() on the master -- 
without having been Bound to the master.

I've added some code to pool_proto_modules.c, in the Execute() function 
to inspect the query_context->where_to_send list before and after the 
invocation of pool_where_to_send(). What I'm finding is:
  * The previously Bound() POOL_SENT_MESSAGE->query_context was sent to 
node 1.
  * The currently Execute()ing query_context is being sent to node 0.

The easiest way for this to change, is for the current transaction to 
write between the invocation of Bind() and Execute(), in which case, the 
SELECT statement was bound to the standby (load-balanced) but by the 
time it should be execute()ed, it needs to be bound to the master.

Arguments could probably be made for both sides of which node the actual 
execute should go to. (the bound node, or the master?)

Anyone have thoughts on this?

Regards,
    -Bryan Varner