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.gaab@scanlab.de • 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

___________________________________________________________________________