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

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

Regards,
Andreas