executeBatch() issue with new driver?

Hello all,

I'm using pg8 beta 3, with the pgdev.307.jdbc3.jar JDBC driver.

If I run the following example code:

Connection conn = ...;
Statement st = conn.createStatement();
String sql = "create temp table t (a int4); insert into t (a) values (1);";
st.addBatch(sql);
st.executeBatch();

I get the following error:

Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 1
at
org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2317)
at
org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleCommandStatus(AbstractJdbc2Statement.java:2293)
at
org.postgresql.core.v3.QueryExecutorImpl.interpretCommandStatus(QueryExecutorImpl.java:1230)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:968)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:283)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2371)
at com.rentec.fi.db.DbStatement.executeBatch(DbStatement.java:88)

Am I missing something here?

Thanks!

-- Alan

Without looking at the code, I noticed that the javadoc's refer to
addBatch as adding a single command, see below.

Adds the given SQL command to the current list of commmands for this
|Statement| object. The commands in this list can be executed as a batch
by calling the method |executeBatch|.

Dave
Alan Stange wrote:

> Hello all,
>
> I'm using pg8 beta 3, with the pgdev.307.jdbc3.jar JDBC driver.
>
>
> If I run the following example code:
>
> Connection conn = ...;
> Statement st = conn.createStatement();
> String sql = "create temp table t (a int4); insert into t (a) values
> (1);";
> st.addBatch(sql);
> st.executeBatch();
>
> I get the following error:
>
> Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 1
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2317)
>
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleCommandStatus(AbstractJdbc2Statement.java:2293)
>
> at
> org.postgresql.core.v3.QueryExecutorImpl.interpretCommandStatus(QueryExecutorImpl.java:1230)
>
> at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:968)
>
> at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:283)
>
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2371)
>
> at com.rentec.fi.db.DbStatement.executeBatch(DbStatement.java:88)
>
>
> Am I missing something here?
>
> Thanks!
>
> -- Alan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

Dave Cramer wrote:

> Without looking at the code, I noticed that the javadoc's refer to
> addBatch as adding a single command, see below.
>
> Adds the given SQL command to the current list of commmands for this
> |Statement| object. The commands in this list can be executed as a
> batch by calling the method |executeBatch|.

I see.

This appears to be a change in behavior from the pg74 drivers, which do
accept more than a single statement. The MS SQLServer JDBC driver
also accepts more than one statement.

I was leaning toward dropping our use of batches anyway. Thanks.

-- Alan

> Alan Stange wrote:
>
>> Hello all,
>>
>> I'm using pg8 beta 3, with the pgdev.307.jdbc3.jar JDBC driver.
>>
>>
>> If I run the following example code:
>>
>> Connection conn = ...;
>> Statement st = conn.createStatement();
>> String sql = "create temp table t (a int4); insert into t (a) values
>> (1);";
>> st.addBatch(sql);
>> st.executeBatch();
>>
>> I get the following error:
>>
>> Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 1
>> at
>> org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2317)
>>
>> at
>> org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleCommandStatus(AbstractJdbc2Statement.java:2293)
>>
>> at
>> org.postgresql.core.v3.QueryExecutorImpl.interpretCommandStatus(QueryExecutorImpl.java:1230)
>>
>> at
>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:968)
>>
>> at
>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:283)
>>
>> at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2371)
>>
>> at com.rentec.fi.db.DbStatement.executeBatch(DbStatement.java:88)
>>
>>
>> Am I missing something here?
>>
>> Thanks!
>>
>> -- Alan
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>>
>>
>

Well, I'm not suggesting that this is the intent of the driver, just
pointing out that there is an inference in the documentation that the
API may not be intended to be used that way.

Dave

Alan Stange wrote:

