How to resolve the "Tried to send an out-of-range integer as a 2-byte value" error

[ghost]

I'm submitting a question about "Tried to send an out-of-range integer as a 2-byte value"

• [ jdbc driver] bug report
• feature request

Introduce

Jdbc execute a long/batch insert sql,  like: ```insert into values(?,?,?),(?,?,?),(?,?,?)...```
but client throws an error: "Tried to send an out-of-range integer as a 2-byte value:" ... 
When I review the source code, and I find the implementation like below:

Background

Maven pom.xml

<dependency>
     <groupId>org.postgresql</groupId>
     <artifactId>postgresql</artifactId>
     <version>42.2.5</version>
</dependency>

Source Code

 [Line: 1442]  QueryExecutorImpl.java

  // ....
   pgStream.sendInteger2(params.getParameterCount());

 // ....

[Line: 235] PGStream.java

// ....
public void sendInteger2(int val) throws IOException {
    if (val < Short.MIN_VALUE || val > Short.MAX_VALUE) {
      throw new IOException("Tried to send an out-of-range integer as a 2-byte value: " + val);
    }

    _int2buf[0] = (byte) (val >>> 8);
    _int2buf[1] = (byte) val;
    pg_output.write(_int2buf);
  }
....

Describe the issue
A clear and concise description of what the issue is.

I don't know why param count should be greater/equals than Short.MIN_VALUE (-32768) 
or less/equals than Short.MAX_VALUE (32767). 
When I execute batch sql (especially build lots of columns ), 
this error must be occurred. I can not guess the check whether  to protect the server side?  
I think the jdbc driver client should not limit the length of statement. 
We can use MAX_ALLOWED_PACKET parameter which is defined in MySQL server in the server side.
Wait for your response asap !!!

Java Version

java version "1.8.0_161"
Java(TM) SE Runtime Environment (build 1.8.0_161-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.161-b12, mixed mode)

OS Version

Mac OSX

PostgreSQL Version

EnterpriseDB 9.3.17.42 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-55), 64-bit

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

loggerLevel=TRACE&loggerFile=pgjdbc-trace.log 

to the connection string

[davecramer]

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?

[ghost]

sense

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 ?
I think the driver client should not limit the length of statement. Wait for your reponse asap : )

[davecramer]

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.

[ghost]

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

[davecramer]

you can use copy, and or use reWriteBatchedInserts https://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters

[vlsi]

So what can I do ?

Did you know that what can I do sounds very close to Russian водка найду (English pronunciation is вот кан ай ду) which means I'll find Vodka ?

[ghost]

So what can I do ?

Did you know that what can I do sounds very close to Russian водка найду (English pronunciation is вот кан ай ду) which means I'll find Vodka ?

I am so sorry, I am not drinking. : ) But vodka is super great 👍

[vlsi]

I am so sorry, I am not drinking.

Would you please care to provide the test case then?

My wild guess is you are crafting insert into table(...) values(?,?,?),(?,?,?),(?,?,?),(?,?,?),...(?,?,?) kind of query and you just hit the limit on the number of params per query.
If that is the case, the only viable solution is to make the query shorter (and/or use copy).

[ghost]

use

Yeah, I use multi value statement, just like
insert into table(...) values(?,?,?),(?,?,?),(?,?,?),(?,?,?),...(?,?,?)
But the table has lots of columns, so the query hit the limit of param count.

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 ?

[vlsi]

I just don't understand why the limit of param count

Wire protocol uses int16 field to denote the number of binds. You can't fit Long.MAX_VALUE there.

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.

[ghost]

I just don't understand why the limit of param count

