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(at)tpf(dot)co(dot)jp]
>> Gesendet: Donnerstag, 19. November 2009 23:12
>> An: Andreas Gaab
>> Cc: 'pgsql-odbc(at)postgresql(dot)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 problem arises, because the cursors are defined inside BEGIN; ... COMMIT; , and thus are send to all backends, but are CLOSEd after 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