> Dave Cramer wrote:
>
>> Without looking at the code, I noticed that the javadoc's refer to
>> addBatch as adding a single command, see below.
>>
>> Adds the given SQL command to the current list of commmands for this
>> |Statement| object. The commands in this list can be executed as a
>> batch by calling the method |executeBatch|.
>
>
> I see.
> This appears to be a change in behavior from the pg74 drivers, which
> do accept more than a single statement. The MS SQLServer JDBC
> driver also accepts more than one statement.
>
> I was leaning toward dropping our use of batches anyway. Thanks.
>
> -- Alan
>
>
>> Alan Stange wrote:
>>
>>> Hello all,
>>>
>>> I'm using pg8 beta 3, with the pgdev.307.jdbc3.jar JDBC driver.
>>>
>>>
>>> If I run the following example code:
>>>
>>> Connection conn = ...;
>>> Statement st = conn.createStatement();
>>> String sql = "create temp table t (a int4); insert into t (a) values
>>> (1);";
>>> st.addBatch(sql);
>>> st.executeBatch();
>>>
>>> I get the following error:
>>>
>>> Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 1
>>> at
>>> org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2317)
>>>
>>> at
>>> org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleCommandStatus(AbstractJdbc2Statement.java:2293)
>>>
>>> at
>>> org.postgresql.core.v3.QueryExecutorImpl.interpretCommandStatus(QueryExecutorImpl.java:1230)
>>>
>>> at
>>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:968)
>>>
>>> at
>>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:283)
>>>
>>> at
>>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2371)
>>>
>>> at com.rentec.fi.db.DbStatement.executeBatch(DbStatement.java:88)
>>>
>>>
>>> Am I missing something here?
>>>
>>> Thanks!
>>>
>>> -- Alan
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 2: you can get off all lists at once with the unregister command
>>> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>>>
>>>
>>
>
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

Dave Cramer wrote:

> Well, I'm not suggesting that this is the intent of the driver, just
> pointing out that there is an inference in the documentation that the
> API may not be intended to be used that way.

I understand.

Thanks!

-- Alan

> Alan Stange wrote:
>
>> Dave Cramer wrote:
>>
>>> Without looking at the code, I noticed that the javadoc's refer to
>>> addBatch as adding a single command, see below.
>>>
>>> Adds the given SQL command to the current list of commmands for this
>>> |Statement| object. The commands in this list can be executed as a
>>> batch by calling the method |executeBatch|.
>>
>>
>>
>> I see.
>> This appears to be a change in behavior from the pg74 drivers, which
>> do accept more than a single statement. The MS SQLServer JDBC
>> driver also accepts more than one statement.
>>
>> I was leaning toward dropping our use of batches anyway. Thanks.
>>
>> -- Alan
>>
>>
>>> Alan Stange wrote:
>>>
>>>> Hello all,
>>>>
>>>> I'm using pg8 beta 3, with the pgdev.307.jdbc3.jar JDBC driver.
>>>>
>>>>
>>>> If I run the following example code:
>>>>
>>>> Connection conn = ...;
>>>> Statement st = conn.createStatement();
>>>> String sql = "create temp table t (a int4); insert into t (a)
>>>> values (1);";
>>>> st.addBatch(sql);
>>>> st.executeBatch();
>>>>
>>>> I get the following error:
>>>>
>>>> Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 1
>>>> at
>>>> org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2317)
>>>>
>>>> at
>>>> org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleCommandStatus(AbstractJdbc2Statement.java:2293)
>>>>
>>>> at
>>>> org.postgresql.core.v3.QueryExecutorImpl.interpretCommandStatus(QueryExecutorImpl.java:1230)
>>>>
>>>> at
>>>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:968)
>>>>
>>>> at
>>>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:283)
>>>>
>>>> at
>>>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2371)
>>>>
>>>> at com.rentec.fi.db.DbStatement.executeBatch(DbStatement.java:88)
>>>>
>>>>
>>>> Am I missing something here?
>>>>
>>>> Thanks!
>>>>
>>>> -- Alan
>>>>
>>>> ---------------------------(end of
>>>> broadcast)---------------------------
>>>> TIP 2: you can get off all lists at once with the unregister command
>>>> (send "unregister YourEmailAddressHere" to
>>>> majordomo(at)postgresql(dot)org)
>>>>
>>>>
>>>
>>
>>
>>
>

