New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
How to resolve the "Tried to send an out-of-range integer as a 2-byte value" error #1311
Comments
Well the way PostgreSQL does batches having a batch size this large does not make sense. Not sure what "We can use MAX_ALLOWED_PACKET parameter which is defined in MySQL server in the server side." has to do with anything? |
I wanna to execute a large batch in order to improve throughput. But when I batch insert into a wide table which has lots of columns. So the batch sql will be very very long, and the client will check the param count, and then throw this error if param count overflow the range [Short.MIN_VALUE, Short.MAX_VALUE]. Why use [Short.MIN_VALUE, Short.MAX_VALUE], but not [Integer.MIN_VALUE, Integer.MAX_VALUE] or [Long.MIN_VALUE, Long.MAX_VALUE]. Finally, what's the function of this check? Just for protecting the PG Server ? |
The problem is that the server does not support batching so while you think you may get better throughput, you won't. Batching is an illusion provided by the driver. |
So what can I do ? My problem is I need to insert large of data asap. Wait for your suggestions : ) |
you can use copy, and or use reWriteBatchedInserts https://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters |
Did you know that |
I am so sorry, I am not drinking. : ) But vodka is super great 👍 |
Would you please care to provide the test case then? My wild guess is you are crafting |
Yeah, I use multi value statement, just like Short query can not improve the throughout. I just don't understand why the limit of param count is Short.MIN_VALUE <= len <= Short.MAX_VALUE ? Why not Long.MIN_VALUE <= len <= Long.MAX_VALUE ? |
Wire protocol uses I don't know the exact limit (e.g. see https://stackoverflow.com/a/6582902/1261287 or try yourself), however you won't be able to use more than 65536 binds per SQL anyway. |
|
Welcome to https://www.postgresql.org/list/pgsql-general/ and/or to https://www.postgresql.org/list/pgsql-hackers/ pgjdbc does not specify the protocol. It is the database that specifies it. |
Got it. |
@myware-io you should probably try COPY instead of a large INSERT (it's more easy than ask a change in the protocol 😉) |
The weakpoint of Amount of data is exceedingly huge, about xxx TB. : ) |
I faced a similar problem and can confirm (as proposed) that the actual limit for the number of bindings is between I suppose the exact limit is |
@leskin-in I'm curious where the number 28902 comes from? I wonder if you are exceeding the size of the buffer ? |
@davecramer I used some existing code which gradually increases the number of bindings, but does that non-uniformly. That is why the result is not exact. After your comment, I changed that code in order to test the hypothesis with better precision and now can confirm that the actual value lies between I encountered no other problems when executing my statement. It has the form I considered an option with |
|
Thanks @davecramer . Interestingly, Postgres does not always log COPY tuples in WAL. There is an explanation in Postgres source. Now I wonder at which point the records, for which the optimization mentioned in |
Ah, it mentions there is a check that streaming is not enabled. |
We ran into a similar problem in our app, in some cases we generate the queries, and that can have many parameters, in the worst case, an unlimited number. The challenge that we have is that we also assume that the DB is unreliable, and we therefore retry 8006 errors, which are a "IOException" which is what is returned if there are more than 32767 parameters. FWIW, I think that the wire format allows for 65536 parameters, aka 0…65536, not -32768…32767. Would it be possible for an |
^^^ seems like a reasonable solution. |
I wonder if PostgreSQL has the same length issue passing an array in? There is similar paradigm using "where in ANY(Array)" |
I don't think so. In practice, That means the assertion in It is sad |
In our particular use case we generate something of the form: select a,b,c, from T1 where a in (?,…253…,?) or a in (?,…253…,?) or a in (?,…253…,?) And that where each
I would also be happy for However, the JavaDoc for SQLNonTransientException states:
As far as I can see, in every case where the (effective) cast to INT16 happens, just retrying will never fix it, you would need to change the query in order for it to work to reduce the number of parameters being passed. That should make it non-transient I think?
But, following my reasoning above, if it ever is hit, should it be considered retryable?
Yes! Along with a bunch of other things, like number of entries you are allowed to have in an in clause :-) That said, I think that 32K parameters really should be enough, I just want to be able to easily distinguish between this and any other "IO error". Since at the moment we assume the DB is unreliable, and thus retry any of these:
The kicker for me is that "has more than 32K parameters" throws a SQLException with a SQLState of "08006", and so it gets retried, even though it can never possibly succeed. That is the real problem I want to resolved, but it is hard to determine if all 08006 errors are not retriable, since it IOException is… widespread. For my purposes, I could also live with this case extending Personally, in this case I think that it should return |
Holy cow. I try creating a test case, and I keep hitting the backend limits:
|
Thanks @vlsi for the quick fix! I assume that this will be in 42.3.7? Is there any timeline for when this will be released and/or is there a nightly release that I can use to test this fix? |
@pwagland , please try 42.3.7-SNAPSHOT. |
@vlsi , I finally managed to get to testing this, I discovered a small bug in the fix, the error message isn't quite right, as per my comment on the review: #2525 (review) |
Thank you. I've fixed it in ec4af11 |
I'm submitting a question about "Tried to send an out-of-range integer as a 2-byte value"
Introduce
Background
Maven pom.xml
Source Code
Describe the issue
A clear and concise description of what the issue is.
Java Version
OS Version
PostgreSQL Version
To Reproduce
Steps to reproduce the behaviour:
Expected behaviour
A clear and concise description of what you expected to happen.
And what actually happens
Logs
If possible PostgreSQL logs surrounding the occurrence of the issue
Additionally logs from the driver can be obtained adding
to the connection string
The text was updated successfully, but these errors were encountered: