Re: Column order seems to play a role after migration from 8.1 to 9.1 if sequences are used

Hello,

I don't know if the root cause of my observation is effectively
JDBC-related, but I thought it might be the best starting point.

TL:DR: After migrating a PosgreSQL DB from 8.1 to 9.1, I had to reorder the
columns of a table to make sure the primary key column that is
automatically filled by a sequence, is the first one in the table. This was
not an issue with 8.1. The problem - during INSERT - only occurs via JDBC.
When using the CLI (i.e. psql), it works fine.

The details of what I've observed:

In preparing a major system upgrade for a legacy application, I tested the
migration of the PostgreSQL server from version 8.1 to 9.1 (on Ubuntu LTS
12.04). On top of the database I use JBoss AS 7.1 with Hibernate, JDBC and
Java 1.7.
I tested with different but recent JDBC drivers for the Java 1.7 platform
(i.e. postgresql-9.3-1102.jdbc41.jar, postgresql-9.4.1208.jre7.jar). The
entity beans are specified to have their primary key (Integer value)
generated by the database via a sequence:

In the bean:

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
public java.lang.Integer getId()
{
return id;
}

In the table:

\d phrases
Table "phrases"
Column | Type | Modifiers
-----------+----------------------+----------------------------------------------------------------------
phrase | text |
frequency | integer | default 4
language | character varying(3) |
id | bigint | not null default nextval
('phrases_id_seq'::regclass)
Indexes:
"phrases_pkey" PRIMARY KEY, btree (id)

However, after switching to the 9.1 server, I got following error message:

WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error: 0,
SQLState: 22003
ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Bad value for type
int : lklkh

Increasing the log levels provided me with following details:

DEBUG [org.hibernate.SQL] insert into phrases (frequency, language, phrase)
values (?, ?, ?)
TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding parameter [1]
as [INTEGER] - 4
TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding parameter [2]
as [VARCHAR] - ger
TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding parameter [3]
as [VARCHAR] - lklkh
WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error: 0,
SQLState: 22003
ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Bad value for type
int : lklkh

Performing the INSERT via CLI worked nicely:

INSERT INTO phrases (frequency, language, phrase) VALUES (4, 'ger',
'lklh');
INSERT 0 1

This led me to the assumption that there was something wrong with the JDBC
driver or the hibernate layer, but none of the tested modifications made
the problem go away. When searching for the given SQL error code & state, I
stumbled on a stackoverflow post (
http://stackoverflow.com/questions/25977903/hibernate-columns-mismatch-on-persistance
), and indeed, after I have re-ordered the columns in the table moving the
id column to the first position, it works without a flaw.

\d phrases
Table "phrases"
Column | Type | Modifiers
-----------+----------------------+----------------------------------------------------------------------
id | bigint | not null default nextval
('phrases_id_seq'::regclass)
phrase | text |
frequency | integer | default 4
language | character varying(3) |
Indexes:
"phrases_pkey" PRIMARY KEY, btree (id)

As it took me quite a while to figure out this work around, I wanted to
share this with the community and ask the question, if you have any ideas
what the actual root cause is. Please point me to any resources, if that is
a known and justified behaviour of the database. Otherwise, I hope this
might help others in similar situations.

Cheers,
Andreas

Andreas,

My guess is that hibernate is adding the id parameter into the statement
and expects it to be the first column.

This could be confirmed by looking at the server logs.

P.S. Please subscribe to the pgjdbc list

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 21 April 2016 at 04:59, Andreas Arens <andreas(dot)arens(at)list(dot)lu> wrote:

