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

Bryan Varner bvarner at polarislabs.com
Sun Apr 8 23:30:27 JST 2012


I apologize for the misunderstanding. I thought we settled on just removing pool_where_to_send for the bind / describe / execute.

I did try out sending the parse/bind/describe to all backends, but couldn't guarantee that the generated IDs were the same on all backends. Since the JDBC driver references things by those ids, it was going to be problematic with pgpool.

-Bryan


________________________________________
From: Tatsuo Ishii [ishii at postgresql.org]
Sent: Thursday, April 05, 2012 7:04 PM
To: Bryan Varner
Cc: pgpool-general at pgpool.net
Subject: Re: [pgpool-general: 348] Re: pgpool-3.1.2 and jdbc driver

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


More information about the pgpool-general mailing list