From: | Andreas Gaab <A(dot)Gaab(at)scanlab(dot)de> |
---|---|
To: | "'pgsql-odbc(at)postgresql(dot)org'" <pgsql-odbc(at)postgresql(dot)org> |
Subject: | declare/fetch problem with pgpool 2.2.3; Postgres 8.4; ODBC 8.03.04.00 |
Date: | 2009-11-19 09:36:40 |
Message-ID: | 48DA836F3865C54B8FBF424A3B775AF6DE908683@Exchange-Server.scanlab-intern.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-odbc |
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
___________________________________________________________________________
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Broersma | 2009-11-19 14:49:51 | Re: What the hell is going on? |
Previous Message | Hiroshi Inoue | 2009-11-19 09:18:56 | Re: What the hell is going on? |