Is there a way to use generated Identity PK (UUID as DEFAULT) using Eclipselink on Postgres 14?

238 views Asked by At

I have a database running on postgres 14 RDBMS. This (relatively) late version has been selected specifically because it features UUID DEFAULT generation function.

The application that uses the DB is JAX RS java application running within Payara application container using JPA EclipseLink as its DB layer (ORM). I have configured the application to work with UUIDs as PKs almost without a problem so far as I was only reading the entities (that have UUID primary keys to be exact).

Recently I am implementing a POST endpoint, that is to INSERT a record. Now there is a problem with PK id generation.

The table is defined like this:

CREATE TABLE field (                                                            
    id UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),                     
    ...                                              
);

Until now I had the entity defined like this:

@Entity                                                                         
@Converter(name="uuidConverter", converterClass=UUIDEclipseConverter.class)     
@Table(name="field")                                                  
public class Field {                                                   
                                                                                
    @Id                                                                         
    @GeneratedValue(strategy = GenerationType.IDENTITY)                         
    @Convert("uuidConverter")                                                   
    @Column(nullable = false)                                                   
    private UUID id;                                                

    ...

And it works OK for reading the entites, relating to other entities even the UUIDs work as a part of composite keys of other tables.

But once I tried to insert an entity like this, the query fails. Three different failures for three different generation strategies:

  1. GenerationType.IDENTITY:

Crashes spectacularly with

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.9.payara-p1): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: lastval is not yet defined in this session
Error Code: 0
Call: select lastval()
Query: ValueReadQuery(name="SEQ_GEN_IDENTITY" sql="select lastval()")
...
  • There is no sequence, so the origin of the error is understandable.
  1. GenerationType.TABLE:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.9.payara-p1): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: relation "planner.sequence" does not exist
  Position: 8
Error Code: 0
Call: UPDATE planner.SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
    bind => [2 parameters bound]
Query: DataModifyQuery(name="SEQ_GEN_TABLE" sql="UPDATE planner.SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")
...
  • Again, expecting a sequence, thus the failure.
  1. GenerationType.SEQUENCE: Without much hope I tried the last option.
Internal Exception: org.postgresql.util.PSQLException: ERROR: relation "planner.seq_gen_sequence" does not exist
  Position: 16
Error Code: 0
Call: select nextval('planner.SEQ_GEN_SEQUENCE')
...
  • Understandably a failure occurred.

So I wonder how to achive to utilize the DEFAULT generation for a PK ID the RDBMS provides.

Another approaches I tried so far:

  1. I had an idea just to remove the GeneratedValue annotation. I must admit it sounded quite promising, but failed, because the Eclipselink doesn't know it should utilize the DEFAULT generation the RDBMS provides. And so it tries to INSERT NULL value into a NOT NULL column.
  2. Then I tried to set both insertable and updatable column properties to false which also fails, because JPA needs a writable PK identifier.
  3. So I removed updatable column property and the INSERT worked. But the JPA won't hydrate the autogenerated ID into the entity, so I cannot deterministically recognize which record had just been created, so not an ideal solution as well (I also need to return the ID to my API's consumer for it to be able to address the same record later)

For now I am using a workaround based on stripping the GeneratedValue annotation and generating random UUID at the application side via PrePersist listener, which I don't like, but can live with should there be no better solution. Ie:

@Entity                                                                         
@Converter(name="uuidConverter", converterClass=UUIDEclipseConverter.class)     
@Table(name="field")                                                            
public class Field {                                                            
                                                                                
    @Id                                                                         
    @Convert("uuidConverter")                                                   
    @Column(nullable = false, columnDefinition="uuid")                          
    private UUID id;                                           

    ...

    @PrePersist                                                                 
    private void prePersist() {                                                 
        if (id != null)                                                         
            return;                                                             
        // TODO: I do not like this solution at all                             
        id = UUID.randomUUID();                                                 
    }                                                                           
}   

My question therefore is:

Is there a way to configure EclipseLink JPA (via entity annotation?) to utilize a DEFAULT value provided for a PK column by RDBMS (Posgres 14's gen_random_uuid() function specifically) without the need to generate it manually on the application side?

There surely has to be an elegant solution, has it not?

Versions:

  • Java openjdk version "1.8.0_312"
  • Payara (full) 5.2021.10
  • EclipseLink 2.7
  • PostgreSQL 14.6

(no hibernate, no spring)

The whole stack is running in a docker environment which DNM IMO.

0

There are 0 answers