> Hello,
>
> I don't know if the root cause of my observation is effectively
> JDBC-related, but I thought it might be the best starting point.
>
> TL:DR: After migrating a PosgreSQL DB from 8.1 to 9.1, I had to reorder
> the columns of a table to make sure the primary key column that is
> automatically filled by a sequence, is the first one in the table. This was
> not an issue with 8.1. The problem - during INSERT - only occurs via JDBC.
> When using the CLI (i.e. psql), it works fine.
>
> The details of what I've observed:
>
> In preparing a major system upgrade for a legacy application, I tested the
> migration of the PostgreSQL server from version 8.1 to 9.1 (on Ubuntu LTS
> 12.04). On top of the database I use JBoss AS 7.1 with Hibernate, JDBC and
> Java 1.7.
> I tested with different but recent JDBC drivers for the Java 1.7 platform
> (i.e. postgresql-9.3-1102.jdbc41.jar, postgresql-9.4.1208.jre7.jar). The
> entity beans are specified to have their primary key (Integer value)
> generated by the database via a sequence:
>
> In the bean:
>
> @Id
> @GeneratedValue(strategy = GenerationType.*IDENTITY*)
> @Column(name = "id")
> *public* java.lang.Integer getId()
> {
> *return* id;
> }
>
> In the table:
>
> \d phrases
> Table "phrases"
> Column | Type | Modifiers
>
> -----------+----------------------+----------------------------------------------------------------------
> phrase | text |
> frequency | integer | default 4
> language | character varying(3) |
> id | bigint | not null default
> nextval('phrases_id_seq'::regclass)
> Indexes:
> "phrases_pkey" PRIMARY KEY, btree (id)
>
>
> However, after switching to the 9.1 server, I got following error message:
>
> WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error: 0,
> SQLState: 22003
> ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Bad value for
> type int : lklkh
>
>
> Increasing the log levels provided me with following details:
>
> DEBUG [org.hibernate.SQL] insert into phrases (frequency, language,
> phrase) values (?, ?, ?)
> TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding parameter
> [1] as [INTEGER] - 4
> TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding parameter
> [2] as [VARCHAR] - ger
> TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding parameter
> [3] as [VARCHAR] - lklkh
> WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error: 0,
> SQLState: 22003
> ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Bad value for
> type int : lklkh
>
> Performing the INSERT via CLI worked nicely:
>
> INSERT INTO phrases (frequency, language, phrase) VALUES (4, 'ger',
> 'lklh');
> INSERT 0 1
>
> This led me to the assumption that there was something wrong with the JDBC
> driver or the hibernate layer, but none of the tested modifications made
> the problem go away. When searching for the given SQL error code & state, I
> stumbled on a stackoverflow post (
> http://stackoverflow.com/questions/25977903/hibernate-columns-mismatch-on-persistance)
> and indeed, after I have re-ordered the columns in the table moving the id column
> to the first position, it works without a flaw.
>
> \d phrases
> Table "phrases"
> Column | Type | Modifiers
>
> -----------+----------------------+----------------------------------------------------------------------
> id | bigint | not null default
> nextval('phrases_id_seq'::regclass)
> phrase | text |
> frequency | integer | default 4
> language | character varying(3) |
> Indexes:
> "phrases_pkey" PRIMARY KEY, btree (id)
>
> As it took me quite a while to figure out this work around, I wanted to
> share this with the community and ask the question, if you have any ideas
> what the actual root cause is. Please point me to any resources, if that is
> a known and justified behaviour of the database. Otherwise, I hope this
> might help others in similar situations.
>
> Cheers,
> Andreas
>

Dave,

Thanks a lot for your quick reply.

Unfortunately, I don't think your guess is right as the generated statement
does not contain the id. Here's what the PostgreSQL server logs:

2016-04-21 14:11:21 CEST LOG: execute <unnamed>: insert into phrases
(frequency, language, phrase) values ($1, $2, $3) RETURNING *
2016-04-21 14:11:21 CEST DETAIL: parameters: $1 = '4', $2 = 'ger', $3 =
'lkjlkjlkj ljlékjlékj lék'

Another point that speaks against this theory is that the same JDBC driver
jar works fine with PostgreSQL 8.1 .

Andreas

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Andreas Arens <andreas(dot)arens(at)list(dot)lu>
Cc: List <pgsql-jdbc(at)postgresql(dot)org>
Date: 21/04/2016 12:15
Subject: Re: [JDBC] Column order seems to play a role after migration
from 8.1 to 9.1 if sequences are used
Sent by: davecramer(at)gmail(dot)com

Andreas,

My guess is that hibernate is adding the id parameter into the statement
and expects it to be the first column.

This could be confirmed by looking at the server logs.

P.S. Please subscribe to the pgjdbc list

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 21 April 2016 at 04:59, Andreas Arens <andreas(dot)arens(at)list(dot)lu> wrote:
Hello,

I don't know if the root cause of my observation is effectively
JDBC-related, but I thought it might be the best starting point.

TL:DR: After migrating a PosgreSQL DB from 8.1 to 9.1, I had to reorder
the columns of a table to make sure the primary key column that is
automatically filled by a sequence, is the first one in the table. This
was not an issue with 8.1. The problem - during INSERT - only occurs via
JDBC. When using the CLI (i.e. psql), it works fine.

The details of what I've observed:

In preparing a major system upgrade for a legacy application, I tested
the migration of the PostgreSQL server from version 8.1 to 9.1 (on Ubuntu
LTS 12.04). On top of the database I use JBoss AS 7.1 with Hibernate,
JDBC and Java 1.7.
 I tested with different but recent JDBC drivers for the Java 1.7
platform (i.e. postgresql-9.3-1102.jdbc41.jar,
postgresql-9.4.1208.jre7.jar). The entity beans are specified to have
their primary key (Integer value) generated by the database via a
sequence:

In the bean:

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    public java.lang.Integer getId()
    {
        return id;
    }

In the table:

\d phrases
                                     Table "phrases"
  Column   |         Type         |
Modifiers
-----------+----------------------+----------------------------------------------------------------------

 phrase    | text                 |
 frequency | integer              | default 4
 language  | character varying(3) |
 id        | bigint               | not null default nextval
('phrases_id_seq'::regclass)
Indexes:
    "phrases_pkey" PRIMARY KEY, btree (id)

However, after switching to the 9.1 server, I got following error
message:

WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error: 0,
SQLState: 22003
ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Bad value for
type int : lklkh

Increasing the log levels provided me with following details:

DEBUG [org.hibernate.SQL] insert into phrases (frequency, language,
phrase) values (?, ?, ?)
TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding parameter
[1] as [INTEGER] - 4
TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding parameter
[2] as [VARCHAR] - ger
TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding parameter
[3] as [VARCHAR] - lklkh
WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error: 0,
SQLState: 22003
ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Bad value for
type int : lklkh

Performing the INSERT via CLI worked nicely:

INSERT INTO phrases (frequency, language, phrase) VALUES (4, 'ger',
'lklh');
INSERT 0 1

This led me to the assumption that there was something wrong with the
JDBC driver or the hibernate layer, but none of the tested modifications
made the problem go away. When searching for the given SQL error code &
state, I stumbled on a stackoverflow post (
http://stackoverflow.com/questions/25977903/hibernate-columns-mismatch-on-persistance
), and indeed, after I have re-ordered the columns in the table moving
the id column to the first position, it works without a flaw.

\d phrases
                                     Table "phrases"
  Column   |         Type         |
Modifiers
-----------+----------------------+----------------------------------------------------------------------

 id        | bigint               | not null default nextval
('phrases_id_seq'::regclass)
 phrase    | text                 |
 frequency | integer              | default 4
 language  | character varying(3) |
Indexes:
    "phrases_pkey" PRIMARY KEY, btree (id)

As it took me quite a while to figure out this work around, I wanted to
share this with the community and ask the question, if you have any ideas
what the actual root cause is. Please point me to any resources, if that
is a known and justified behaviour of the database. Otherwise, I hope
this might help others in similar situations.

Cheers,
 Andreas

Andreas,

So did the server have an error after that ? It does not appear to. In
which case it is still some problem with hibernate. At this point I suspect
hibernate thinks the first value returned is the id which it subsequently
tries to put into the id column?

PS. Why did you not upgrade to 9.5 ? FYI, 9.2 has significant performance
benefits over 9.1

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 21 April 2016 at 08:19, Andreas Arens <andreas(dot)arens(at)list(dot)lu> wrote:

> Dave,
>
> Thanks a lot for your quick reply.
>
> Unfortunately, I don't think your guess is right as the generated
> statement does not contain the id. Here's what the PostgreSQL server logs:
>
> 2016-04-21 14:11:21 CEST LOG: execute <unnamed>: insert into phrases
> (frequency, language, phrase) values ($1, $2, $3) RETURNING *
> 2016-04-21 14:11:21 CEST DETAIL: parameters: $1 = '4', $2 = 'ger', $3 =
> 'lkjlkjlkj ljlékjlékj lék'
>
> Another point that speaks against this theory is that the same JDBC driver
> jar works fine with PostgreSQL 8.1 .
>
> Andreas
>
> [image: Inactive hide details for Dave Cramer ---21/04/2016
> 12:15:24---Andreas, My guess is that hibernate is adding the id parameter i]Dave
> Cramer ---21/04/2016 12:15:24---Andreas, My guess is that hibernate is
> adding the id parameter into the statement
>
> From: Dave Cramer <pg(at)fastcrypt(dot)com>
> To: Andreas Arens <andreas(dot)arens(at)list(dot)lu>
> Cc: List <pgsql-jdbc(at)postgresql(dot)org>
> Date: 21/04/2016 12:15
> Subject: Re: [JDBC] Column order seems to play a role after migration
> from 8.1 to 9.1 if sequences are used
> Sent by: davecramer(at)gmail(dot)com
> ------------------------------
>
>
>
> Andreas,
>
> My guess is that hibernate is adding the id parameter into the statement
> and expects it to be the first column.
>
> This could be confirmed by looking at the server logs.
>
> P.S. Please subscribe to the pgjdbc list
>
> Dave Cramer
>
> *davec(at)postgresintl(dot)com* <davec(at)postgresintl(dot)com>
> *www.postgresintl.com* <http://www.postgresintl.com/>
>
> On 21 April 2016 at 04:59, Andreas Arens <*andreas(dot)arens(at)list(dot)lu*
> <andreas(dot)arens(at)list(dot)lu>> wrote:
>
> Hello,
>
> I don't know if the root cause of my observation is effectively
> JDBC-related, but I thought it might be the best starting point.
>
> TL:DR: After migrating a PosgreSQL DB from 8.1 to 9.1, I had to
> reorder the columns of a table to make sure the primary key column that is
> automatically filled by a sequence, is the first one in the table. This was
> not an issue with 8.1. The problem - during INSERT - only occurs via JDBC.
> When using the CLI (i.e. psql), it works fine.
>
> The details of what I've observed:
>
> In preparing a major system upgrade for a legacy application, I tested
> the migration of the PostgreSQL server from version 8.1 to 9.1 (on Ubuntu
> LTS 12.04). On top of the database I use JBoss AS 7.1 with Hibernate, JDBC
> and Java 1.7.
> I tested with different but recent JDBC drivers for the Java 1.7
> platform (i.e. postgresql-9.3-1102.jdbc41.jar,
> postgresql-9.4.1208.jre7.jar). The entity beans are specified to have their
> primary key (Integer value) generated by the database via a sequence:
>
> In the bean:
>
> @Id
> @GeneratedValue(strategy = GenerationType.*IDENTITY*)
> @Column(name = "id")
> *public* java.lang.Integer getId()
> {
> *return* id;
> }
>
> In the table:
>
> \d phrases
> Table "phrases"
> Column | Type |
> Modifiers
>
> -----------+----------------------+----------------------------------------------------------------------
> phrase | text |
> frequency | integer | default 4
> language | character varying(3) |
> id | bigint | not null default
> nextval('phrases_id_seq'::regclass)
> Indexes:
> "phrases_pkey" PRIMARY KEY, btree (id)
>
>
> However, after switching to the 9.1 server, I got following error
> message:
>
> WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error: 0,
> SQLState: 22003
> ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Bad value for
> type int : lklkh
>
>
> Increasing the log levels provided me with following details:
>
> DEBUG [org.hibernate.SQL] insert into phrases (frequency, language,
> phrase) values (?, ?, ?)
> TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding
> parameter [1] as [INTEGER] - 4
> TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding
> parameter [2] as [VARCHAR] - ger
> TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding
> parameter [3] as [VARCHAR] - lklkh
> WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error: 0,
> SQLState: 22003
> ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Bad value for
> type int : lklkh
>
> Performing the INSERT via CLI worked nicely:
>
> INSERT INTO phrases (frequency, language, phrase) VALUES (4, 'ger',
> 'lklh');
> INSERT 0 1
>
> This led me to the assumption that there was something wrong with the
> JDBC driver or the hibernate layer, but none of the tested modifications
> made the problem go away. When searching for the given SQL error code &
> state, I stumbled on a stackoverflow post (
> *http://stackoverflow.com/questions/25977903/hibernate-columns-mismatch-on-persistance*
> <http://stackoverflow.com/questions/25977903/hibernate-columns-mismatch-on-persistance>),
> and indeed, after I have re-ordered the columns in the table moving the
> id column to the first position, it works without a flaw.
>
> \d phrases
> Table "phrases"
> Column | Type |
> Modifiers
>
> -----------+----------------------+----------------------------------------------------------------------
> id | bigint | not null default
> nextval('phrases_id_seq'::regclass)
> phrase | text |
> frequency | integer | default 4
> language | character varying(3) |
> Indexes:
> "phrases_pkey" PRIMARY KEY, btree (id)
>
> As it took me quite a while to figure out this work around, I wanted
> to share this with the community and ask the question, if you have any
> ideas what the actual root cause is. Please point me to any resources, if
> that is a known and justified behaviour of the database. Otherwise, I hope
> this might help others in similar situations.
>
> Cheers,
> Andreas
>
>
>

Dave,

Well, it seems that the PostgreSQL server itself does not log an error, but
I get it from the JDBC driver:

Caused by: org.postgresql.util.PSQLException: Bad value for type int :
lkjlkjlkj ljlékjlékj lék lkjlék àékj éà àék
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toInt
(AbstractJdbc2ResultSet.java:2955)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt
(AbstractJdbc2ResultSet.java:2138)
at org.jboss.jca.adapters.jdbc.WrappedResultSet.getInt
(WrappedResultSet.java:1052)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[rt.jar:1.7.0_67]
at sun.reflect.NativeMethodAccessorImpl.invoke
(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_67]
at sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_67]
at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_67]
at
org.hibernate.engine.jdbc.internal.proxy.AbstractResultSetProxyHandler.continueInvocation
(AbstractResultSetProxyHandler.java:104)
[hibernate-core-4.0.1.Final.jar:4.0.1.Final]

