Re: issues with SQL size st.execute(SQL) in 8.3.3

Hi,
i am experiencing the following situation with 8.3.3 backend and postgresql-8.3-603.jdbc3.jar driver.
In a simple program such as shown below i try to execute a rather large SQL worth of 2.5 MBytes:
.....
FileReader fr = new FileReader(file);
BufferedReader br = new BufferedReader(fr);
String SQL="";
String line;
while ((line=br.readLine()) != null) {
SQL += line;
}
Statement st = con.createStatement();
st.execute(SQL);
.......

i noticed that when i run the above program against postgresql 7.4.2 (with the contrib stock jdbc driver) the above
program executes and ends successfully.
However when i try to run it against 8.3.3 server and postgresql-8.3-603.jdbc3.jar driver, it seems to stall at a statement
which is located somewhere around 1.5 MByte offset. If i split the SQL file into two parts 1.5Mbytes and 1Mbyte respectively then
the above program runs fine.
Experimenting i found out that a file of 1623377 bytes would rather be executed (i think it is not deterministic when it
comes to file sizes), while a file of 1635008 would stall.
Can somebody think of anything about this (change of) behaviour?
Any way to by pass this in 8.3 without resorting to drastically change one's architecture?

Thanx

--
Achilleas Mantzios

On Thu, 16 Oct 2008, Achilleas Mantzios wrote:

> i am experiencing the following situation with 8.3.3 backend and
> postgresql-8.3-603.jdbc3.jar driver. In a simple program such as shown
> below i try to execute a rather large SQL worth of 2.5 MBytes:
>
> i noticed that when i run the above program against postgresql 7.4.2
> (with the contrib stock jdbc driver) the above program executes and ends
> successfully. However when i try to run it against 8.3.3 server and
> postgresql-8.3-603.jdbc3.jar driver, it seems to stall at a statement
> which is located somewhere around 1.5 MByte offset. If i split the SQL
> file into two parts 1.5Mbytes and 1Mbyte respectively then the above
> program runs fine. Experimenting i found out that a file of 1623377
> bytes would rather be executed (i think it is not deterministic when it
> comes to file sizes), while a file of 1635008 would stall. Can somebody
> think of anything about this (change of) behaviour? Any way to by pass
> this in 8.3 without resorting to drastically change one's architecture?
>

See this report on -bugs which describes the problem in detail.

http://archives.postgresql.org/pgsql-bugs/2008-05/msg00132.php

I haven't looked into a fix, but this is what's going on.

Kris Jurka

Στις Thursday 16 October 2008 22:04:33 ο/η Kris Jurka έγραψε:
>

>
> See this report on -bugs which describes the problem in detail.
>
> http://archives.postgresql.org/pgsql-bugs/2008-05/msg00132.php
>
> I haven't looked into a fix, but this is what's going on.

Ahh, i see.
Its a classic problematic situation found in many contexts in computing life.
We are potentially affected by this bug, although we havn't had any such incident yet.
How would you advise to try and solve the problem? Maybe dig in the code and arrange
for a temporary fix that suits us?
Thanx
>
> Kris Jurka
>

--
Achilleas Mantzios

Στις Friday 17 October 2008 10:04:09 ο/η Achilleas Mantzios έγραψε:
> Στις Thursday 16 October 2008 22:04:33 ο/η Kris Jurka έγραψε:
> >
>
> >
> > See this report on -bugs which describes the problem in detail.
> >
> > http://archives.postgresql.org/pgsql-bugs/2008-05/msg00132.php
> >
> > I haven't looked into a fix, but this is what's going on.
>
> Ahh, i see.
> Its a classic problematic situation found in many contexts in computing life.
> We are potentially affected by this bug, although we havn't had any such incident yet.
> How would you advise to try and solve the problem? Maybe dig in the code and arrange
> for a temporary fix that suits us?
> Thanx

As a quick and dirty workaround, i tried setting protocolVersion=2 and it worked.
Can you see any potential side effects about it as a temporary solution to the problem?

> >
> > Kris Jurka
> >
>
>
>
> --
> Achilleas Mantzios
>

--
Achilleas Mantzios

On Fri, 17 Oct 2008, Achilleas Mantzios wrote:

> As a quick and dirty workaround, i tried setting protocolVersion=2 and
> it worked. Can you see any potential side effects about it as a
> temporary solution to the problem?

It all depends on whether you are relying on any V3 features in other
places. I have a fix for the issue (attached). I'm still testing it and
will hopefully apply it this weekend.

Kris Jurka

Στις Friday 17 October 2008 20:34:57 ο/η Kris Jurka έγραψε:
>
> On Fri, 17 Oct 2008, Achilleas Mantzios wrote:
>
> > As a quick and dirty workaround, i tried setting protocolVersion=2 and
> > it worked. Can you see any potential side effects about it as a
> > temporary solution to the problem?
>
> It all depends on whether you are relying on any V3 features in other
> places. I have a fix for the issue (attached). I'm still testing it and
> will hopefully apply it this weekend.

Regarding V2, its just a simple program like the one i showed,
can you point some cases which would explicitly rely on V3 features ?
Which JDBC method calls would be affected by the enforcement of V2?
Can the rest of the backends (other than the one serving the v2 client) be
affected in any way by this?
>
> Kris Jurka

--
Achilleas Mantzios

