postgres + hibernate, sequence in database is negative, why?

Question

we have a very strange behaviour on our productions system.

1. db: posgres 9.0.13
2. spring: 3.1.2.RELEASE
3. hibernate: 3.5.3-Final
4. clustered tomcat setup with 5 tomcats running the same web app and accessing the same db
5. table 'mails' roundabout 80.000.000 rows

our postgres sequence:

\d mails_id_seq 

        Sequence "public.mails_id_seq"
    Column     |  Type   |        Value        
---------------+---------+---------------------
 sequence_name | name    | mails_id_seq
 last_value    | bigint  | 64728416
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 12
 is_cycled     | boolean | f
 is_called     | boolean | t

 \d mails

 \d mails
                  Table "public.mails"
    Column     |            Type             | Modifiers
---------------+-----------------------------+-----------
 id            | integer                     | not null
 .......

hibernate mapping of the sequence:

in plain old xml

<id name="id" column="id" type="integer">
    <generator class="sequence">
        <param name="sequence">mails_id_seq</param>
    </generator>
</id>

and JPA

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "mails_id_sequence")
private int id;

problem:

we have loads of negative numbers as id column - and i have no clue where they come from.

e.g.

SELECT id from mails order by date desc limit 20; 
     id 
------------- 
 -1061189751 
    64675701 
    64675700 
 -1061183780 
    64675699 
 -1061188135 
 -1061183781 
 -1061183782 
 -1061190524 
    64675698 
    64675697 
    64675696 
 -1061189086 
 -1061183783 
 -1061183784 
 -1061189752 
 -1061188136 
 -1061183785 
 -1061189753 
 -1061189087 
(20 rows) 


select date_trunc('month', date), min(id), max(id) from mails where id < 1 group by 1 order by 1; 
     date_trunc | min | max 
---------------------+-------------+------------- 
 2011-11-01 00:00:00 | -1719265246 | -1697584933 
 2011-12-01 00:00:00 | -1697584932 | -1646904070 
 2012-01-01 00:00:00 | -1646904069 | -1583272560 
 2012-02-01 00:00:00 | -1583272559 | -1535772936 
 2012-03-01 00:00:00 | -1535792803 | -1500721344 
 2012-04-01 00:00:00 | -1500728054 | -1467115794 
 2012-05-01 00:00:00 | -1467125218 | -1435499591 
 2012-06-01 00:00:00 | -1435512613 | -1404295391 
 2012-07-01 00:00:00 | -1404307470 | -1371674696 
 2012-08-01 00:00:00 | -1371685552 | -1339094928 
 2012-09-01 00:00:00 | -1339106811 | -1307258065 
 2012-10-01 00:00:00 | -1307266574 | -1273922095 
 2012-11-01 00:00:00 | -1274667945 | -1244428790 
 2012-12-01 00:00:00 | -1255313231 | -1217789919 
 2013-01-01 00:00:00 | -1217857051 | -1183437272 
 2013-02-01 00:00:00 | -1183488949 | -1153262500 
 2013-03-01 00:00:00 | -1153277010 | -1123106036 
 2013-04-01 00:00:00 | -1123137957 | -1095481993 
 2013-05-01 00:00:00 | -1095498223 | -1067058535 
 2013-06-01 00:00:00 | -1067083223 | -1061174790 

select date_trunc('month', date), count(*) from mails where id < 1 group by 1; 
    date_trunc | count 
---------------------+-------- 
2011-11-01 00:00:00 | 95360 
2011-12-01 00:00:00 | 218360 
2012-01-01 00:00:00 | 294554 
2012-02-01 00:00:00 | 417913 
2012-03-01 00:00:00 | 539100 
2012-04-01 00:00:00 | 569667 
2012-05-01 00:00:00 | 612164 
2012-06-01 00:00:00 | 614076 
2012-07-01 00:00:00 | 657972 
2012-08-01 00:00:00 | 662931 
2012-09-01 00:00:00 | 684952 
2012-10-01 00:00:00 | 663296 
2012-11-01 00:00:00 | 580336 
2012-12-01 00:00:00 | 567856 
2013-01-01 00:00:00 | 732353 
2013-02-01 00:00:00 | 689715 
2013-03-01 00:00:00 | 710528 
2013-04-01 00:00:00 | 652021 
2013-05-01 00:00:00 | 718845 
2013-06-01 00:00:00 | 126313 

we have no idea if this problem is a postgres, hibernate or a mixture of both.

does anybody have any idea where this problem could come from?

any help highly appreciated

cheers marcel

Answer 1

I think you should change the line
<id name="id" column="id" type="integer">
to
<id name="id" column="id" type="long">

because Long.MAX_VALUE = 9223372036854775807 = max_value of the sequence. And Integer.MAX_VALUE only equals 2147483647!

Answer 2

Your id should be a type long

private long id;