SEO

hi

i have a big problem.

task: i need to execute multiple queries form some files. each file contains at about 50-100 query. the queries are from create table to create function ... almost everything. this batches should run on more than 600 databases.

my problem is:

i used the Statement.addBatch() to add the batch to the statement, and i execute batch with the Statement.executeBatch() method. the execution failes with the following error:


org.postgresql.util.PSQLException: Too many update results were returned.
at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchR esultHandler.handleCommandStatus(AbstractJdbc2Stat ement.java:2468)
at org.postgresql.core.v3.QueryExecutorImpl.interpret CommandStatus(QueryExecutorImpl.java:1559)
at org.postgresql.core.v3.QueryExecutorImpl.processRe sults(QueryExecutorImpl.java:1274)
at org.postgresql.core.v3.QueryExecutorImpl.execute(Q ueryExecutorImpl.java:347)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execut eBatch(AbstractJdbc2Statement.java:2559)
at com.xxx.db.Database.executeUpdater(Database.java:x y)
at com.taylor.zeus.patcher.db.DatabaseSelector.upgrad eDatabases(DatabaseSelector.java:xy)
at com.xxx.Patcher.start(Patcher.java:xy)
at com.xxx.Application.start(Application.java:xy)
at com.xxx.Application.main(Application.java:xy)

i goggled all over the web to find out what is the source of this exception, but i don't find anything.

can someone explain to me what should i do. or at least what is the problem with this.

platform: linux with 2.6.20-16-generic kernel
db: PostgreSQL 8.1.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-13)
jdbc: 8.1 jdbc3 (i don't know what build number is)

regards

Laszlo-Robert, Albert



---------------------------------
Building a website is a piece of cake.
Yahoo! Small Business gives you all the tools to get online.

Albert Laszlo-Robert wrote:

> org.postgresql.util.PSQLException: Too many update results were returned.

At a guess maybe you are adding statements to the batch that are
actually semicolon-separated statements? I don't know if the batch code
understands that.

-O

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Oliver Jowett wrote:
> Albert Laszlo-Robert wrote:
>
>> org.postgresql.util.PSQLException: Too many update results were
>> returned.
>
> At a guess maybe you are adding statements to the batch that are
> actually semicolon-separated statements? I don't know if the batch
> code understands that.
>
> -O
>
yes, the statements are semicolon-separated. than you have some idea how
to make it to work?

note: if i use a simple executeUpdate for multiple inserts that are
semicolon-separated, than they work.

albertlr


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Albert László-Róbert wrote:

> yes, the statements are semicolon-separated. than you have some idea how
> to make it to work?

Try adding only one statement per addBatch() i.e. instead of

stmt.addBatch("insert a; insert b; insert c");

do:

stmt.addBatch("insert a");
stmt.addBatch("insert b");
stmt.addBatch("insert c");

> note: if i use a simple executeUpdate for multiple inserts that are
> semicolon-separated, than they work.

Yes, executeUpdate is a different code path that does understand
semicolon-separated paths.

The batch code expects exactly one command status per addBatch(). It's
not clear from the JDBC API what you're meant to do with an addBatch()
call that returns more than one thing, since executeBatch() is meant to
return one array element (representing the update status) per addBatch()
call AFAIK.

-O

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Oliver Jowett wrote:

> semicolon-separated paths.

err, statements not paths (I need more coffee)

-O

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Oliver Jowett wrote:
> Albert László-Róbert wrote:
>
>> yes, the statements are semicolon-separated. than you have some idea how
>> to make it to work?
>
> Try adding only one statement per addBatch() i.e. instead of
>
> stmt.addBatch("insert a; insert b; insert c");
>
> do:
>
> stmt.addBatch("insert a");
> stmt.addBatch("insert b");
> stmt.addBatch("insert c");
i wish i can do that. the files that contains those statements are up to
500k ... and the majority of the code contain function implementations.

exists some software that can extract these statements?
>
>> note: if i use a simple executeUpdate for multiple inserts that are
>> semicolon-separated, than they work.
>
> Yes, executeUpdate is a different code path that does understand
> semicolon-separated paths.
>
> The batch code expects exactly one command status per addBatch(). It's
> not clear from the JDBC API what you're meant to do with an addBatch()
> call that returns more than one thing, since executeBatch() is meant
> to return one array element (representing the update status) per
> addBatch() call AFAIK.
>
> -O
>

albertlr

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Albert László-Róbert wrote:

> i wish i can do that. the files that contains those statements are up to
> 500k ... and the majority of the code contain function implementations.

Err ok if you're just reading from a file and giving it to the driver
without any (or little) parsing, why exactly are you using batch updates
in the first place? Can't you just use plain execute()?

-O

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Oliver Jowett wrote:
> Albert László-Róbert wrote:
>
>> i wish i can do that. the files that contains those statements are up to
>> 500k ... and the majority of the code contain function implementations.
>
> Err ok if you're just reading from a file and giving it to the driver
> without any (or little) parsing, why exactly are you using batch
> updates in the first place? Can't you just use plain execute()?
>
> -O
>
i tryed, but there i got syntax error.

these files are upgrades from a database, but must be run on more than
600 databases.

albertlr

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Albert László-Róbert wrote:
> Oliver Jowett wrote:
>> Albert László-Róbert wrote:
>>
>>> i wish i can do that. the files that contains those statements are up to
>>> 500k ... and the majority of the code contain function implementations.
>> Err ok if you're just reading from a file and giving it to the driver
>> without any (or little) parsing, why exactly are you using batch
>> updates in the first place? Can't you just use plain execute()?
>>
>> -O
>>
> i tryed, but there i got syntax error.

If you're getting a syntax error on execute() then using executeBatch()
is not going to help you since executeBatch() is really just an
optimized special case of execute() .. I think you should be
investigating this syntax error first of all.

-O

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Albert László-Róbert wrote:

> org.postgresql.util.PSQLException: ERROR: unterminated dollar-quoted
> string at or near "$BODY$

What driver version are you using?

-O

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster