Thread: PSQLException: Too many update results were returned.

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$BatchResultHandler.handleCommandStatus(AbstractJdbc2Statement.java:2468)
    at org.postgresql.core.v3.QueryExecutorImpl.interpretCommandStatus(QueryExecutorImpl.java:1559)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1274)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:347)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2559)
    at com.xxx.db.Database.executeUpdater(Database.java:xy)
    at com.taylor.zeus.patcher.db.DatabaseSelector.upgradeDatabases(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

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

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

Oliver Jowett wrote:

> semicolon-separated paths.

err, statements not paths (I need more coffee)

-O

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

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

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

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

Oliver Jowett wrote:
> 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
>
if i run that script from the console, it will execute it without a
single warning. i generated the exception i got when i use the execute():

org.postgresql.util.PSQLException: ERROR: unterminated dollar-quoted
string at or near "$BODY$
DECLARE
    vID                 INTEGER"
    at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1512)
    at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:437)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:339)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:331)


this is the starting part of a function declaration.

albertlr

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

Oliver Jowett wrote:
> 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
>
8.1, but i don't know what build number is.

Albert László-Róbert wrote:
> Oliver Jowett wrote:
>> 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
>>
> 8.1, but i don't know what build number is.

Support for dollar-quoting was only introduced in 8.2-504, you will need
to upgrade your driver.

-O

Oliver Jowett wrote:
> Albert László-Róbert wrote:
>> Oliver Jowett wrote:
>>> 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
>>>
>> 8.1, but i don't know what build number is.
>
> Support for dollar-quoting was only introduced in 8.2-504, you will
> need to upgrade your driver.
>
> -O
>
>
i don't know that. i supposed that the driver features are compatible
with the database features.
i will try it and i let you know. i hope this will work.

thanks again for the help
albertlr

Oliver Jowett wrote:
> Albert László-Róbert wrote:
>> Oliver Jowett wrote:
>>> 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
>>>
>> 8.1, but i don't know what build number is.
>
> Support for dollar-quoting was only introduced in 8.2-504, you will
> need to upgrade your driver.
>
> -O
>
>
the problem is the same when i use the execute() method.
i tryed the following drivers: postgresql-8.2-506.jdbc3.jar;
postgresql-8.2-506.jdbc4.jar and postgresql-8.3dev-601.jdbc3.jar

in the mean while i try to figure it out how the development team of the
SQuirreL use the sql execution tool in that client.

albertlr