which might be the result from a wrong assumption within the Hibernate
layer while evaluating the result set. This clearly supports your view.
Weird though that it only appears with 9.1 and not 8.1.

I am upgrading to 9.1 as it is the latest version supported by Ubuntu 12.04
LTS. Once this is done, the system will be migrated to 16.04 LTS and then
the database to PostgreSQL to 9.5.

Andreas

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Andreas Arens <andreas(dot)arens(at)list(dot)lu>
Cc: List <pgsql-jdbc(at)postgresql(dot)org>
Date: 21/04/2016 14:24
Subject: Re: [JDBC] Column order seems to play a role after migration
from 8.1 to 9.1 if sequences are used
Sent by: pgsql-jdbc-owner(at)postgresql(dot)org

Andreas,

So did the server have an error after that ? It does not appear to. In
which case it is still some problem with hibernate. At this point I suspect
hibernate thinks the first value returned is the id which it subsequently
tries to put into the id column?

PS. Why did you not upgrade to 9.5 ? FYI, 9.2 has significant performance
benefits over 9.1

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 21 April 2016 at 08:19, Andreas Arens <andreas(dot)arens(at)list(dot)lu> wrote:
Dave,

Thanks a lot for your quick reply.

Unfortunately, I don't think your guess is right as the generated
statement does not contain the id. Here's what the PostgreSQL server
logs:

