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 [at] scanlab.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
____________________________________________________________ _______________

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

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
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">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1"=
>
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>

</style>

</head>

<body lang=3DDE link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

I
encountered a problem using pgpool with two identical postgres 8.4 servers =
as=A0
backends.<o:p></o:p>

<o:p> </o:p>

Activating
the odbc-setting declare/fetch, our application creates an data mismatch er=
ror.<o:p></o:p>

<o:p> </o:p>

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 a=
fter
the COMMIT, thus are only closed on one server. This leads to data
inconsistency for the next query DECLARING an cursor.<o:p></o:p>

<o:p> </o:p>

Pgpool
log excerpt:<o:p></o:p>

<o:p> </o:p>

2009-11-19
09:50:00 LOG:=A0=A0 pid 16244: statement: BEGIN;declare "SQL_CUR031D30=
F0"
cursor with hold for SELECT * FROM table WHERE resultid =3D 1252644 for rea=
d
only;;fetch 100 in "SQL_CUR031D30F0"<o:p></o:p>

2009-11-19
09:50:00 DEBUG: pid 16244: wait_for_query_response: waiting for backend 0
completing the query<o:p></o:p>

2009-11-19
09:50:00 DEBUG: pid 16244: detect_error: kind: C<o:p></o:p>

2009-11-19
09:50:00 DEBUG: pid 16244: detect_error: kind: C<o:p></o:p>

2009-11-19
09:50:00 DEBUG: pid 16244: detect_error: kind: C<o:p></o:p>

2009-11-19
09:50:00 DEBUG: pid 16244: detect_error: kind: C<o:p></o:p>

2009-11-19
09:50:00 DEBUG: pid 16244: wait_for_query_response: waiting for backend 1
completing the query<o:p></o:p>

2009-11-19
09:50:00 DEBUG: pid 16244: read_kind_from_backend: read kind from 0 th back=
end
C NUM_BACKENDS: 2<o:p></o:p>

2009-11-19
09:50:00 DEBUG: pid 16244: read_kind_from_backend: read kind from 1 th back=
end
C NUM_BACKENDS: 2<o:p></o:p>

…<o:p></o:p>

2009-11-19
09:50:00 LOG:=A0=A0 pid 16244: statement: COMMIT<o:p></o:p>

2009-11-19
09:50:00 DEBUG: pid 16244: wait_for_query_response: waiting for backend 1
completing the query<o:p></o:p>

2009-11-19
09:50:01 DEBUG: pid 16244: wait_for_query_response: waiting for backend 0
completing the query<o:p></o:p>

2009-11-19
09:50:01 DEBUG: pid 16244: detect_error: kind: C<o:p></o:p>

2009-11-19
09:50:01 DEBUG: pid 16244: detect_error: kind: C<o:p></o:p>

2009-11-19
09:50:01 DEBUG: pid 16244: read_kind_from_backend: read kind from 0 th back=
end
C NUM_BACKENDS: 2<o:p></o:p>

2009-11-19
09:50:01 DEBUG: pid 16244: read_kind_from_backend: read kind from 1 th back=
end
C NUM_BACKENDS: 2<o:p></o:p>

…<o:p></o:p>

2009-11-19
09:50:02 LOG:=A0=A0 pid 16244: statement: close "SQL_CUR031D30F0"=
<o:p></o:p>

2009-11-19
09:50:02 DEBUG: pid 16244: wait_for_query_response: waiting for backend 0
completing the query<o:p></o:p>

2009-11-19
09:50:02 DEBUG: pid 16244: detect_error: kind: C<o:p></o:p>

2009-11-19
09:50:02 DEBUG: pid 16244: detect_error: kind: C<o:p></o:p>

2009-11-19
09:50:02 DEBUG: pid 16244: read_kind_from_backend: read kind from 0 th back=
end
C NUM_BACKENDS: 1<o:p></o:p>

2009-11-19
09:50:02 DEBUG: pid 16244: pool_process_query: kind from backend: C<o:p></o=
:p>

<o:p> </o:p>

<o:p> </o:p>

Any
idea other than diabling declare/fetch?<o:p></o:p>

<o:p> </o:p>

Best
regards,<o:p></o:p>

Andreas
Gaab<o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

_____________________=
______________________________________________________<o:p></o:p></p=
>

<o:p> </o:p></sp=
an>

SCANLAB AG<o:p><=
/o:p>

Dr. Andreas =
;Simon Gaab<o:p></o:p>

Entwicklung •
R & D<o:p></o:p>

<o:p> </o:p></sp=
an>

Siemensstr. 2a &=
#8226;
82178 Puchheim • Germany<o:p></o:p>

Tel. +49 (8=
9) 800 746-513
• Fax +49 (89) 800 746-199<o:p></o:p>

<a
href=3D"mailto:a.gaab [at] scanlab.de">mailto:a.gaab [at] scanlab.de</a> • <a
href=3D"http://www.scanlab.de">www.scanlab.de</a><o:p></o:p>

<o:p> </o:p></sp=
an>

Amtsgericht Mü=
nchen:
HRB 124707 • USt-IdNr.: DE 129 456 351<o:p></o:p>=


Vorstand:
Georg Hofner (Sprecher), Christian Huttenloher,
Norbert Petschik<o:p></o:p>

Aufsichtsrat (Vo=
rsitz):
Dr. Hans J. Langer<o:p></o:p>

_____________________=
______________________________________________________<o:p></o:p></=
span>

<o:p> </o:p>

</div>

</body>

</html>

--_000_48DA836F3865C54B8FBF424A3B775AF6DE908683ExchangeServe rs_--

Andreas Gaab [ Do, 19 November 2009 10:36 ] [ ID #2023330 ]

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 [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Hiroshi Inoue [ Do, 19 November 2009 23:11 ] [ ID #2023346 ]

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 [at] tpf.co.jp]
> Gesendet: Donnerstag, 19. November 2009 23:12
> An: Andreas Gaab
> Cc: 'pgsql-odbc [at] 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
>> 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 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

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

Andreas Gaab [ Fr, 20 November 2009 08:55 ] [ ID #2023467 ]

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 [at] tpf.co.jp]
>> Gesendet: Donnerstag, 19. November 2009 23:12
>> An: Andreas Gaab
>> Cc: 'pgsql-odbc [at] 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
>>> 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 curs=
ors
>>> are defined after an BEGIN, thus with load balancing disabled, but ar=
e
>>> CLOSED after the COMMIT, thus are only closed on one server. This lea=
ds
>>> 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 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.
>
> 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

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

Hiroshi Inoue [ So, 22 November 2009 01:21 ] [ ID #2023649 ]

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 [at] tpf.co.jp] Gesendet: Donnerstag, 19.=

>>> November 2009 23:12
>>> An: Andreas Gaab
>>> Cc: 'pgsql-odbc [at] 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=

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

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

Hiroshi Inoue [ Sa, 05 Dezember 2009 00:27 ] [ ID #2024900 ]