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

[Tatsuo Ishii]

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

> Tatsuo, I am absolutely certain that this is a bug with the query
> targeting in load-balance and master_slave modes.
> 
> 
> pool_where_to_send is being called in bind, execute, and describe, and
> should not be.
> 
> Scenario #1 (prepared statement doesn't exist on server)
> 
> TX opens,
> 
> read query #1 is issued, (SELECT foo FROM bar;) pgpool determines it
> can be balanced, and sends it to a hot-standby, where it does PREPARE,
> BIND, and EXECUTE.

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

> 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.

> 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.

Or if you mean by "read query #1 is issued again" is client sends
parse, bind and execute, this itself should not be a source of a
problem.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> TX Closes.
> 
> If the BIND step gets redirected to the master, the portal "" issue
> manifests. This normally happens when you do a WRITE operation while
> iterating a result set from a READ query.
> 
> If the EXECUTE step gets redirected to the master (re-run a previously
> bound query without a cursor reference), then you get the statement
> "..." not found. issue.
> 
> I'm attaching my patch for this once again. It simply removes
> pool_where_to_send from places where the query could be directed to a
> backend which doesn't have it.
> 
> This is extremely common in java applications where a java database
> connection pool is implemented atop the JDBC driver.
> 
> Regards,
> -Bryan
> 
> 
> 
> 
> On 05/30/2012 09:13 AM, Tatsuo Ishii wrote:
>> I'm not sure if it's related to load balancing.
>> I guess it's related to unamed statements.
>> http://www.pgpool.net/mantisbt/view.php?id=14
>>
>> So I think using named statements is one of workarounds for now.
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
>>
>>> I'd say it's a safe assumption that since they're running java apps
>>> (extended protocol) in production they're not using load-balance mode.
>>>
>>> Otherwise, they'd be running into the same issue David Kerr reported
>>> again this week.
>>>
>>> On 05/30/2012 04:28 AM, Tatsuo Ishii wrote:
>>>> They are using pgpool's native replication mode (the site has opened
>>>> in April 2009).
>>>> --
>>>> Tatsuo Ishii
>>>> SRA OSS, Inc. Japan
>>>> English: http://www.sraoss.co.jp/index_en.php
>>>> Japanese: http://www.sraoss.co.jp
>>>>
>>>>> Does anybody know, which mode they are using (replication mode,
>>>>> master/slave mode, ...). If they are using master/slave mode, which
>>>>> replication software is used?
>>>>>
>>>>> Thanks.
>>>>>
>>>>> 2012/5/23 Tatsuo Ishii<ishii at postgresql.org>
>>>>>
>>>>>>> Hi everyone,
>>>>>>>
>>>>>>> on http://wiki.postgresql.org/wiki/Pgpool-II it says that pgpool-II is
>>>>>>> already used for several commercial systems.
>>>>>>>
>>>>>>> I would like to know what companies actually use it. Can you name any?
>>>>>>>
>>>>>>> Thanks for your replies.
>>>>>>
>>>>>> Here it is.
>>>>>>
>>>>>> http://www.sraoss.co.jp/case_study/nttdataccs.php
>>>>>>
>>>>>> (Unfortunately, it is in Japanese).
>>>>>>
>>>>>> The system is for a web site: http://travelpoint.jtb.co.jp/tabicard/
>>>>>> This is for one of the largest travel agency in Japan called "JTB".
>>>>>> (http://www.jtbcorp.jp/en/)
>>>>>>
>>>>>> The system consists of 4 PostgreSQL servers, two pgpool-II servers,
>>>>>> JBoss, Tomcat and Apache. The databases are two groups, each has two
>>>>>> PostgreSQL for HA and load balancing.
>>>>>> --
>>>>>> Tatsuo Ishii
>>>>>> SRA OSS, Inc. Japan
>>>>>> English: http://www.sraoss.co.jp/index_en.php
>>>>>> Japanese: http://www.sraoss.co.jp
>>>>>>
>>>> _______________________________________________
>>>> pgpool-general mailing list
>>>> pgpool-general at pgpool.net
>>>> http://www.pgpool.net/mailman/listinfo/pgpool-general
>>>
>>> _______________________________________________
>>> pgpool-general mailing list
>>> pgpool-general at pgpool.net
>>> http://www.pgpool.net/mailman/listinfo/pgpool-general
>