PSQLException: Too many update results were returned.

[Albert Laszlo-Robert]

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.

[Oliver Jowett]

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

[Albert László-Róbert]

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

[Oliver Jowett]

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]

Oliver Jowett wrote:

> semicolon-separated paths.

err, statements not paths (I need more coffee)

-O

---------------------------(end of broadcast)---------------------------

[Albert László-Róbert]

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 majo...@postgresql.org so that your
message can get through to the mailing list cleanly

[Oliver Jowett]

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

[Albert László-Róbert]

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

[Oliver Jowett]

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]

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?

[Albert László-Róbert]

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

---------------------------(end of broadcast)---------------------------

[Albert László-Róbert]

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.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

[Oliver Jowett]

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

---------------------------(end of broadcast)---------------------------

[Albert László-Róbert]

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

---------------------------(end of broadcast)---------------------------

[Albert László-Róbert]

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

---------------------------(end of broadcast)---------------------------