Thread: 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

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
and
http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#executeBatch%28%29

?

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

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

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

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

Well my bet is the actual value of Statement.SUCCESS_NO_INFO is negative. My understanding of the code is that it will not throw the exception unless there is a real parse error.

Dave

Dave Cramer

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

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

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