Wire protocol uses int16 field to denote the number of binds. You can't fit Long.MAX_VALUE there.

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.

    if (val < Short.MIN_VALUE || val > Short.MAX_VALUE) {
      throw new IOException("Tried to send an out-of-range integer as a 2-byte value: " + val);
    }

    _int2buf[0] = (byte) (val >>> 8);
    _int2buf[1] = (byte) val;
    pg_output.write(_int2buf);
  }```

input argument [val] is int type, but the range is short type. It's unbelievable, why not integer[min, max] ?

Okay, I split the data list manually. But I think the the protocol type  ```int16```  is not appropriate.

[vlsi]

But I think the the protocol type int16 is not appropriate

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.

[ghost]

But I think the the protocol type int16 is not appropriate

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.
So the kernel/protocol should be redefined : )
Thank you, vlsi and davecramer. Keep in touch ~~

[jorsol]

@myware-io you should probably try COPY instead of a large INSERT (it's more easy than ask a change in the protocol 😉)

[ghost]

@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 COPY...TO...:
Firstly I need to dump out data into file, like *.sql, *.csv, *.dsv. It's very fussy, and lots of files make the management inconvenience.
Secondly I use copy ... to ... statement, but if the process interruppted, how does it execute continually ?

Amount of data is exceedingly huge, about xxx TB. : )

[leskin-in]

I faced a similar problem and can confirm (as proposed) that the actual limit for the number of bindings is between 28902 and 32867 (pgjdbc 42.2.6 on Intel x86/64).

I suppose the exact limit is 32767, as the int16 is a signed integer type, and 32767 is the maximum (positive) value it can represent.

[davecramer]

@leskin-in I'm curious where the number 28902 comes from? I wonder if you are exceeding the size of the buffer ?

[leskin-in]

@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 32767 (excluding - works with this) and 32772 (fails with an error mentioned above).

I encountered no other problems when executing my statement. It has the form INSERT INTO ... VALUES (?, ?, ...), (?, ?, ...), ...; (similar to a one mentioned above). No batching is used, though.

I considered an option with COPY; however, as far as I know, it has two disadvantages: inserted tuples are not logged in the WAL, and the operation itself is Postgres-specific. Staging tables could resolve the first problem, but not the second one.

[davecramer]

inserted tuples are not logged in the WAL I'm 1000% sure they are. Otherwise physical replication would not work.

[leskin-in]

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 COPY FROM implementation applies, actually get logged to WAL.

[leskin-in]

Ah, it mentions there is a check that streaming is not enabled.

[pwagland]

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 SQLNonTransientException to be thrown in the case that too many parameters are being passed in?

[davecramer]

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 SQLNonTransientException to be thrown in the case that too many parameters are being passed in?

^^^ seems like a reasonable solution.

[davecramer]

I wonder if PostgreSQL has the same length issue passing an array in? There is similar paradigm using "where in ANY(Array)"

[vlsi]

^^^ seems like a reasonable solution.

I don't think so.

In practice, con.prepareStatement(...) should fail if we notice more than 32K binds are detected.
At the same time, preparedStatement.set...(int, ...) should fail in case user uses index exceeding 32K.

That means the assertion in sendInteger2 will never be hit if we fix the assertion.

---

It is sad java.sql.DatabaseMetaData provides no API for maximum number of parameters in prepared statement

[pwagland]

I wonder if PostgreSQL has the same length issue passing an array in? There is similar paradigm using "where in ANY(Array)"

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 in has 255 ? statements, and there are about 160 or statements. We do it that way since some databases won't let you have more than 255 things "in" an array. So I think that the answer is yes 😁

^^^ seems like a reasonable solution.

I don't think so.

In practice, con.prepareStatement(...) should fail if we notice more than 32K binds are detected. At the same time, preparedStatement.set...(int, ...) should fail in case user uses index exceeding 32K.

I would also be happy for prepareStatement to fail as well.

However, the JavaDoc for SQLNonTransientException states:

The subclass of SQLException thrown when an instance where a retry of the same operation would fail unless the cause of the SQLException is corrected.

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?

That means the assertion in sendInteger2 will never be hit if we fix the assertion.

But, following my reasoning above, if it ever is hit, should it be considered retryable?

It is sad java.sql.DatabaseMetaData provides no API for maximum number of parameters in prepared statement

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:

  /**
   * After a connection has been established, it went bad.
   */
  CONNECTION_FAILURE("08006"),
  CONNECTION_FAILURE_DURING_TRANSACTION("08007"),

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 SQLNonTransientConnectionException, with the "08006" SQLState, since I first check for the NonTransient before checking the SQLState. That would also allow existing code that relied on the 08006 state to continue working unless they also cast to PSQLException, which I would hope that no-one would ever do!

Personally, in this case I think that it should return COMMUNICATION_ERROR("08S01") instead?

[vlsi]

Holy cow.

I try creating a test case, and I keep hitting the backend limits:

1. First I tried select ? c1, ? c2, ? c3, ... ? c32000..., and it failed with target lists can have at most 1664 entries. Hopefully the limit can be added to https://www.postgresql.org/docs/current/limits.html

2. Then I tried select ?||?||?||?||?...||?, and it failed with stack depth limit exceeded. Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.

[pwagland]

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?

[vlsi]

@pwagland , please try 42.3.7-SNAPSHOT.
I would go for releasing is week.

[pwagland]

@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)

[vlsi]

Thank you. I've fixed it in ec4af11