2016-04-21 14:11:21 CEST LOG:  execute <unnamed>: insert into phrases
(frequency, language, phrase) values ($1, $2, $3) RETURNING *
2016-04-21 14:11:21 CEST DETAIL:  parameters: $1 = '4', $2 = 'ger', $3 =
'lkjlkjlkj ljlékjlékj lék'

Another point that speaks against this theory is that the same JDBC
driver jar works fine with PostgreSQL 8.1 .

Andreas

Dave Cramer ---21/04/2016 12:15:24---Andreas, My guess is that hibernate
is adding the id parameter into the statement

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Andreas Arens <andreas(dot)arens(at)list(dot)lu>
Cc: List <pgsql-jdbc(at)postgresql(dot)org>
Date: 21/04/2016 12:15
Subject: Re: [JDBC] Column order seems to play a role after migration
from 8.1 to 9.1 if sequences are used
Sent by: davecramer(at)gmail(dot)com

Andreas,

My guess is that hibernate is adding the id parameter into the statement
and expects it to be the first column.

This could be confirmed by looking at the server logs.

P.S. Please subscribe to the pgjdbc list

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 21 April 2016 at 04:59, Andreas Arens <andreas(dot)arens(at)list(dot)lu> wrote:
Hello,

I don't know if the root cause of my observation is effectively
JDBC-related, but I thought it might be the best starting point.

TL:DR: After migrating a PosgreSQL DB from 8.1 to 9.1, I had to
reorder the columns of a table to make sure the primary key column
that is automatically filled by a sequence, is the first one in the
table. This was not an issue with 8.1. The problem - during INSERT
- only occurs via JDBC. When using the CLI (i.e. psql), it works
fine.

The details of what I've observed:

In preparing a major system upgrade for a legacy application, I
tested the migration of the PostgreSQL server from version 8.1 to
9.1 (on Ubuntu LTS 12.04). On top of the database I use JBoss AS
7.1 with Hibernate, JDBC and Java 1.7.
 I tested with different but recent JDBC drivers for the Java 1.7
platform (i.e. postgresql-9.3-1102.jdbc41.jar,
postgresql-9.4.1208.jre7.jar). The entity beans are specified to
have their primary key (Integer value) generated by the database
via a sequence:

In the bean:

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    public java.lang.Integer getId()
    {
        return id;
    }

In the table:

\d phrases
                                     Table "phrases"
  Column   |         Type         |
Modifiers
-----------+----------------------+----------------------------------------------------------------------

 phrase    | text                 |
 frequency | integer              | default 4
 language  | character varying(3) |
 id        | bigint               | not null default nextval
('phrases_id_seq'::regclass)
Indexes:
    "phrases_pkey" PRIMARY KEY, btree (id)

However, after switching to the 9.1 server, I got following error
message:

WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error:
0, SQLState: 22003
ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Bad value
for type int : lklkh

Increasing the log levels provided me with following details:

DEBUG [org.hibernate.SQL] insert into phrases (frequency, language,
phrase) values (?, ?, ?)
TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding
parameter [1] as [INTEGER] - 4
TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding
parameter [2] as [VARCHAR] - ger
TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding
parameter [3] as [VARCHAR] - lklkh
WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error:
0, SQLState: 22003
ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Bad value
for type int : lklkh

Performing the INSERT via CLI worked nicely:

INSERT INTO phrases (frequency, language, phrase) VALUES (4, 'ger',
'lklh');
INSERT 0 1

This led me to the assumption that there was something wrong with
the JDBC driver or the hibernate layer, but none of the tested
modifications made the problem go away. When searching for the
given SQL error code & state, I stumbled on a stackoverflow post (
http://stackoverflow.com/questions/25977903/hibernate-columns-mismatch-on-persistance
), and indeed, after I have re-ordered the columns in the table
moving the id column to the first position, it works without a
flaw.

\d phrases
                                     Table "phrases"
  Column   |         Type         |
Modifiers
-----------+----------------------+----------------------------------------------------------------------

 id        | bigint               | not null default nextval
('phrases_id_seq'::regclass)
 phrase    | text                 |
 frequency | integer              | default 4
 language  | character varying(3) |
Indexes:
    "phrases_pkey" PRIMARY KEY, btree (id)

As it took me quite a while to figure out this work around, I
wanted to share this with the community and ask the question, if
you have any ideas what the actual root cause is. Please point me
to any resources, if that is a known and justified behaviour of the
database. Otherwise, I hope this might help others in similar
situations.

Cheers,
 Andreas

This is a bug in their code. They are relying on the presumption that id is
the first column

As for pg versions. Use the projects distros we patch them ahead of anyone.
On Apr 21, 2016 8:45 AM, "Andreas Arens" <andreas(dot)arens(at)list(dot)lu> wrote:

