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

Hi Andreas,

Sorry for the delay.

The recent pgpool-II 2.2.6 release seems to contain the bug fix.
Thanks to pgpool team.

regards,
Hiroshi Inoue

Hiroshi Inoue wrote:
> 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