3 Replies Latest reply on Jun 5, 2006 5:33 PM by Chris DeCenzo

    @SequenceGenerator problems with psql

    Chris DeCenzo Newbie

      I'm using jboss 4.0.4 and RC7.

      I'm trying to use my psql sequence to generate the id column in my table. While an id is getting generated for me, its not coming from my sequence and I can't figure out where its coming from since psql doesn't show any other sequences existing.

      Here's a snippet of my code:

      @Entity
      @Table(name="ptxapp",
      uniqueConstraints = {@UniqueConstraint(columnNames={"app", "name"})}
      )
      @SequenceGenerator(name = "SEQUENCE", sequenceName = "ptxapp_id_seq")
      public class AppProperty implements Serializable {

      Integer id = null;
      private String app;
      private String name;
      private String value;


      @Id
      @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQUENCE")
      public Integer getId() { return id; }

      ...
      }

      And here's the psql code I use to create the sequence and the table:

      CREATE SEQUENCE ptxapp_id_seq;
      CREATE TABLE ptxapp (
      id integer DEFAULT nextval('ptxapp_id_seq') UNIQUE NOT NULL,
      app VARCHAR(255) NOT NULL,
      name VARCHAR(255) NOT NULL,
      val VARCHAR(255) NOT NULL,
      PRIMARY KEY( app, name )
      );


      If I change the sequenceName in the @SequenceGenerator annotation, then I see a new sequence with the new name get created so I know it is seeing my sequence.

      What's confusing is that the ids generated by ejb when 'ptxapp_id_seq' is specified are not the next in sequence from 'ptxapp_id_seq', and subsequent calls to nextval('ptxapp_id_seq') using the psql shell do not reflect any advancement of the sequence despite ejb inserting rows with new id values.

      I'm new to ejb3 and would appreciate any advice if I've misunderstood how @SequenceGenerator and @GeneratedValue are supposed to work with an existing psql sequence. Or does this sound like a bug?