Java – different PostgreSQL sequence IDS in the database and JPA

I'm really confused... But first, let me give you a rough overview

I've done some reorganization in the database, merging four tables into two All tables have a simple sequence of numbers as the primary key In fact, these tables are very (very) similar The only reason they are divided into two parts is based on the historical data that must be imported Without this division, there will be a lot of redundancy, which makes sense conceptually

Now, after a lot of work into data cleaning, you can finally merge them, just use one of the fields as a discriminator The talk is less abstract, and the table contains companies They are either local residents or not (two classes) They can be easily distinguished by zip code (authentication field) These tables are slowly changing dimensions (sequences are surrogate keys) The other two tables contain normal data attached to these SCDS Therefore, table 4 2 is a local company and 2 is a non local company

These expressions have been simplified and merged, so I only have company and companydata

For security reasons and without losing any historical information, I created two new tables with new sequence fields After 10 years of preservation of the old sequence, I realized that something was wrong;)

So far, it's very good

Restructuring is fairly easy, and reconnecting the correct entries is also a wise choice Next, I need to update the application interface with this dB. This is more work, but it is still easy The application uses JPA and eclipse link 2.0 - as described above - a PostgreSQL 9.0 database

Here comes a strange part:

When I try to insert a new company, I receive a duplicate key error indicating that the given ID already exists. But this should be handled by the sequence object... If not?

So I did some digging I can verify that subsequent inerts do return duplicate key errors with incremental IDs This means that the sequence logic is normal The only problem is that the current value is too low Therefore, calling nextval (or anything used by JPA) will return an existing ID

I have the following in JPA entity:

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE,generator = "enterprise_id_seq")
@Column(name = "id",nullable = false)
private Integer id;

My sequence looks like this:

test_db=# \d enterprise_id_seq 
      Sequence "public.enterprise_id_seq"
    Column     |  Type   |        Value        
---------------+---------+---------------------
 sequence_name | name    | enterprise_id_seq
 last_value    | bigint  | 19659
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 32
 is_cycled     | boolean | f
 is_called     | boolean | t

My mistake is:

[...]

Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.1.v20100213-
r6600): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PsqlException: ERROR: duplicate key value violates unique constraint "enterprise_pkey"
    Detail: Key (id)=(19611) already exists.
Error Code: 0
Call: INSERT INTO en...

[...]

As you can see, it tries to insert an entity with ID 19611, but the last value of the sequence is 1965 9 This is clearly wrong

I also try to restart all these subsequent application servers to close all open connections and sessions No luck... Another thing I noticed: the field is defined as an integer Should it be longer? This will require considerable changes in the code, and I don't have time to solve this problem

Since I have only 50 entries, I can simply try to run insert 50 times, but I know more exactly what's wrong

What did I miss here?

Update: after some mining, I encountered allocationsize, whose default value is 50 Interestingly, this is very close to the difference in ID I see Due to some tests and irritability, it may not be 100% the same Does it matter? To be honest, I haven't understood the idea behind this background

Solution

Of course, for hibernate, generationtype is used by default Sequence uses the hi / Lo policy, and uses the allocationsize ID at most before the value returned by the database. Set the allocationsize to 1, which should be dtrt

The previous answer to a very similar question: Hibernate generating two different sequence IDs for PostgreSQL insert