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

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.

Pgpool log excerpt:

2009-11-19 09:50:00 LOG: pid 16244: statement: BEGIN;declare "SQL_CUR031D30F0" cursor with hold for SELECT * FROM table WHERE resultid = 1252644 for read only;;fetch 100 in "SQL_CUR031D30F0"
2009-11-19 09:50:00 DEBUG: pid 16244: wait_for_query_response: waiting for backend 0 completing the query
2009-11-19 09:50:00 DEBUG: pid 16244: detect_error: kind: C
2009-11-19 09:50:00 DEBUG: pid 16244: detect_error: kind: C
2009-11-19 09:50:00 DEBUG: pid 16244: detect_error: kind: C
2009-11-19 09:50:00 DEBUG: pid 16244: detect_error: kind: C
2009-11-19 09:50:00 DEBUG: pid 16244: wait_for_query_response: waiting for backend 1 completing the query
2009-11-19 09:50:00 DEBUG: pid 16244: read_kind_from_backend: read kind from 0 th backend C NUM_BACKENDS: 2
2009-11-19 09:50:00 DEBUG: pid 16244: read_kind_from_backend: read kind from 1 th backend C NUM_BACKENDS: 2
...
2009-11-19 09:50:00 LOG: pid 16244: statement: COMMIT
2009-11-19 09:50:00 DEBUG: pid 16244: wait_for_query_response: waiting for backend 1 completing the query
2009-11-19 09:50:01 DEBUG: pid 16244: wait_for_query_response: waiting for backend 0 completing the query
2009-11-19 09:50:01 DEBUG: pid 16244: detect_error: kind: C
2009-11-19 09:50:01 DEBUG: pid 16244: detect_error: kind: C
2009-11-19 09:50:01 DEBUG: pid 16244: read_kind_from_backend: read kind from 0 th backend C NUM_BACKENDS: 2
2009-11-19 09:50:01 DEBUG: pid 16244: read_kind_from_backend: read kind from 1 th backend C NUM_BACKENDS: 2
...
2009-11-19 09:50:02 LOG: pid 16244: statement: close "SQL_CUR031D30F0"
2009-11-19 09:50:02 DEBUG: pid 16244: wait_for_query_response: waiting for backend 0 completing the query
2009-11-19 09:50:02 DEBUG: pid 16244: detect_error: kind: C
2009-11-19 09:50:02 DEBUG: pid 16244: detect_error: kind: C
2009-11-19 09:50:02 DEBUG: pid 16244: read_kind_from_backend: read kind from 0 th backend C NUM_BACKENDS: 1
2009-11-19 09:50:02 DEBUG: pid 16244: pool_process_query: kind from backend: C

Any idea other than diabling declare/fetch?

Best regards,
Andreas Gaab

___________________________________________________________________________

SCANLAB AG
Dr. Andreas Simon Gaab
Entwicklung * R & D

Siemensstr. 2a * 82178 Puchheim * Germany
Tel. +49 (89) 800 746-513 * Fax +49 (89) 800 746-199
mailto:a(dot)gaab(at)scanlab(dot)de * www.scanlab.de<http://www.scanlab.de>

Amtsgericht München: HRB 124707 * USt-IdNr.: DE 129 456 351
Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik
Aufsichtsrat (Vorsitz): Dr. Hans J. Langer
___________________________________________________________________________

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

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

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

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