I'm really puzzled ... but first let me give you a general overview.
I did some restructuring in the database by combining 4 tables in two. All tables have simple numerical sequences as primary keys. The tables were actually very similar in pairs. The only reason they were divided into two parts was based on historical data that needed to be imported. Without this split, there would be a lot of redundancy, and conceptually it made sense.
Now, after a lot of work on cleaning the data, now, finally, you can combine them and just use one of the fields as a discriminator. To speak less abstractly, there are companies in the tables. And they are either local residents or not (two classes). They can be easily distinguished by their zip code (discriminator field). These tables are slowly resizing (the sequence is a surrogate key). The other two tables contain normal data attached to these SCDs. Therefore, 4 tables. 2 for local companies and 2 for non-local ones.
These tables are now simplified and consolidated, so now I have only Companyand CompanyData.
In order to be safe and not to lose any historical information, I created two new tables with new sequence fields. The old sequences are stored if after 10 years I understand that something went wrong;)
So far so good.
Restructuring was fairly straightforward; reconnecting the correct records was also not easy. Then I needed to update the application that interacts with this database, which was a bit more work, but still easy. The application uses JPA, using EclipseLink 2.0 on top of - as already mentioned - PostgreSQL 9.0 database.
And here is the weird part:
When I try to insert a new company, I get a double key error stating that this identifier already exists. But this has to be handled by the sequence object ... right?
- . , . , . , . , nextval ( JPA) , .
JPA-Entity:
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "enterprise_id_seq")
@Column(name = "id", nullable = false)
private Integer id;
:
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
:
[...]
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...
[...]
, id 19611, 19659. .
, . ... , : Integer. Long? , .
50 , 50 , , ...
?
:. allocationSize, - 50. , , . 100% - . , ? , , ...