On Tue, 2 Nov 2004, Alan Stange wrote:

> Connection conn = ...;
> Statement st = conn.createStatement();
> String sql = "create temp table t (a int4); insert into t (a) values (1);";
> st.addBatch(sql);
> st.executeBatch();
>

This is something (as discussed with Dave) that you shouldn't be doing,
but you certainly shouldn't get an ArrayIndexOutOfBoundsException. I've
fixed the driver to return the desired SQLException reporting "Too many
results were returned."

Kris Jurka

On Tue, 2 Nov 2004, Alan Stange wrote:

> >This is something (as discussed with Dave) that you shouldn't be doing,
> >
> Let me be devils advocate for a second and ask "why not"? I don't
> disagree, but I'm not sure I understand all the issues here.
>

The real problem is that executeBatch() returns an int[] of update counts.
What should we return for an entry like addBatch("INSERT ... ; INSERT
..."); ? Two individual entries? The sum of the update counts? The
javadoc for executeBatch isn't entirely clear, but certainly seems to make
the assumption that one batch entry is one command. Especially since you
are already using addBatch, there doesn't seem to be much point in jamming
two commands into one batch.

Kris Jurka

Kris Jurka wrote:

>On Tue, 2 Nov 2004, Alan Stange wrote:
>
>
>
>>>This is something (as discussed with Dave) that you shouldn't be doing,
>>>
>>>
>>>
>>Let me be devils advocate for a second and ask "why not"? I don't
>>disagree, but I'm not sure I understand all the issues here.
>>
>>
>>
>
>The real problem is that executeBatch() returns an int[] of update counts.
>What should we return for an entry like addBatch("INSERT ... ; INSERT
>..."); ? Two individual entries? The sum of the update counts? The
>javadoc for executeBatch isn't entirely clear, but certainly seems to make
>the assumption that one batch entry is one command.
>
This was what I was thinking. What does the pg74 driver do in this
case as it does allow multiple statements in each batch?

> Especially since you
>are already using addBatch, there doesn't seem to be much point in jamming
>two commands into one batch.
>
>
Network latency. We were able to greatly increase performance this way
by reducing the number of round trips.

-- Alan

On Tue, 2 Nov 2004, Alan Stange wrote:

> >The real problem is that executeBatch() returns an int[] of update counts.
> >
> This was what I was thinking. What does the pg74 driver do in this
> case as it does allow multiple statements in each batch?
>

The 8.0 driver uses the extended query protocol while the 7.4 driver
uses the simple query protocol. The relevent difference in this case is
that you can only send one command per query in the extended query
protocol while you can string them together with ";" in the simple case.
This means the 8.0 driver splits the queries and issues them individually
and gets update counts for each command. The 7.4 driver issues them
together and only gets one update count (for the last piece of the batch).

> Network latency. We were able to greatly increase performance this way
> by reducing the number of round trips.
>

Well in the 8.0 case you've lost that anyway because it splits them and
issues them separately. Although the 8.0 driver does have some other
advantages in that it doesn't issue a Sync after each individual query,
but sends out a number of commands and then processes a number of results,
so the number of round trips will be reduced, but in a different fashion.

Kris Jurka

Kris Jurka wrote:

>On Tue, 2 Nov 2004, Alan Stange wrote:
>
>
>
>>>The real problem is that executeBatch() returns an int[] of update counts.
>>>
>>>
>>>
>>This was what I was thinking. What does the pg74 driver do in this
>>case as it does allow multiple statements in each batch?
>>
>>
>>
>
>The 8.0 driver uses the extended query protocol while the 7.4 driver
>uses the simple query protocol. The relevent difference in this case is
>that you can only send one command per query in the extended query
>protocol while you can string them together with ";" in the simple case.
>This means the 8.0 driver splits the queries and issues them individually
>and gets update counts for each command. The 7.4 driver issues them
>together and only gets one update count (for the last piece of the batch).
>
>
>
>>Network latency. We were able to greatly increase performance this way
>>by reducing the number of round trips.
>>
>>
>>
>
>Well in the 8.0 case you've lost that anyway because it splits them and
>issues them separately. Although the 8.0 driver does have some other
>advantages in that it doesn't issue a Sync after each individual query,
>but sends out a number of commands and then processes a number of results,
>so the number of round trips will be reduced, but in a different fashion.
>
>
What about this case:

Connection conn = ...
Statement st = conn.createStatement();
st.execute("insert a; insert b; insert c; insert d;");

Is the 8.0 driver busting this up into 4 trips to the database?!

-- Alan

Alan Stange wrote:

>> Especially since you are already using addBatch, there doesn't seem to
>> be much point in jamming two commands into one batch.
>>
> Network latency. We were able to greatly increase performance this way
> by reducing the number of round trips.

If you give the development driver multiple queries via *multiple*
addBatch() calls, it will avoid the extra round trips.

i.e. if you do:

stmt.addBatch("CREATE TABLE ...");
stmt.addBatch("INSERT ...");
stmt.executeBatch();

then the driver will only do one round trip.

There is a limit of somewhere around 100 queries per round-trip (from
memory) due to some issues with avoiding network deadlocks, but in
practice that won't have much effect on performance.

AFAIK this is exactly what addBatch/executeBatch is there for. It's just
that the older driver was not particularly smart about handling this
case, so you had to shoehorn multiple statements into one query to get
the same effect.

Both driver versions should handle multiple queries per query string if
you use the normal query execution interface rather than the batch
interface. The older driver doesn't handle multiple resultsets per
query, but multiple updates (or other queries that do not return
results) should be fine.

-O

Alan Stange wrote:

> What about this case:
>
> Connection conn = ...
> Statement st = conn.createStatement();
> st.execute("insert a; insert b; insert c; insert d;");
>
> Is the 8.0 driver busting this up into 4 trips to the database?!

No, but it must split that query string into 4 individual queries (a V3
extended-query-protocol requirement). So it will send:

Parse("insert a")
Bind(...)
Execute(...)
Parse("insert b")
Bind(...)
Execute(...)
Parse("insert c")
Bind(...)
Execute(...)
Parse("insert d")
Bind(...)
Execute(...)
Sync

Then it starts reading responses from the server.

The driver does exactly the same thing if you do:

st.addBatch("insert a");
st.addBatch("insert b");
st.addBatch("insert c");
st.addBatch("insert d");
st.executeBatch();

If you're interested in the gory protocol details, take a look at
http://www.postgresql.org/docs/7.4/static/protocol-flow.html#AEN52666

-O

Oliver Jowett wrote:

> Alan Stange wrote:
>
>> And the only reason we stuck with batch's in this case was because we
>> had a temp table and we needed to stay in the scope of that table
>> name, hence the single statement with multiple batches of multiple
>> statements.
>
>
> I'm not sure what you mean here. Are you trying to use CREATE
> TEMPORARY TABLE .. ON COMMIT DROP with autocommit on?

We were "conserving bandwidth" using a temp table, inserting a condensed
form of the data, along the lines of the following:

CREATE TEMP TABLE T(...);
INSERT INTO T ...
lots of these...
INSERT INTO T...
INSERT INTO BIGTABLE(LONGCOLNAME1,LONGCOLNAME2) SELECT A,B,"common
value" from T;
DROP TABLE T;

>
>> The new pgjdbc driver does support multiple result sets?
>
> Yes.

Thanks!

-- Alan