declare/fetch problem with pgpool 2.2.3; Postgres 8.4; ODBC8.03.04.00

--_000_48DA836F3865C54B8FBF424A3B775AF6DE908683ExchangeServe rs_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I encountered a problem using pgpool with two identical postgres 8.4 server=
s 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 ar=
e 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 i=
nconsistency for the next query DECLARING an cursor.

Pgpool log excerpt:

2009-11-19 09:50:00 LOG: pid 16244: statement: BEGIN;declare "SQL_CUR031D=
30F0" cursor with hold for SELECT * FROM table WHERE resultid =3D 1252644 f=
or 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 fro=
m 0 th backend C NUM_BACKENDS: 2
2009-11-19 09:50:00 DEBUG: pid 16244: read_kind_from_backend: read kind fro=
m 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 fro=
m 0 th backend C NUM_BACKENDS: 2
2009-11-19 09:50:01 DEBUG: pid 16244: read_kind_from_backend: read kind fro=
m 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 fro=
m 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
____________________________________________________________ _______________


--_000_48DA836F3865C54B8FBF424A3B775AF6DE908683ExchangeServe rs_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">


>

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

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

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@tpf.co.jp]=20
> Gesendet: Donnerstag, 19. November 2009 23:12
> An: Andreas Gaab
> Cc: 'pgsql-odbc@postgresql.org'
> Betreff: Re: [ODBC] declare/fetch problem with pgpool 2.2.3; Postgres 8.4=
; ODBC 8.03.04.00
>
> Hi Andreas,
>=20
> Andreas Gaab wrote:
>> I encountered a problem using pgpool with two identical postgres 8.4=20
>> servers as backends.>
>>=20
>> Activating the odbc-setting declare/fetch, our application creates an=20
>> data mismatch error.
>>=20
>> As far as I can track down the problem, it is caused because the cursors=
=20
>> are defined after an BEGIN, thus with load balancing disabled, but are=
=20
>> CLOSED after the COMMIT, thus are only closed on one server. This leads=
=20
>> 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 fam=
ily 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


--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

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

Andreas Gaab wrote:
>> -----Ursprüngliche Nachricht-----
>> Von: Hiroshi Inoue [mailto:inoue@tpf.co.jp]=20
>> Gesendet: Donnerstag, 19. November 2009 23:12
>> An: Andreas Gaab
>> Cc: 'pgsql-odbc@postgresql.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=20
>>> servers as backends.> =20
>>>
>>> Activating the odbc-setting declare/fetch, our application creates an=
=20
>>> data mismatch error. =20
>>>
>>> As far as I can track down the problem, it is caused because the curs=
ors=20
>>> are defined after an BEGIN, thus with load balancing disabled, but ar=
e=20
>>> CLOSED after the COMMIT, thus are only closed on one server. This lea=
ds=20
>>> 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
>=20
>=20
> Hi Hiroshi,
>=20
> DECLARE, FETCH and CLOSE are probably regarded as members of the SELECT=
family and thus are load balanced. The problem arises, because the curso=
rs are defined inside BEGIN; ... COMMIT; , and thus are send to all backe=
nds, but are CLOSEd after the transaction, and thus load balanced, which =
leaves on cursor defined on one backend.
>=20
> 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


--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

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

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@tpf.co.jp] Gesendet: Donnerstag, 19.=
=20
>>> November 2009 23:12
>>> An: Andreas Gaab
>>> Cc: 'pgsql-odbc@postgresql.org'
>>> Betreff: Re: [ODBC] declare/fetch problem with pgpool 2.2.3; Postgres=
=20
>>> 8.4; ODBC 8.03.04.00
>>>
>>> Hi Andreas,
>>>
>>> Andreas Gaab wrote:
>>>> I encountered a problem using pgpool with two identical postgres 8.4=
=20
>>>> servers as backends.>=20
>>>> Activating the odbc-setting declare/fetch, our application creates=20
>>>> an data mismatch error.=20
>>>> As far as I can track down the problem, it is caused because the=20
>>>> cursors are defined after an BEGIN, thus with load balancing=20
>>>> disabled, but are CLOSED after the COMMIT, thus are only closed on=20
>>>> one server. This leads to data inconsistency for the next query=20
>>>> 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=20
>> SELECT family and thus are load balanced. The problem arises, because=20
>> the cursors are defined inside BEGIN; ... COMMIT; , and thus are send=20
>> to all backends, but are CLOSEd after the transaction, and thus load=20
>> balanced, which leaves on cursor defined on one backend.
>>
>> The transaction block and handling of cursor is done by the odbc,=20
>> isn't it?
>=20
> Hi Andreas,
> I told pgpool guys this issue at JPUGCon Yesterday.
> I would get a reply in a few days.
>=20
> regards,
> Hiroshi Inoue

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc