BUG #7766: Running a DML statement that affects more than 4 billion rows results in an exception

zelaine@amazon.com writes:

The following bug has been logged on the website:
Bug reference: 7766
Logged by: Zelaine Fong
Email address: zelaine@amazon.com
PostgreSQL version: 8.4.0
Operating system: Linux
Description:

The updateCount field in the ResultHandler interface in Java is defined

as

an int rather than long. Therefore, if you prepare and execute an

update,

delete, or insert statement that affects more than 2^32 rows, you will

get

the following exception:

Unable to interpret the update count in command completion tag

Forwarding this to pgsql-jdbc list. FWIW, guys, the backend currently
thinks that execution counts are unsigned ints. So I surmise that the
problematic update count was actually between 2^31 and 2^32. We might
get around to changing it to unsigned long someday ...

regards, tom lane

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Dave Cramer <pg@fastcrypt.com> writes:

So an unsigned long won't fit inside a java long either, but hopefully it
will never be necessary. That would be a huge number of changes.

I think we'll all be safely dead by the time anybody manages to process
2^63 rows in one PG command ;-). If you can widen the value from int to
long on the Java side, that should be sufficient.

regards, tom lane

wrote:

One thought:

What about returning Statement.SUCCESS_NO_INFO as it says in
http://docs.oracle.com/javase/**6/docs/api/java/sql/**
BatchUpdateException.html#**getUpdateCounts%28%29<http://docs.oracle.com/javase/6/docs/api/java/sql/BatchUpdateException.html#getUpdateCounts%28%29&gt;
and
http://docs.oracle.com/javase/**6/docs/api/java/sql/Statement.**
html#executeBatch%28%29<http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#executeBatch%28%29&gt;

?

It seems better to report no number at all rather than a number (INT_MAX)
that is known to be wrong.

Dave Cramer schrieb:

Ok, this is much more difficult than I thought.

Turns out that there are at least two interfaces that expect an int not a
long.

BatchUpdateException
executeBatch

I'm thinking the only option here is to report INT_MAX as opposed to
failing.

Thoughts ?

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Fri, Dec 21, 2012 at 3:17 PM, Tom Lane <tgl@sss.pgh.pa.us <mailto:
tgl@sss.pgh.pa.us>> wrote:

Dave Cramer <pg@fastcrypt.com <mailto:pg@fastcrypt.com>> writes:

So an unsigned long won't fit inside a java long either, but

hopefully it

will never be necessary. That would be a huge number of changes.

I think we'll all be safely dead by the time anybody manages to
process
2^63 rows in one PG command ;-). If you can widen the value from
int to
long on the Java side, that should be sufficient.

regards, tom lane

wrote:

Kris Jurka schrieb:

On Fri, 11 Jan 2013, Dave Cramer wrote:

Ok, I've pushed this fix into master

You've made any failure to parse the affected row count return

SUCCESS_NO_INFO. Shouldn't you change the integer parsing to a long
parsing and only modify the response if the value is > INT_MAX while still
throwing an exception if we get something that is truly undecipherable?

Kris Jurka

Dave,

I'm completely unfamiliar with the driver's code, so I better won't take
part in the further discussion -- just one thing: Now "insert_oid" won't be
assigned correctly when the assignment of update_count fails:

[QueryExecutorImpl.java]
try
{
update_count = Integer.parseInt(status.**substring(1 +
status.lastIndexOf(' ')));
if (status.startsWith("INSERT"))
insert_oid = Long.parseLong(status.**substring(1 +
status.indexOf(' '),
status.lastIndexOf(' ')));
}
catch (NumberFormatException nfe)
{
update_count=Statement.**SUCCESS_NO_INFO;
}

better be something like this: ?

try
{
update_count = Integer.parseInt(status.**substring(1 +
status.lastIndexOf(' ')));
}
catch (NumberFormatException nfe)
{
update_count=Statement.**SUCCESS_NO_INFO;
}
try {
if (status.startsWith("INSERT"))
insert_oid = Long.parseLong(status.**substring(1 +
status.indexOf(' '),
status.lastIndexOf(' ')));
} catch ( ...
// don't know what expected behaviour should be ...
}

regards
Stefan Reiser

Ok, this is much more difficult than I thought.

Turns out that there are at least two interfaces that expect an int not a
long.

BatchUpdateException
executeBatch

I'm thinking the only option here is to report INT_MAX as opposed to
failing.

Thoughts ?

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Fri, Dec 21, 2012 at 3:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Dave Cramer <pg@fastcrypt.com> writes:

So an unsigned long won't fit inside a java long either, but hopefully

it

will never be necessary. That would be a huge number of changes.

I think we'll all be safely dead by the time anybody manages to process
2^63 rows in one PG command ;-). If you can widen the value from int to
long on the Java side, that should be sufficient.

regards, tom lane

And what about
http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#getUpdateCount()?

P.
On Jan 11, 2013 2:20 PM, "Dave Cramer" <pg@fastcrypt.com> wrote:

Ok, this is much more difficult than I thought.

Turns out that there are at least two interfaces that expect an int not a
long.

BatchUpdateException
executeBatch

I'm thinking the only option here is to report INT_MAX as opposed to
failing.

Thoughts ?

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Fri, Dec 21, 2012 at 3:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Dave Cramer <pg@fastcrypt.com> writes:

So an unsigned long won't fit inside a java long either, but hopefully

it

will never be necessary. That would be a huge number of changes.

I think we'll all be safely dead by the time anybody manages to process
2^63 rows in one PG command ;-). If you can widen the value from int to
long on the Java side, that should be sufficient.

regards, tom lane

Peter,

Can you be more specific about your concerns ?

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Sat, Jan 12, 2013 at 3:25 AM, Péter Kovács <
peter.dunay.kovacs@gmail.com> wrote:

