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

[Tatsuo Ishii]

I thought your patch has been on a discussion. The thread has ended
here:

http://www.pgpool.net/pipermail/pgfoundry/pgpool-general/2011-October/004216.html

I had been waiting for your reply...
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> 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?
>>
>> --
>> С уважением Селявка Евгений
> 
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general