Exception: relation does not exist with postgresql table / sequence in ejb3

Question

I am having error when I use ejb3 and Postgres to insert data. But using raw SQL it goes fine.

My table is:


CREATE SEQUENCE vmb_mails_seq;

CREATE TABLE vmb_mails (
   ID BIGINT DEFAULT nextval('vmb_mails_seq') PRIMARY KEY,
   TITLE TEXT ,
   FROM_ADDR VARCHAR(256), 
   DATE_ARRIVED TIMESTAMP,
   BODY TEXT, 
   ENT_BY    NUMERIC(20,0),
   IS_DELETED NUMERIC(1,0),
   DELETE_DATE TIMESTAMP, 
   MOD_BY    NUMERIC(20,0),
   IS_ACTIVE NUMERIC(1,0), 
   ENT_DATE TIMESTAMP,
   MOD_DATE TIMESTAMP, 
   REACTIVE_DATE TIMESTAMP,
   INACTIVE_DATE TIMESTAMP
);
ALTER SEQUENCE vmb_mails_seq OWNED BY vmb_mails.id;

ejb3 entity is:


@Entity
@Table(name="vmb_mails")
@SequenceGenerator(name="Mails_Seq_Gen",sequenceName="vmb_mails_seq",allocationSize=1)
public class Mail implements Serializable
{
    private long id;
    private String title;
    private String fromAddr;
    private Date dateArrived;
//  private Clob body;
    private String body;

    private long entBy;
    private int isDeleted;
    private Date deleteDate;
    private long modBy;
    private int isActive;   
    private Date entDate;
    private Date modDate;
    private Date reActiveDate;
    private Date inActiveDate;

    public Mail()
    {

    }
    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE,generator="Mails_Seq_Gen")
    @Column(name="ID")
    public long getId() {
        return id;
    }
    public void setId(long id) {
        this.id = id;
    }
    @Column(name="TITLE")

    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    @Column(name="FROM_ADDR")

    public String getFromAddr() {
        return fromAddr;
    }
    public void setFromAddr(String fromAddr) {
        this.fromAddr = fromAddr;
    }
    @Column(name="DATE_ARRIVED")
    @Temporal(TemporalType.TIMESTAMP)
    public Date getDateArrived() {
        return dateArrived;
    }
    public void setDateArrived(Date dateArrived) {
        this.dateArrived = dateArrived;
    }
    @Column(name="BODY")
    public String getBody()
    {
        return body;
    }
    public void setBody(String body)
    {
        this.body = body;
    }
    /*public Clob getBody() {
        return body;
    }
    public void setBody(Clob body) {
        this.body = body;
    }*/
    @Column(name="ENT_BY")

    public long getEntBy() {
        return entBy;
    }
    public void setEntBy(long entBy) {
        this.entBy = entBy;
    }
    @Column(name="IS_DELETED")

    public int getIsDeleted() {
        return isDeleted;
    }
    public void setIsDeleted(int isDeleted) {
        this.isDeleted = isDeleted;
    }
    @Column(name="DELETE_DATE")
    @Temporal(TemporalType.TIMESTAMP)
    public Date getDeleteDate() {
        return deleteDate;
    }
    public void setDeleteDate(Date deleteDate) {
        this.deleteDate = deleteDate;
    }
    @Column(name="MOD_BY")

    public long getModBy() {
        return modBy;
    }
    public void setModBy(long modBy) {
        this.modBy = modBy;
    }
    @Column(name="IS_ACTIVE")

    public int getIsActive() {
        return isActive;
    }
    public void setIsActive(int isActive) {
        this.isActive = isActive;
    }
    @Column(name="ENT_DATE")
    @Temporal(TemporalType.TIMESTAMP)

    public Date getEntDate() {
        return entDate;
    }
    public void setEntDate(Date entDate) {
        this.entDate = entDate;
    }
    @Column(name="MOD_DATE")
    @Temporal(TemporalType.TIMESTAMP)

    public Date getModDate() {
        return modDate;
    }
    public void setModDate(Date modDate) {
        this.modDate = modDate;
    }
    @Column(name="REACTIVE_DATE")
    @Temporal(TemporalType.TIMESTAMP)

    public Date getReActiveDate() {
        return reActiveDate;
    }
    public void setReActiveDate(Date reActiveDate) {
        this.reActiveDate = reActiveDate;
    }
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name="INACTIVE_DATE")

    public Date getInActiveDate() {
        return inActiveDate;
    }
    public void setInActiveDate(Date inActiveDate) {
        this.inActiveDate = inActiveDate;
    }

}

While calling entitmanager.persist(mail) I get this error:

at java.lang.Thread.run(Thread.java:662) Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: org.postgresql.util.PSQLException: ERROR: relation "vmb_mails_seq" does not exist Position: 16 Error Code: 0 Call: select nextval('vmb_mails_seq') Query: ValueReadQuery(sql="select nextval('vmb_mails_seq')") at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:644)

Erwin Brandstetter · Accepted Answer · 2022-10-23 02:53:26Z

7

You can simplify your DDL script with the use of a serial data type. bigserial in your case:

CREATE TABLE vmb_mails (
  id bigserial PRIMARY KEY
, title text,
  -- more columns
);

This creates a sequence named vmb_mails_id_seq automatically (in the same schema).
Also note that sequences have separate privileges.

GRANT INSERT ON vmb_mails TO ..

Does not cover the sequence. Additionally you need:

GRANT USAGE ON SEQUENCE vmb_mails_id_seq TO ..

See:

Explicitly granting permissions to update the sequence for a serial column necessary?

However, the error message says:

relation "vmb_mails_seq" does not exist

And that has some other cause. Did you actually check if the sequence is there? In the right database, at the right port, in the right schema?

edited Oct 23, 2022 at 2:53

answered Jul 3, 2012 at 17:47

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

5 Comments

A.H. Over a year ago

Since the name of the sequence is (or even has to be?) also in the Java code, I think it is better to be explicit about the name in the DDL also.

App Work Over a year ago

Thanks for your reply,It is working in windows , and also was working in centos. but now not working in centos. the same code. I checked the squences are there , I replaced the sequence to Serial. But now it says the relation (table) VMB_MAILS does not exit. I cant figure out what can be the problem.....

Erwin Brandstetter Over a year ago

VMB_MAILS? If you double-quote identifiers at creation, they are case-sensitive and you need to write them the same double-quoted way. Else, all identifiers are cast to lower-case. You know that, right?

Askar Kalykov Over a year ago

@Erwin Brandstetter, I had exactly same problem now in my db. First, I created sequence via pgAdmin, then tried to select newly created sequence, and of course, failed. After reading your comment, I tried something like { select nextval('"SEQ_REVISION_NUMBER"') } (notice double quotes inside single quotes) and it worked!

Erwin Brandstetter Over a year ago

@AskarKalykov: Nice. :) It's my standing advice to only use unquoted lower case identifiers in Postgres. Makes your life easier.

iuv.Li · Accepted Answer · 2021-09-30 05:52:09Z

0

you can try 'CREATE SEQUENCE IF NOT EXISTS vmb_mails_seq;'

answered Sep 30, 2021 at 5:52

iuv.Li

215 bronze badges

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Exception: relation does not exist with postgresql table / sequence in ejb3

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related