> Dave,
>
> Well, it seems that the PostgreSQL server itself does not log an error,
> but I get it from the JDBC driver:
>
> Caused by: org.postgresql.util.PSQLException: Bad value for type int :
> lkjlkjlkj ljlékjlékj lék lkjlék àékj éà àék
> at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.toInt(AbstractJdbc2ResultSet.java:2955)
> at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2ResultSet.java:2138)
> at
> org.jboss.jca.adapters.jdbc.WrappedResultSet.getInt(WrappedResultSet.java:1052)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> [rt.jar:1.7.0_67]
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> [rt.jar:1.7.0_67]
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> [rt.jar:1.7.0_67]
> at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_67]
> at
> org.hibernate.engine.jdbc.internal.proxy.AbstractResultSetProxyHandler.continueInvocation(AbstractResultSetProxyHandler.java:104)
> [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
>
> which might be the result from a wrong assumption within the Hibernate
> layer while evaluating the result set. This clearly supports your view.
> Weird though that it only appears with 9.1 and not 8.1.
>
> I am upgrading to 9.1 as it is the latest version supported by Ubuntu
> 12.04 LTS. Once this is done, the system will be migrated to 16.04 LTS and
> then the database to PostgreSQL to 9.5.
>
> Andreas
>
>
> [image: Inactive hide details for Dave Cramer ---21/04/2016
> 14:24:47---Andreas, So did the server have an error after that ? It does no]Dave
> Cramer ---21/04/2016 14:24:47---Andreas, So did the server have an error
> after that ? It does not appear to. In
>
> From: Dave Cramer <pg(at)fastcrypt(dot)com>
> To: Andreas Arens <andreas(dot)arens(at)list(dot)lu>
> Cc: List <pgsql-jdbc(at)postgresql(dot)org>
> Date: 21/04/2016 14:24
> Subject: Re: [JDBC] Column order seems to play a role after migration
> from 8.1 to 9.1 if sequences are used
> Sent by: pgsql-jdbc-owner(at)postgresql(dot)org
> ------------------------------
>
>
>
> Andreas,
>
> So did the server have an error after that ? It does not appear to. In
> which case it is still some problem with hibernate. At this point I suspect
> hibernate thinks the first value returned is the id which it subsequently
> tries to put into the id column?
>
> PS. Why did you not upgrade to 9.5 ? FYI, 9.2 has significant performance
> benefits over 9.1
>
>
>
> Dave Cramer
>
> *davec(at)postgresintl(dot)com* <davec(at)postgresintl(dot)com>
> *www.postgresintl.com* <http://www.postgresintl.com/>
>
> On 21 April 2016 at 08:19, Andreas Arens <*andreas(dot)arens(at)list(dot)lu*
> <andreas(dot)arens(at)list(dot)lu>> wrote:
>
> Dave,
>
> Thanks a lot for your quick reply.
>
> Unfortunately, I don't think your guess is right as the generated
> statement does not contain the id. Here's what the PostgreSQL server logs:
>
> 2016-04-21 14:11:21 CEST LOG: execute <unnamed>: insert into phrases
> (frequency, language, phrase) values ($1, $2, $3) RETURNING *
> 2016-04-21 14:11:21 CEST DETAIL: parameters: $1 = '4', $2 = 'ger', $3
> = 'lkjlkjlkj ljlékjlékj lék'
>
> Another point that speaks against this theory is that the same JDBC
> driver jar works fine with PostgreSQL 8.1 .
>
> Andreas
>
> Dave Cramer ---21/04/2016 12:15:24---Andreas, My guess is that
> hibernate is adding the id parameter into the statement
>
> From: Dave Cramer <*pg(at)fastcrypt(dot)com* <pg(at)fastcrypt(dot)com>>
> To: Andreas Arens <*andreas(dot)arens(at)list(dot)lu* <andreas(dot)arens(at)list(dot)lu>>
> Cc: List <*pgsql-jdbc(at)postgresql(dot)org* <pgsql-jdbc(at)postgresql(dot)org>>
> Date: 21/04/2016 12:15
> Subject: Re: [JDBC] Column order seems to play a role after migration
> from 8.1 to 9.1 if sequences are used
> Sent by: *davecramer(at)gmail(dot)com* <davecramer(at)gmail(dot)com>
> ------------------------------
>
>
>
>
> Andreas,
>
> My guess is that hibernate is adding the id parameter into the
> statement and expects it to be the first column.
>
> This could be confirmed by looking at the server logs.
>
> P.S. Please subscribe to the pgjdbc list
>
> Dave Cramer
>
> *davec(at)postgresintl(dot)com* <davec(at)postgresintl(dot)com>
> *www.postgresintl.com* <http://www.postgresintl.com/>
>
> On 21 April 2016 at 04:59, Andreas Arens <*andreas(dot)arens(at)list(dot)lu*
> <andreas(dot)arens(at)list(dot)lu>> wrote:
> Hello,
>
> I don't know if the root cause of my observation is effectively
> JDBC-related, but I thought it might be the best starting point.
>
> TL:DR: After migrating a PosgreSQL DB from 8.1 to 9.1, I had to
> reorder the columns of a table to make sure the primary key column that is
> automatically filled by a sequence, is the first one in the table. This was
> not an issue with 8.1. The problem - during INSERT - only occurs via JDBC.
> When using the CLI (i.e. psql), it works fine.
>
> The details of what I've observed:
>
> In preparing a major system upgrade for a legacy application, I
> tested the migration of the PostgreSQL server from version 8.1 to 9.1 (on
> Ubuntu LTS 12.04). On top of the database I use JBoss AS 7.1 with
> Hibernate, JDBC and Java 1.7.
> I tested with different but recent JDBC drivers for the Java 1.7
> platform (i.e. postgresql-9.3-1102.jdbc41.jar,
> postgresql-9.4.1208.jre7.jar). The entity beans are specified to have their
> primary key (Integer value) generated by the database via a sequence:
>
> In the bean:
>
> @Id
> @GeneratedValue(strategy = GenerationType.*IDENTITY*)
> @Column(name = "id")
> *public* java.lang.Integer getId()
> {
> *return* id;
> }
>
> In the table:
>
> \d phrases
> Table "phrases"
> Column | Type |
> Modifiers
>
> -----------+----------------------+----------------------------------------------------------------------
> phrase | text |
> frequency | integer | default 4
> language | character varying(3) |
> id | bigint | not null default
> nextval('phrases_id_seq'::regclass)
> Indexes:
> "phrases_pkey" PRIMARY KEY, btree (id)
>
>
> However, after switching to the 9.1 server, I got following error
> message:
>
> WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error:
> 0, SQLState: 22003
> ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Bad value
> for type int : lklkh
>
>
> Increasing the log levels provided me with following details:
>
> DEBUG [org.hibernate.SQL] insert into phrases (frequency, language,
> phrase) values (?, ?, ?)
> TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding
> parameter [1] as [INTEGER] - 4
> TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding
> parameter [2] as [VARCHAR] - ger
> TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding
> parameter [3] as [VARCHAR] - lklkh
> WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error:
> 0, SQLState: 22003
> ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Bad value
> for type int : lklkh
>
> Performing the INSERT via CLI worked nicely:
>
> INSERT INTO phrases (frequency, language, phrase) VALUES (4, 'ger',
> 'lklh');
> INSERT 0 1
>
> This led me to the assumption that there was something wrong with
> the JDBC driver or the hibernate layer, but none of the tested
> modifications made the problem go away. When searching for the given SQL
> error code & state, I stumbled on a stackoverflow post (
> *http://stackoverflow.com/questions/25977903/hibernate-columns-mismatch-on-persistance*
> <http://stackoverflow.com/questions/25977903/hibernate-columns-mismatch-on-persistance>),
> and indeed, after I have re-ordered the columns in the table moving the
> id column to the first position, it works without a flaw.
>
> \d phrases
> Table "phrases"
> Column | Type |
> Modifiers
>
> -----------+----------------------+----------------------------------------------------------------------
> id | bigint | not null default
> nextval('phrases_id_seq'::regclass)
> phrase | text |
> frequency | integer | default 4
> language | character varying(3) |
> Indexes:
> "phrases_pkey" PRIMARY KEY, btree (id)
>
> As it took me quite a while to figure out this work around, I
> wanted to share this with the community and ask the question, if you have
> any ideas what the actual root cause is. Please point me to any resources,
> if that is a known and justified behaviour of the database. Otherwise, I
> hope this might help others in similar situations.
>
> Cheers,
> Andreas
>
>
>

On Thu, 2016-04-21 at 09:30 -0400, Dave Cramer wrote:
> This is a bug in their code. They are relying on the presumption that
> id is the first column
> As for pg versions. Use the projects distros we patch them ahead of
> anyone.

Hello,

I haven't used Hibernate for yonks but does the column order in the
phrases.hbm file match the order used in the CREATE TABLE definition?
I have a vague recollection that was important. We used to generate the
hbm files from the SQL create script.

As an aside, if you define column id as:-

id BIGSERIAL NOT NULL PRIMARY KEY USING INDEX TABLESPACE blah,

saves you having to define a sequence and the index as well as making
it pretty obvious exactly what column id does.
Also, an ISO language code of, say, 'en-GB', isn't going to fit in a
VARCHAR(3) sized column. 

Cheers,
Rob

> On Apr 21, 2016 8:45 AM, "Andreas Arens" <andreas(dot)arens(at)list(dot)lu>
> wrote:
> > Dave, 
> >
> > Well, it seems that the PostgreSQL server itself does not log an
> > error, but I get it from the JDBC driver: 
> >
> > Caused by: org.postgresql.util.PSQLException: Bad value for type
> > int : lkjlkjlkj ljlékjlékj lék lkjlék àékj éà àék
> > at
> > org.postgresql.jdbc2.AbstractJdbc2ResultSet.toInt(AbstractJdbc2Resu
> > ltSet.java:2955)
> > at
> > org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2Res
> > ultSet.java:2138)
> > at
> > org.jboss.jca.adapters.jdbc.WrappedResultSet.getInt(WrappedResultSe
> > t.java:1052)
> > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> > [rt.jar:1.7.0_67]
> > at
> > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImp
> > l.java:57) [rt.jar:1.7.0_67]
> > at
> > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcc
> > essorImpl.java:43) [rt.jar:1.7.0_67]
> > at java.lang.reflect.Method.invoke(Method.java:606)
> > [rt.jar:1.7.0_67]
> > at
> > org.hibernate.engine.jdbc.internal.proxy.AbstractResultSetProxyHand
> > ler.continueInvocation(AbstractResultSetProxyHandler.java:104)
> > [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
> >
> > which might be the result from a wrong assumption within the
> > Hibernate layer while evaluating the result set. This clearly
> > supports your view. Weird though that it only appears with 9.1 and
> > not 8.1.
> >
> > I am upgrading to 9.1 as it is the latest version supported by
> > Ubuntu 12.04 LTS. Once this is done, the system will be migrated to
> > 16.04 LTS and then the database to PostgreSQL to 9.5. 
> >
> > Andreas
> >  
> >
> > Dave Cramer ---21/04/2016 14:24:47---Andreas, So did the server
> > have an error after that ? It does not appear to. In
> >
> > From: Dave Cramer <pg(at)fastcrypt(dot)com>
> > To: Andreas Arens <andreas(dot)arens(at)list(dot)lu>
> > Cc: List <pgsql-jdbc(at)postgresql(dot)org>
> > Date: 21/04/2016 14:24
> > Subject: Re: [JDBC] Column order seems to play a role after
> > migration from 8.1 to 9.1 if sequences are used
> > Sent by: pgsql-jdbc-owner(at)postgresql(dot)org
> >
> >
> >
> > Andreas,
> >
> > So did the server have an error after that ? It does not appear to.
> > In which case it is still some problem with hibernate. At this
> > point I suspect hibernate thinks the first value returned is the id
> > which it subsequently tries to put into the id column?
> >
> > PS. Why did you not upgrade to 9.5 ? FYI, 9.2 has significant
> > performance benefits over 9.1
> >
> >
> >
> > Dave Cramer
> >
> > davec(at)postgresintl(dot)com
> > www.postgresintl.com
> >
> > On 21 April 2016 at 08:19, Andreas Arens <andreas(dot)arens(at)list(dot)lu>
> > wrote:
> > Dave,
> >
> > Thanks a lot for your quick reply. 
> >
> > Unfortunately, I don't think your guess is right as the generated
> > statement does not contain the id. Here's what the PostgreSQL
> > server logs:
> >
> > 2016-04-21 14:11:21 CEST LOG:  execute <unnamed>: insert into
> > phrases (frequency, language, phrase) values ($1, $2, $3) RETURNING
> > *
> > 2016-04-21 14:11:21 CEST DETAIL:  parameters: $1 = '4', $2 = 'ger',
> > $3 = 'lkjlkjlkj ljlékjlékj lék'
> >
> > Another point that speaks against this theory is that the same JDBC
> > driver jar works fine with PostgreSQL 8.1 . 
> >
> > Andreas
> >
> > Dave Cramer ---21/04/2016 12:15:24---Andreas, My guess is that
> > hibernate is adding the id parameter into the statement
> >
> > From: Dave Cramer <pg(at)fastcrypt(dot)com>
> > To: Andreas Arens <andreas(dot)arens(at)list(dot)lu>
> > Cc: List <pgsql-jdbc(at)postgresql(dot)org>
> > Date: 21/04/2016 12:15
> > Subject: Re: [JDBC] Column order seems to play a role after
> > migration from 8.1 to 9.1 if sequences are used
> > Sent by: davecramer(at)gmail(dot)com
> >
> >
> >
> >
> > Andreas,
> >
> > My guess is that hibernate is adding the id parameter into the
> > statement and expects it to be the first column.
> >
> > This could be confirmed by looking at the server logs.
> >
> > P.S. Please subscribe to the pgjdbc list
> >
> > Dave Cramer
> >
> > davec(at)postgresintl(dot)com
> > www.postgresintl.com
> >
> > On 21 April 2016 at 04:59, Andreas Arens <andreas(dot)arens(at)list(dot)lu>
> > wrote:
> > Hello, 
> >
> > I don't know if the root cause of my observation is effectively
> > JDBC-related, but I thought it might be the best starting point.
> >
> > TL:DR: After migrating a PosgreSQL DB from 8.1 to 9.1, I had to
> > reorder the columns of a table to make sure the primary key column
> > that is automatically filled by a sequence, is the first one in the
> > table. This was not an issue with 8.1. The problem - during INSERT
> > - only occurs via JDBC. When using the CLI (i.e. psql), it works
> > fine.
> >
> > The details of what I've observed: 
> >
> > In preparing a major system upgrade for a legacy application, I
> > tested the migration of the PostgreSQL server from version 8.1 to
> > 9.1 (on Ubuntu LTS 12.04). On top of the database I use JBoss AS
> > 7.1 with Hibernate, JDBC and Java 1.7.
> >  I tested with different but recent JDBC drivers for the Java 1.7
> > platform (i.e. postgresql-9.3-1102.jdbc41.jar, postgresql-
> > 9.4.1208.jre7.jar). The entity beans are specified to have their
> > primary key (Integer value) generated by the database via a
> > sequence:
> >
> > In the bean:
> >
> >     @Id
> >     @GeneratedValue(strategy = GenerationType.IDENTITY)
> >     @Column(name = "id")
> >     public java.lang.Integer getId()
> >     {
> >         return id;
> >     }
> >
> > In the table:
> >
> > \d phrases
> >                                      Table "phrases"
> >   Column   |         Type         |                            
> >  Modifiers
> > -----------+----------------------+------------------------------
> > ----------------------------------------
> >  phrase    | text                 |
> >  frequency | integer              | default 4
> >  language  | character varying(3) |
> >  id        | bigint               | not null default
> > nextval('phrases_id_seq'::regclass)
> > Indexes:
> >     "phrases_pkey" PRIMARY KEY, btree (id)
> >
> >
> > However, after switching to the 9.1 server, I got following error
> > message:
> >
> > WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error:
> > 0, SQLState: 22003
> > ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Bad value
> > for type int : lklkh
> >
> >
> > Increasing the log levels provided me with following details:
> >
> > DEBUG [org.hibernate.SQL] insert into phrases (frequency, language,
> > phrase) values (?, ?, ?)
> > TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding
> > parameter [1] as [INTEGER] - 4
> > TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding
> > parameter [2] as [VARCHAR] - ger
> > TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding
> > parameter [3] as [VARCHAR] - lklkh
> > WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error:
> > 0, SQLState: 22003
> > ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Bad value
> > for type int : lklkh
> >
> > Performing the INSERT via CLI worked nicely: 
> >
> > INSERT INTO phrases (frequency, language, phrase) VALUES (4, 'ger',
> > 'lklh');
> > INSERT 0 1
> >
> > This led me to the assumption that there was something wrong with
> > the JDBC driver or the hibernate layer, but none of the tested
> > modifications made the problem go away. When searching for the
> > given SQL error code & state, I stumbled on a stackoverflow post (h
> > ttp://stackoverflow.com/questions/25977903/hibernate-columns-
> > mismatch-on-persistance), and indeed, after I have re-ordered the
> > columns in the table moving the id column to the first position, it
> > works without a flaw. 
> >
> > \d phrases
> >                                      Table "phrases"
> >   Column   |         Type         |                            
> >  Modifiers
> > -----------+----------------------+------------------------------
> > ----------------------------------------
> >  id        | bigint               | not null default
> > nextval('phrases_id_seq'::regclass)
> >  phrase    | text                 |
> >  frequency | integer              | default 4
> >  language  | character varying(3) |
> > Indexes:
> >     "phrases_pkey" PRIMARY KEY, btree (id)
> >
> > As it took me quite a while to figure out this work around, I
> > wanted to share this with the community and ask the question, if
> > you have any ideas what the actual root cause is. Please point me
> > to any resources, if that is a known and justified behaviour of the
> > database. Otherwise, I hope this might help others in similar
> > situations. 
> >
> > Cheers,
> >  Andreas
> >
> >

Rob,

Thanks for pointing the different things out. Luckily, we don't have have
to specify the tables through *hbm files in Hibernate anymore. I forwarded
the issue to the Hibernate folks
(https://forum.hibernate.org/viewtopic.php?f=1&t=1043212) I will post
updates in this list when more is known.

Andreas

From: rob stone <floriparob(at)gmail(dot)com>
To: Dave Cramer <davecramer(at)gmail(dot)com>, Andreas Arens
<andreas(dot)arens(at)list(dot)lu>
Cc: List <pgsql-jdbc(at)postgresql(dot)org>
Date: 21/04/2016 21:02
Subject: Re: [JDBC] Column order seems to play a role after migration
from 8.1 to 9.1 if sequences are used

On Thu, 2016-04-21 at 09:30 -0400, Dave Cramer wrote:
> This is a bug in their code. They are relying on the presumption that
> id is the first column
> As for pg versions. Use the projects distros we patch them ahead of
> anyone.

Hello,

I haven't used Hibernate for yonks but does the column order in the
phrases.hbm file match the order used in the CREATE TABLE definition?
I have a vague recollection that was important. We used to generate the
hbm files from the SQL create script.

As an aside, if you define column id as:-

id BIGSERIAL NOT NULL PRIMARY KEY USING INDEX TABLESPACE blah,

saves you having to define a sequence and the index as well as making
it pretty obvious exactly what column id does.
Also, an ISO language code of, say, 'en-GB', isn't going to fit in a
VARCHAR(3) sized column.

Cheers,
Rob

> On Apr 21, 2016 8:45 AM, "Andreas Arens" <andreas(dot)arens(at)list(dot)lu>
> wrote:
> > Dave,
> >
> > Well, it seems that the PostgreSQL server itself does not log an
> > error, but I get it from the JDBC driver:
> >
> > Caused by: org.postgresql.util.PSQLException: Bad value for type
> > int : lkjlkjlkj ljlékjlékj lék lkjlék àékj éà àék
> > at
> > org.postgresql.jdbc2.AbstractJdbc2ResultSet.toInt(AbstractJdbc2Resu
> > ltSet.java:2955)
> > at
> > org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2Res
> > ultSet.java:2138)
> > at
> > org.jboss.jca.adapters.jdbc.WrappedResultSet.getInt(WrappedResultSe
> > t.java:1052)
> > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> > [rt.jar:1.7.0_67]
> > at
> > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImp
> > l.java:57) [rt.jar:1.7.0_67]
> > at
> > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcc
> > essorImpl.java:43) [rt.jar:1.7.0_67]
> > at java.lang.reflect.Method.invoke(Method.java:606)
> > [rt.jar:1.7.0_67]
> > at
> > org.hibernate.engine.jdbc.internal.proxy.AbstractResultSetProxyHand
> > ler.continueInvocation(AbstractResultSetProxyHandler.java:104)
> > [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
> >
> > which might be the result from a wrong assumption within the
> > Hibernate layer while evaluating the result set. This clearly
> > supports your view. Weird though that it only appears with 9.1 and
> > not 8.1.
> >
> > I am upgrading to 9.1 as it is the latest version supported by
> > Ubuntu 12.04 LTS. Once this is done, the system will be migrated to
> > 16.04 LTS and then the database to PostgreSQL to 9.5.
> >
> > Andreas
> >
> >
> > Dave Cramer ---21/04/2016 14:24:47---Andreas, So did the server
> > have an error after that ? It does not appear to. In
> >
> > From: Dave Cramer <pg(at)fastcrypt(dot)com>
> > To: Andreas Arens <andreas(dot)arens(at)list(dot)lu>
> > Cc: List <pgsql-jdbc(at)postgresql(dot)org>
> > Date: 21/04/2016 14:24
> > Subject: Re: [JDBC] Column order seems to play a role after
> > migration from 8.1 to 9.1 if sequences are used
> > Sent by: pgsql-jdbc-owner(at)postgresql(dot)org
> >
> >
> >
> > Andreas,
> >
> > So did the server have an error after that ? It does not appear to.
> > In which case it is still some problem with hibernate. At this
> > point I suspect hibernate thinks the first value returned is the id
> > which it subsequently tries to put into the id column?
> >
> > PS. Why did you not upgrade to 9.5 ? FYI, 9.2 has significant
> > performance benefits over 9.1
> >
> >
> >
> > Dave Cramer
> >
> > davec(at)postgresintl(dot)com
> > www.postgresintl.com
> >
> > On 21 April 2016 at 08:19, Andreas Arens <andreas(dot)arens(at)list(dot)lu>
> > wrote:
> > Dave,
> >
> > Thanks a lot for your quick reply.
> >
> > Unfortunately, I don't think your guess is right as the generated
> > statement does not contain the id. Here's what the PostgreSQL
> > server logs:
> >
> > 2016-04-21 14:11:21 CEST LOG:  execute <unnamed>: insert into
> > phrases (frequency, language, phrase) values ($1, $2, $3) RETURNING
> > *
> > 2016-04-21 14:11:21 CEST DETAIL:  parameters: $1 = '4', $2 = 'ger',
> > $3 = 'lkjlkjlkj ljlékjlékj lék'
> >
> > Another point that speaks against this theory is that the same JDBC
> > driver jar works fine with PostgreSQL 8.1 .
> >
> > Andreas
> >
> > Dave Cramer ---21/04/2016 12:15:24---Andreas, My guess is that
> > hibernate is adding the id parameter into the statement
> >
> > From: Dave Cramer <pg(at)fastcrypt(dot)com>
> > To: Andreas Arens <andreas(dot)arens(at)list(dot)lu>
> > Cc: List <pgsql-jdbc(at)postgresql(dot)org>
> > Date: 21/04/2016 12:15
> > Subject: Re: [JDBC] Column order seems to play a role after
> > migration from 8.1 to 9.1 if sequences are used
> > Sent by: davecramer(at)gmail(dot)com
> >
> >
> >
> >
> > Andreas,
> >
> > My guess is that hibernate is adding the id parameter into the
> > statement and expects it to be the first column.
> >
> > This could be confirmed by looking at the server logs.
> >
> > P.S. Please subscribe to the pgjdbc list
> >
> > Dave Cramer
> >
> > davec(at)postgresintl(dot)com
> > www.postgresintl.com
> >
> > On 21 April 2016 at 04:59, Andreas Arens <andreas(dot)arens(at)list(dot)lu>
> > wrote:
> > Hello,
> >
> > I don't know if the root cause of my observation is effectively
> > JDBC-related, but I thought it might be the best starting point.
> >
> > TL:DR: After migrating a PosgreSQL DB from 8.1 to 9.1, I had to
> > reorder the columns of a table to make sure the primary key column
> > that is automatically filled by a sequence, is the first one in the
> > table. This was not an issue with 8.1. The problem - during INSERT
> > - only occurs via JDBC. When using the CLI (i.e. psql), it works
> > fine.
> >
> > The details of what I've observed:
> >
> > In preparing a major system upgrade for a legacy application, I
> > tested the migration of the PostgreSQL server from version 8.1 to
> > 9.1 (on Ubuntu LTS 12.04). On top of the database I use JBoss AS
> > 7.1 with Hibernate, JDBC and Java 1.7.
> >  I tested with different but recent JDBC drivers for the Java 1.7
> > platform (i.e. postgresql-9.3-1102.jdbc41.jar, postgresql-
> > 9.4.1208.jre7.jar). The entity beans are specified to have their
> > primary key (Integer value) generated by the database via a
> > sequence:
> >
> > In the bean:
> >
> >     @Id
> >     @GeneratedValue(strategy = GenerationType.IDENTITY)
> >     @Column(name = "id")
> >     public java.lang.Integer getId()
> >     {
> >         return id;
> >     }
> >
> > In the table:
> >
> > \d phrases
> >                                      Table "phrases"
> >   Column   |         Type         |
> >  Modifiers
> > -----------+----------------------+------------------------------
> > ----------------------------------------
> >  phrase    | text                 |
> >  frequency | integer              | default 4
> >  language  | character varying(3) |
> >  id        | bigint               | not null default
> > nextval('phrases_id_seq'::regclass)
> > Indexes:
> >     "phrases_pkey" PRIMARY KEY, btree (id)
> >
> >
> > However, after switching to the 9.1 server, I got following error
> > message:
> >
> > WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error:
> > 0, SQLState: 22003
> > ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Bad value
> > for type int : lklkh
> >
> >
> > Increasing the log levels provided me with following details:
> >
> > DEBUG [org.hibernate.SQL] insert into phrases (frequency, language,
> > phrase) values (?, ?, ?)
> > TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding
> > parameter [1] as [INTEGER] - 4
> > TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding
> > parameter [2] as [VARCHAR] - ger
> > TRACE [org.hibernate.type.descriptor.sql.BasicBinder] binding
> > parameter [3] as [VARCHAR] - lklkh
> > WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error:
> > 0, SQLState: 22003
> > ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] Bad value
> > for type int : lklkh
> >
> > Performing the INSERT via CLI worked nicely:
> >
> > INSERT INTO phrases (frequency, language, phrase) VALUES (4, 'ger',
> > 'lklh');
> > INSERT 0 1
> >
> > This led me to the assumption that there was something wrong with
> > the JDBC driver or the hibernate layer, but none of the tested
> > modifications made the problem go away. When searching for the
> > given SQL error code & state, I stumbled on a stackoverflow post (h
> > ttp://stackoverflow.com/questions/25977903/hibernate-columns-
> > mismatch-on-persistance), and indeed, after I have re-ordered the
> > columns in the table moving the id column to the first position, it
> > works without a flaw.
> >
> > \d phrases
> >                                      Table "phrases"
> >   Column   |         Type         |
> >  Modifiers
> > -----------+----------------------+------------------------------
> > ----------------------------------------
> >  id        | bigint               | not null default
> > nextval('phrases_id_seq'::regclass)
> >  phrase    | text                 |
> >  frequency | integer              | default 4
> >  language  | character varying(3) |
> > Indexes:
> >     "phrases_pkey" PRIMARY KEY, btree (id)
> >
> > As it took me quite a while to figure out this work around, I
> > wanted to share this with the community and ask the question, if
> > you have any ideas what the actual root cause is. Please point me
> > to any resources, if that is a known and justified behaviour of the
> > database. Otherwise, I hope this might help others in similar
> > situations.
> >
> > Cheers,
> >  Andreas
> >
> >