From: | Andreas Arens <andreas(dot)arens(at)list(dot)lu> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Fw: Column order seems to play a role after migration from 8.1 to 9.1 if sequences are used |
Date: | 2016-04-26 09:22:40 |
Message-ID: | OF810A031B.19FFDCF5-ONC1257FA1.00334E80-C1257FA1.00338380@list.lu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
To sum up, it turned out that Hibernate version 4.0.1 had a flaw that
appears to be fixed in the latest version. Have a look into the Hibernate
forum topic for details:
https://forum.hibernate.org/viewtopic.php?t=1043212
Andreas
----- Forwarded by Andreas Arens/LIST on 26/04/2016 11:20 -----
From: Andreas Arens/LIST
To: pgsql-jdbc(at)postgresql(dot)org
Date: 25/04/2016 13:11
Subject: Re: [JDBC] Column order seems to play a role after migration
from 8.1 to 9.1 if sequences are used
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
> >
> >
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Cramer | 2016-04-26 09:24:59 | Re: Fw: Column order seems to play a role after migration from 8.1 to 9.1 if sequences are used |
Previous Message | Craig Ringer | 2016-04-26 03:21:31 | Re: Complicated re-distribution of pgjdbc the "open source way" |