Re: declare/fetch problem with pgpool 2.2.3; Postgres 8.4; ODBC 8.03.04.00

Andreas Gaab wrote:
>> -----Ursprüngliche Nachricht-----
>> Von: Hiroshi Inoue [mailto:inoue@tpf.co.jp]
>> Gesendet: Donnerstag, 19. November 2009 23:12
>> An: Andreas Gaab
>> Cc: 'pgsql-odbc@postgresql.org'
>> Betreff: Re: [ODBC] declare/fetch problem with pgpool 2.2.3; Postgres 8.4; ODBC 8.03.04.00
>>
>> Hi Andreas,
>>
>> Andreas Gaab wrote:
>>> I encountered a problem using pgpool with two identical postgres 8.4
>>> servers as  backends.>
>>>
>>> Activating the odbc-setting declare/fetch, our application creates an
>>> data mismatch error.
>>>
>>> As far as I can track down the problem, it is caused because the cursors
>>> are defined after an BEGIN, thus with load balancing disabled, but are
>>> CLOSED after the COMMIT, thus are only closed on one server. This leads
>>> to data inconsistency for the next query DECLARING an cursor.
>> Unfortunately I'm not familiar with pgpool.
>> Are DECLARE, FETCH and CLOSE regarded as members of SELECT family ?
>> If so, I'm suspicios if it's appropriate.
>> I would ask pgpool guys about it.
>>
>> regards,
>> Hiroshi Inoue
>
>
> Hi Hiroshi,
>
> DECLARE, FETCH and CLOSE are probably regarded as members of the SELECT family and thus are load balanced. The
problemarises, because the cursors are defined inside BEGIN; ... COMMIT; , and thus are send to all backends, but are
CLOSEdafter the transaction, and thus load balanced, which leaves on cursor defined on one backend. 
>
> The transaction block and handling of cursor is done by the odbc, isn't it?

Hi Andreas,
I told pgpool guys this issue at JPUGCon Yesterday.
I would get a reply in a few days.

regards,
Hiroshi Inoue