[pgpool-general: 348] Re: pgpool-3.1.2 and jdbc driver

[Bryan Varner]

I have submitted patches to pgpool starting in October 2011 to correct 
this issue. I have yet to see any of them accepted.

The issue is that pool_where_to_send is being called for every step of 
query planning / binding, instead of just the initial step (extended 
protocol).

In cases where load demands that the query be redirected to a different 
server than binding originally started on, or that it gets executed on a 
different backend than previously bound to, the query will fail since it 
will have different id's on the various backends, and the JDBC driver 
tracks precompiled IDs and uses those for it's performance tweak with 
extended query support.

The easiest solution is to just stop calling pool_where_to_send for each 
step, and to trust where things were balanced to originally, and use 
synchronous replication. The issue is that if you're using asynch 
replication, and the backend the query was bound to is marked out of 
synch (not able to direct queries to it) then you'll still experience 
this issue.

I have all but ruled out pgpool as a long-term solution for 
load-balancing pg due to this issue being understood, identified, and 
unresolved for more than 9 months. I've gotten little to zero feedback 
on the patches I've submitted, and have no idea why they've not been 
accepted.

Your options at this point with JDBC and pgpool in replication mode are:
  * Turn off load-balance, and use pgpool for HA only.
  * Turn on load-balance, and disable the JDBC driver prepareThreshold 
(I think it's -1?) This may not work well either, and will have 
disastrous impact on performance.
  * Cross your fingers that this gets resolved, now that there's more 
than one person complaining about it.

Regards,
  - Bryan Varner




On 04/05/2012 04:01 AM, Евгений Селявка wrote:
> Dear users, I have installed postgresql-9.1.3 in streaming replication
> mode and pgpool-3.1.2 only in load balancing mode. In log i see message
> like this
>
> 2012-04-04 15:47:39 LOG: pid 16968: pool_send_and_wait: Error or notice
> message from backend: : DB node id: 0 backend pid: 4664 statement:
> SELECT 1 message: portal "" does not exist
>
> I have jdbc: 9.0-801.jdbc4
> And pooling connection with java dbcp
>
> <bean id="Ds" class="org.apache.commons.dbcp.BasicDataSource"
> destroy-method="close">
> <property name="driverClassName" value="org.postgresql.Driver" />
> <property name="url"
> value="jdbc:postgresql://${db.server}:${db.port}/${db.name
> <http://db.name>}" />
> <property name="username" value="${db.user}" />
> <property name="password" value="${db.pass}" />
> <property name="maxActive" value="${db.connections.max_active}" />
> <property name="maxIdle" value="${db.connections.max_idle}" />
> <property name="maxWait" value="30000" />
> <property name="validationQuery" value="SELECT 1" />
> <property name="removeAbandoned" value="true" />
> <property name="removeAbandonedTimeout" value="60" />
> </bean>
>
> While googling i see that this bug was fixed in version pgpool-3.0.3?
>
> --
> С уважением Селявка Евгений