Category: Software Development

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, 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. I am really curious about what the actual root cause is. I hope this might help others in similar situations.

While testing a few tools to quickly get useful statistics on SVN repository, I stumbled on StatSVN, which I liked the most. As it is written in Java, it should run on most systems. Just follow the instructions of the Quick Start section in the Wiki to create comprehensive statistics of your projects.

As StatSVN works on the svn log file you have to create for your checked out modules, it might make sense to create the logs only for a specific period of time:

svn log -v --xml -r {2011-06-30}:{2011-01-01} > log.xml

It is also possible to create joined statistics for several SVN modules. If you have a bunch of modules with branches you may want to make sure that you only check out the trunk portion of the modules and not all branches. For this, check out all the modules you have in the SVN repository but only the first level:

svn checkout --set-depth immediates;

This should give you the wanted directory structure. Now ”cd” into each module directory and check out the ”trunk” part of each module:

cd MODULE_DIR
svn update --set-depth immediates;
cd trunk;
svn update --set-depth infinity;

After creating the appropriate svn log file you can generate the statistics by invoking StatSVN, e.g.

java -jar /path/to/statsvn.jar /path/to/module/logfile.log /path/to/module