[pgpool-general: 568] Re: Companies using pgpool-II

[Bryan Varner]

> I assume all extended queries in your example are unnamed statement.
> (please correct me if I am wrong).

Yes. You are correct.

> You mean parse, bind, (describe) and execute message? I'm asking you
> because there's no such a thing PREPARE in extended protocol.

Again, you are correct.

>> write query is issued, pgpool pegs the TX to the master, any future
>> query gets sent there.
>>
>> read query #2 is issued, (SELECT bar FROM foo;) pgpool sends it to the
>> master.
>
> I assume you are saying that parse(SELECT bar FROM foo), bind,
> describe and execute messages are sent from client.

Yes, JDBC driver seems to send these.

>> read query #1 is issued again. pgpool sends it to the master.
>> It has already done PREPARE, BIND, so it tries EXECUTE.
>>
>> The master has no record of Query #1 being PREPARED, or BOUND. Error
>> is thrown.
>
> I'm confused. What do you mean by "read query #1 is issued again"? Are
> you saying that only execute message sent from client?  If so, this is
> just a client's fault since unnamed statement/portal disappears once
> it is bound to unamed statement.

http://jdbc.postgresql.org/documentation/81/server-prepare.html

When using the JDBC driver, it appears that it does just that.

The default value in the JDBC driver for prepared statement is (iirc) 5, 
which means executing the same select on a pooled connection, results in 
a major performance improvement. The server does less processing, and 
the JDBC driver doesn't have to re-fetch resultset meta data for each 
execution of the query.

I created a small java project some time ago to demonstrate the issue. 
It's not 100% reliable, since it's highly dependent upon the decision of 
pool_where_to_send, and I don't know of any way to force 
pool_where_to_send to make the wrong choice. I just know that given a 
configuration of more than one backend in master_slave with 
load_balance, it's a matter of time (and opportunity from a work-load) 
before pgpool sends a parse / bind / execute to the wrong backend. I've 
uploaded the project source as a .zip file to: 
http://www.varnernet.com/~bryan/files/pgpool-buster.zip

Regards,
-Bryan