HI All,
Can we call a psotgres procedure through JAVA Csllable statemnt
Thanks and Regards,
Ramasubramanian.G|Software Engineer - Delivery
E-mail:ramasubramanian(dot)g(at)renaissance-it(dot)com | Extn: 1607
Sobha Renaissance Information Technology (P) Ltd.
An SEI-CMM, P-CMM & SSE-CMM Level 5 Company
BS ISO/IEC 27001:2005 & ISO 9001:2000 Certified

A Top 50 Fast Growing Technology Company (Deloitte, 2006 & 2007) |
A Six Sigma Practice Company
Phone: + 91 80 41951999 |
Fax: + 91 80 41523300 A Top 50 Fast Growing Technology Company (Deloitte,
2006 & 2007) |
A Six Sigma Practice Company
Phone: + 91 80 41951999 |
Fax: + 91 80 41523300

URL: www.renaissance-it.com |
Video Conference: + 91 80 41252222
?
----- Original Message -----
From: "Achilleas Mantzios" <achill(at)matrix(dot)gatewaynet(dot)com>
To: <pgsql-jdbc(at)postgresql(dot)org>
Cc: "Kris Jurka" <books(at)ejurka(dot)com>
Sent: Saturday, October 18, 2008 11:03 AM
Subject: Re: [JDBC] issues with SQL size st.execute(SQL) in 8.3.3

Στις Friday 17 October 2008 20:34:57 ο/η Kris Jurka έγραψε:
>
> On Fri, 17 Oct 2008, Achilleas Mantzios wrote:
>
> > As a quick and dirty workaround, i tried setting protocolVersion=2 and
> > it worked. Can you see any potential side effects about it as a
> > temporary solution to the problem?
>
> It all depends on whether you are relying on any V3 features in other
> places. I have a fix for the issue (attached). I'm still testing it and
> will hopefully apply it this weekend.

Regarding V2, its just a simple program like the one i showed,
can you point some cases which would explicitly rely on V3 features ?
Which JDBC method calls would be affected by the enforcement of V2?
Can the rest of the backends (other than the one serving the v2 client) be
affected in any way by this?
>
> Kris Jurka

--
Achilleas Mantzios

On Sat, 18 Oct 2008, Ramasubramanian wrote:

> Can we call a psotgres procedure through JAVA Csllable statemnt

Please start a new thread for a new question rather than just replying to
a previous email.

Yes, you can call a procedure from a CallableStatement. See the
documentation here:

http://jdbc.postgresql.org/documentation/83/callproc.html

Kris Jurka

On Sat, 18 Oct 2008, Achilleas Mantzios wrote:

> Friday 17 October 2008 20:34:57 / Kris Jurka :
>>
>> It all depends on whether you are relying on any V3 features in other
>> places. I have a fix for the issue (attached). I'm still testing it and
>> will hopefully apply it this weekend.
>
> Regarding V2, its just a simple program like the one i showed,
> can you point some cases which would explicitly rely on V3 features ?
> Which JDBC method calls would be affected by the enforcement of V2?
> Can the rest of the backends (other than the one serving the v2 client) be
> affected in any way by this?

If it's just this connection, then switching to V2 is a fine solution. A
simple execute works fine and won't affect anything else. I thought you
were considering switching your whole application to V2 which needs a
little more thought. As an example ParameterMetaData is only
implementable with the V3 protocol.

I have applied to previous patch to CVS, and it should fix your problem,
but it does not solve every deadlock situation. The deadlock avoidance
code was originally written for batch execution which should not be
returning ResultSets. If many long queries that return significant
results are issued with a single execute we will still deadlock. Solving
this would require tracking the bytes actually sent rather than the number
of queries sent, or a more significant redesign to use multiple threads or
NIO. Attached is sample code which still deadlocks.

Kris Jurka

Στις Saturday 18 October 2008 16:48:12 ο/η Kris Jurka έγραψε:
>
> On Sat, 18 Oct 2008, Achilleas Mantzios wrote:
>
> > Στις Friday 17 October 2008 20:34:57 ο/η Kris Jurka έγραψε:
> >>
> >> It all depends on whether you are relying on any V3 features in other
> >> places. I have a fix for the issue (attached). I'm still testing it and
> >> will hopefully apply it this weekend.
> >
> > Regarding V2, its just a simple program like the one i showed,
> > can you point some cases which would explicitly rely on V3 features ?
> > Which JDBC method calls would be affected by the enforcement of V2?
> > Can the rest of the backends (other than the one serving the v2 client) be
> > affected in any way by this?
>
> If it's just this connection, then switching to V2 is a fine solution. A
> simple execute works fine and won't affect anything else. I thought you
> were considering switching your whole application to V2 which needs a
> little more thought. As an example ParameterMetaData is only
> implementable with the V3 protocol.
>
> I have applied to previous patch to CVS, and it should fix your problem,
> but it does not solve every deadlock situation. The deadlock avoidance
> code was originally written for batch execution which should not be
> returning ResultSets. If many long queries that return significant
> results are issued with a single execute we will still deadlock. Solving
> this would require tracking the bytes actually sent rather than the number
> of queries sent, or a more significant redesign to use multiple threads or
> NIO. Attached is sample code which still deadlocks.

Kris,
many thanx for the explanation of the problem and the patch.

>
> Kris Jurka

--
Achilleas Mantzios