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

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