org.postgresql.util.PSQLException: Bad value for type int

General
#1

Hi, I recently moved my application to Cockroach db from MySql database. While I am trying to persist my transaction I am getting following error:

Caused by: org.postgresql.util.PSQLException: Bad value for type int : 426240218309623809
at org.postgresql.jdbc.PgResultSet.toInt(PgResultSet.java:2838)
at org.postgresql.jdbc.PgResultSet.getInt(PgResultSet.java:2073)
at org.postgresql.jdbc.PgResultSet.getInt(PgResultSet.java:2485)
at org.apache.commons.dbcp2.DelegatingResultSet.getInt(DelegatingResultSet.java:633)
at org.apache.commons.dbcp2.DelegatingResultSet.getInt(DelegatingResultSet.java:633)
at org.hibernate.id.IdentifierGeneratorHelper.extractIdentifier(IdentifierGeneratorHelper.java:162)
at org.hibernate.id.IdentifierGeneratorHelper.get(IdentifierGeneratorHelper.java:145)
at org.hibernate.id.IdentifierGeneratorHelper.getGeneratedIdentity(IdentifierGeneratorHelper.java:77)
at org.hibernate.dialect.identity.GetGeneratedKeysDelegate.executeAndExtract(GetGeneratedKeysDelegate.java:61)
at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:42)
… 89 more

I am not sure from where this magical number(426240218309623809) is coming ?Whereas I was not getting any error in Mysql

I debugged a lot but this issue only coming with Cockroach DB.

Please suggest.Thanks in advance for any suggestion.

#2

Hi @rishi2019,

It’s difficult to say what’s happening without more information. If you can provide a small reproduction of the bug, or a log of the SQL statements your application is executing, then we might be able to provide some pointers on what’s going wrong. Does this problem also occur against Postgres?

Justin

#3

Hi @justin ,

Below is sample code of my JPA based Entity class and database table:

==================Entity Class==================

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(catalog=“sample_db”, name=“sample_data”)
public class SampleData {

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name = “id”, unique = true, nullable = false)
private Integer id;

@Column(name = “name”)
private String name;
//getter/setter
}

==============sample_data table========

CREATE TABLE sample_data (
id INT NOT NULL DEFAULT unique_rowid(),
name VARCHAR NULL,
CONSTRAINT sample_data_pk PRIMARY KEY (id ASC),
FAMILY “primary” (id, name)
)

=============application.properties======
jdbc.driverClassName=org.postgresql.Driver
jdbc.url=jdbc:postgresql://127.0.0.1:26257/sample_db?sslmode=disable
jdbc.username=root
jdbc.password=root

============================

As per my investigation it seems that this magical number is unique generated ID value for entity during persisting new value into database and which is incompatible with variable “private Integer id;”.

So, as a fix If I replace ‘Integer’ with ‘Long’ data type it works fine and above mentioned magical value is getting inserted into database ID field.

My assumption is that it seems that magical number is beyond Int range ( Java Integer) that is the reason why It is throwing this error.

But question is:

  1. How to restrict to generate smaller generated ID value?
  2. Even if Database field ID is of type INT then why it is generating so long value?

I am using Postgresql version 42.2.5. and JPA for persisting records.

Thanks in advance for support.

#4

In CockroachDB the type “INT” is 64-bit by default, compared to 32-bit in PostgreSQL. So your choice to replace Int by Long in Java was the correct change.

The value you see is because CockroachDB uses the function unique_rowid() to generate row IDs, and this function populates all the 64 bits of a INT column. Of course the resulting values do not fit the 32-bit Java Int.