Skip site navigation (1) Skip section navigation (2)

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

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 (view raw or flat)
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
___________________________________________________________________________

Responses

pgsql-odbc by date

Next:From: Richard BroersmaDate: 2009-11-19 14:49:51
Subject: Re: What the hell is going on?
Previous:From: Hiroshi InoueDate: 2009-11-19 09:18:56
Subject: Re: What the hell is going on?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group