org.postgresql.util.PSQLException: ERROR: relation "sequence" does not exist

Question

I created a Dynamic Web Project with the JPA facet running on Tomcat 9.0.24. I have a single table, called profile, in my Prostgres 10.10 database and I have a single entity "Profile" that I am trying to persist. I am using Eclipse 4.12.0.

The profile table was created using pgAdmin 4 console and it's sequence exists.

Here is my code...

Maven POM:

<dependency>
  <groupId>org.eclipse.persistence</groupId>
  <artifactId>org.eclipse.persistence.jpa</artifactId>
  <version>2.7.4</version>
</dependency>

Profile entity:

package za.co.ezimax.entity;

import java.time.LocalDate;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity()
@Table(name = "profile")
public class Profile {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    @Column(name="first_name", nullable = false, length = 80)
    private String firstName;

    @Column(name="last_name", nullable = false, length = 80)
    private String lastName;

    @Column(name="birth_date", columnDefinition = "DATE",  nullable = false)
    private LocalDate birthDate;

    @Column(name="sex", nullable = false)
    private int sex;

    @Column(name="mobile_mac", nullable = false)
    private String mobileMac;

    @Column(name="mobile_no", nullable = false, length = 12)
    private String mobileNo;

    @Column(name="registration_date", columnDefinition = "DATE", nullable = false)
    private LocalDate registrationDate;

    @Column(name="user_password", nullable = false, length = 80)
    private char[] userPassword;

    public Profile() {
    }
}

Code to persist entity:

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("ezimax");
        EntityManager em = emf.createEntityManager();

        em.getTransaction().begin();

        Profile profile = new Profile();
        profile.setFirstName("Hendre");
        profile.setLastName("Louw");
        profile.setSex(1);
        profile.setMobileMac("ff:ff:ff:ff:ff:ff:ff:ff");
        profile.setMobileNo("+27832000000");
        profile.setRegistrationDate(LocalDate.now());
        profile.setUserPassword("password".toCharArray());

        em.persist(profile);
        em.getTransaction().commit();

        em.close();
        emf.close();

Here is the exception I am getting:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.4.v20190115-ad5b7c6b2a): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: relation "sequence" does not exist
  Position: 8
Error Code: 0
Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
    bind => [50, SEQ_GEN]
Query: DataModifyQuery(name="SEQUENCE" sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:342)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.processExceptionForCommError(DatabaseAccessor.java:1650)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:906)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:970)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:640)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:567)
    at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2096)
    at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:311)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:275)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:261)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelectCall(DatasourceCallQueryMechanism.java:304)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelect(DatasourceCallQueryMechanism.java:284)
    at org.eclipse.persistence.queries.DataModifyQuery.executeDatabaseQuery(DataModifyQuery.java:87)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:914)
    at org.eclipse.persistence.internal.sessions.AbstractSession.internalExecuteQuery(AbstractSession.java:3349)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1895)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1877)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1842)
    at org.eclipse.persistence.sequencing.QuerySequence.update(QuerySequence.java:356)
    at org.eclipse.persistence.sequencing.QuerySequence.updateAndSelectSequence(QuerySequence.java:293)
    at org.eclipse.persistence.sequencing.StandardSequence.getGeneratedVector(StandardSequence.java:75)
    at org.eclipse.persistence.sequencing.DefaultSequence.getGeneratedVector(DefaultSequence.java:168)
    at org.eclipse.persistence.sequencing.Sequence.getGeneratedVector(Sequence.java:267)
    at org.eclipse.persistence.internal.sequencing.SequencingManager$Preallocation_Transaction_NoAccessor_State.getNextValue(SequencingManager.java:487)
    at org.eclipse.persistence.internal.sequencing.SequencingManager.getNextValue(SequencingManager.java:1109)
    at org.eclipse.persistence.internal.sequencing.ClientSessionSequencing.getNextValue(ClientSessionSequencing.java:72)
    at org.eclipse.persistence.internal.descriptors.ObjectBuilder.assignSequenceNumber(ObjectBuilder.java:372)
    at org.eclipse.persistence.internal.descriptors.ObjectBuilder.assignSequenceNumber(ObjectBuilder.java:331)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.assignSequenceNumber(UnitOfWorkImpl.java:532)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.registerNotRegisteredNewObjectForPersist(UnitOfWorkImpl.java:4388)
    at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.registerNotRegisteredNewObjectForPersist(RepeatableWriteUnitOfWork.java:523)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.registerNewObjectForPersist(UnitOfWorkImpl.java:4333)
    at org.eclipse.persistence.internal.jpa.EntityManagerImpl.persist(EntityManagerImpl.java:596)
    at za.co.ezimax.rest.Servlet.doGet(Servlet.java:53)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:634)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:526)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:678)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:860)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1587)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "sequence" does not exist
  Position: 8
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2468)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2211)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:124)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:898)
    ... 55 more

Indeed. The actual type depends on the database, so it's neither portable nor clear what implementation is used. — Kayaman
– Kayaman, Commented Sep 4, 2019 at 11:48
@SimonMartinelli When I change the GenerationType to SEQUENCE, then exception changes to Internal Exception: org.postgresql.util.PSQLException: ERROR: relation "seq_gen_sequence" does not exist — Hendré
– Hendré, Commented Sep 4, 2019 at 12:05

Simon Martinelli · Accepted Answer · 2019-09-04 13:37:00Z

7

As you mentioned you want to use sequences with JPA.

So you have to use the GenerationType.SEQUENCE and configure the name of the sequence:

@Id
@GeneratedValue(
    strategy = GenerationType.SEQUENCE,
    generator = "sequence-generator"
)
@SequenceGenerator(
    name = "sequence-generator",
    sequenceName = "the_sequence_name"
)
private long id;

Replace the_sequence_name with the name of the sequence in PostgreSQL.

answered Sep 4, 2019 at 13:37

Simon Martinelli

36.6k6 gold badges62 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

FourtyTwo Over a year ago

I also had to add allocationSize = 1 to @SequenceGenerator like so @SequenceGenerator( name = "sequence-generator", sequenceName = "the_sequence_name", allocationSize = 1 )

Kayaman · Accepted Answer · 2019-09-04 11:44:22Z

1

In your case GenerationType.AUTO translates to GenerationType.TABLE. The code is expecting that there's a table named SEQUENCE containing the values of the sequences. Since it doesn't exist, EclipseLink can't provide you with generated ids.

Either create the table (and the sequence row in it) or use a different GenerationType.

answered Sep 4, 2019 at 11:44

Kayaman

73.8k6 gold badges91 silver badges128 bronze badges

6 Comments

Hendré Over a year ago

When I change the GenerationType to SEQUENCE, then exception changes to Internal Exception: org.postgresql.util.PSQLException: ERROR: relation "seq_gen_sequence" does not exist @Kayaman

Kayaman Over a year ago

@Hendré if you want autogeneration, look into EclipseLink documentation. Or you can create the sequence yourself.

Hendré Over a year ago

The sequence exists. It was automatically created when I created the table with pgAdmin.

Kayaman Over a year ago

@Hendré is the sequence in the same schema as the table? What's the stacktrace with the sequence approach?

Simon Martinelli Over a year ago

@Hendré Maybe there is also a trigger or default value that uses the sequence. Kind of auto increment. Check this out and if you find such a trigger or default value the GenerationType would be IDENTITY

|

Collectives™ on Stack Overflow

org.postgresql.util.PSQLException: ERROR: relation "sequence" does not exist

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related