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

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