And what about
http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#getUpdateCount()?

P.
On Jan 11, 2013 2:20 PM, "Dave Cramer" <pg@fastcrypt.com> wrote:

Ok, this is much more difficult than I thought.

Turns out that there are at least two interfaces that expect an int not
a long.

BatchUpdateException
executeBatch

I'm thinking the only option here is to report INT_MAX as opposed to
failing.

Thoughts ?

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Fri, Dec 21, 2012 at 3:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Dave Cramer <pg@fastcrypt.com> writes:

So an unsigned long won't fit inside a java long either, but

hopefully it

will never be necessary. That would be a huge number of changes.

I think we'll all be safely dead by the time anybody manages to process
2^63 rows in one PG command ;-). If you can widen the value from int to
long on the Java side, that should be sufficient.

regards, tom lane

I mean what value this method will return for an update statement
affecting, say, five billion rows? But I may misunderstand something.
On Jan 12, 2013 9:57 AM, "Dave Cramer" <pg@fastcrypt.com> wrote:

Peter,

Can you be more specific about your concerns ?

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Sat, Jan 12, 2013 at 3:25 AM, Péter Kovács <
peter.dunay.kovacs@gmail.com> wrote:

And what about
http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#getUpdateCount()?

P.
On Jan 11, 2013 2:20 PM, "Dave Cramer" <pg@fastcrypt.com> wrote:

Ok, this is much more difficult than I thought.

Turns out that there are at least two interfaces that expect an int not
a long.

BatchUpdateException
executeBatch

I'm thinking the only option here is to report INT_MAX as opposed to
failing.

Thoughts ?

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Fri, Dec 21, 2012 at 3:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Dave Cramer <pg@fastcrypt.com> writes:

So an unsigned long won't fit inside a java long either, but

hopefully it

will never be necessary. That would be a huge number of changes.

I think we'll all be safely dead by the time anybody manages to process
2^63 rows in one PG command ;-). If you can widen the value from int
to
long on the Java side, that should be sufficient.

regards, tom lane

Well since it returns an int and it's impossible to return > 2^32 in an
int then we will be returning Statement.SUCCESS_NO_INFO

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Sat, Jan 12, 2013 at 4:27 AM, Péter Kovács <
peter.dunay.kovacs@gmail.com> wrote:

I mean what value this method will return for an update statement
affecting, say, five billion rows? But I may misunderstand something.
On Jan 12, 2013 9:57 AM, "Dave Cramer" <pg@fastcrypt.com> wrote:

Peter,

Can you be more specific about your concerns ?

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Sat, Jan 12, 2013 at 3:25 AM, Péter Kovács <
peter.dunay.kovacs@gmail.com> wrote:

And what about
http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#getUpdateCount()?

P.
On Jan 11, 2013 2:20 PM, "Dave Cramer" <pg@fastcrypt.com> wrote:

Ok, this is much more difficult than I thought.

Turns out that there are at least two interfaces that expect an int
not a long.

BatchUpdateException
executeBatch

I'm thinking the only option here is to report INT_MAX as opposed to
failing.

Thoughts ?

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Fri, Dec 21, 2012 at 3:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Dave Cramer <pg@fastcrypt.com> writes:

So an unsigned long won't fit inside a java long either, but

hopefully it

will never be necessary. That would be a huge number of changes.

I think we'll all be safely dead by the time anybody manages to
process
2^63 rows in one PG command ;-). If you can widen the value from int
to
long on the Java side, that should be sufficient.

regards, tom lane

But being designed for batch updates, is Statement.SUCCESS_NO_INFO
appropriate in the context of plain updates? I think the value of
Statement.SUCCESS_NO_INFO is supposed to be opaque. What if it happens to
be 3, for example? Client code will think three rows have been affected.

Conversely, if you plan to throw a batch update exception for all
successful plain updates affecting too large amount of rows, client code is
unlikely to be prepared to handle batch update exceptions for plain
updates. (I feel there is also a more general usability problem with the
JDBC API for batch updates expecting client code to expect exceptions to be
thrown for successful executions. But I may be misunderstanding
something...)

Peter
On Jan 12, 2013 10:41 AM, "Dave Cramer" <pg@fastcrypt.com> wrote:

Well since it returns an int and it's impossible to return > 2^32 in an
int then we will be returning Statement.SUCCESS_NO_INFO

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Sat, Jan 12, 2013 at 4:27 AM, Péter Kovács <
peter.dunay.kovacs@gmail.com> wrote:

I mean what value this method will return for an update statement
affecting, say, five billion rows? But I may misunderstand something.
On Jan 12, 2013 9:57 AM, "Dave Cramer" <pg@fastcrypt.com> wrote:

Peter,

Can you be more specific about your concerns ?

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Sat, Jan 12, 2013 at 3:25 AM, Péter Kovács <
peter.dunay.kovacs@gmail.com> wrote:

And what about
http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#getUpdateCount()?

P.
On Jan 11, 2013 2:20 PM, "Dave Cramer" <pg@fastcrypt.com> wrote:

Ok, this is much more difficult than I thought.

Turns out that there are at least two interfaces that expect an int
not a long.

BatchUpdateException
executeBatch

I'm thinking the only option here is to report INT_MAX as opposed to
failing.

Thoughts ?

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Fri, Dec 21, 2012 at 3:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Dave Cramer <pg@fastcrypt.com> writes:

So an unsigned long won't fit inside a java long either, but

hopefully it

will never be necessary. That would be a huge number of changes.

I think we'll all be safely dead by the time anybody manages to
process
2^63 rows in one PG command ;-). If you can widen the value from
int to
long on the Java side, that should be sufficient.

regards, tom lane