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