Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

Your id should be a type long

private long id;
share|improve this answer
 
thanks for your post. what i don't get is that the current value is 64728416 which should easily fit into the INT value. any ideas how this can currently go wrong and produce these negative numbers? –  Marcel Jun 11 at 8:13
add comment

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!

share|improve this answer
 
thanks for your post. what i don't get is that the current value is 64728416 which should easily fit into the INT value. any ideas how this can currently go wrong and produce these negative numbers? –  Marcel Jun 11 at 7:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.