glassfish
  1. glassfish
  2. GLASSFISH-701

Sequence generator conflicts with existing primary key values

    Details

    • Type: New Feature New Feature
    • Status: Reopened
    • Priority: Critical Critical
    • Resolution: Unresolved
    • Affects Version/s: 9.0pe
    • Fix Version/s: future release
    • Component/s: entity-persistence
    • Labels:
      None
    • Environment:

      Operating System: All
      Platform: All

    • Issuezilla Id:
      701

      Description

      When I build a persistence unit for a set of entity classes that correspond to
      an existing database (again, in my case, I was using the TRAVEL example database
      that ships with Creator 2 Update 1), I wanted to take advantage of the ability
      to autogenerate primary key values. When I deployed an application using these
      classes the first time, it created a SEQUENCE table for me (good). But the
      starting value it stored in the SEQ_COUNT column was within a range of primary
      key values that were used by existing rows in the database (bad), so I got a
      "duplicate key" error the first time I tried to insert a new row.

      Workaround is to manually adjust the stored sequence number to something larger
      than the highest used key in any of the existing tables represented in this
      persistence unit. But that is something the JPA architecture should do for me,
      so I don't have to.

        Activity

        Hide
        marina vatkina added a comment -

        Feature request

        Show
        marina vatkina added a comment - Feature request
        Hide
        marina vatkina added a comment -

        The requested feature will not be spec compliant if implemented by default (i.e.
        without any extra hint or vendor specific annotation). Users of such
        applications will not know that they are relying on a vendor-specific behavior.

        The spec requires (see 9.1.37) that SEQUENCE has an initial value of 1 if the
        initialValue is not specified. Does specifying the initialValue in the
        annotation solves your problem?

        Regards,
        -marina

        Show
        marina vatkina added a comment - The requested feature will not be spec compliant if implemented by default (i.e. without any extra hint or vendor specific annotation). Users of such applications will not know that they are relying on a vendor-specific behavior. The spec requires (see 9.1.37) that SEQUENCE has an initial value of 1 if the initialValue is not specified. Does specifying the initialValue in the annotation solves your problem? Regards, -marina
        Hide
        craig_mcc added a comment -

        > The requested feature will not be spec compliant if implemented by default (i.e.
        > without any extra hint or vendor specific annotation). Users of such
        > applications will not know that they are relying on a vendor-specific behavior.

        Wow, that's a pretty horrible spec bug, then. I'll forward a pointer to this to
        the JSR-220 expert grup.

        > The spec requires (see 9.1.37) that SEQUENCE has an initial value of 1 if the
        > initialValue is not specified. Does specifying the initialValue in the
        > annotation solves your problem?

        No.

        Consider a scenario where I am building an app in a big enterprise installation
        that has multiple stagings for deploying an updated version of an existing app
        (test, staging, and production). What are the chances that any single
        initialValue will be appropriate for all of them? What happens when I redeploy
        later and the "used" values have changed?

        As it is, the only thing I can see the developer doing is going into the
        SEQUENCE table with a SQL console, and manually changing the value. That means
        several things:

        • They have to understand the cause of this problem in the first place.
        • They have to understand how to construct an appropriate SQL UPDATE
          statement.
        • They have to undertand how to use a SQL command tool to update the
          SEQUENCE table, which is implementation specific.

        That is not an appropriate solution for a Java EE 5 release that bills itself
        as being focused on ease of use.

        Craig McClanahan

        Show
        craig_mcc added a comment - > The requested feature will not be spec compliant if implemented by default (i.e. > without any extra hint or vendor specific annotation). Users of such > applications will not know that they are relying on a vendor-specific behavior. Wow, that's a pretty horrible spec bug, then. I'll forward a pointer to this to the JSR-220 expert grup. > The spec requires (see 9.1.37) that SEQUENCE has an initial value of 1 if the > initialValue is not specified. Does specifying the initialValue in the > annotation solves your problem? No. Consider a scenario where I am building an app in a big enterprise installation that has multiple stagings for deploying an updated version of an existing app (test, staging, and production). What are the chances that any single initialValue will be appropriate for all of them? What happens when I redeploy later and the "used" values have changed? As it is, the only thing I can see the developer doing is going into the SEQUENCE table with a SQL console, and manually changing the value. That means several things: They have to understand the cause of this problem in the first place. They have to understand how to construct an appropriate SQL UPDATE statement. They have to undertand how to use a SQL command tool to update the SEQUENCE table, which is implementation specific . That is not an appropriate solution for a Java EE 5 release that bills itself as being focused on ease of use. Craig McClanahan
        Hide
        marina vatkina added a comment -

        resetting the default owner

        Show
        marina vatkina added a comment - resetting the default owner
        Hide
        Mitesh Meswani added a comment -

        Not targeting for 4.0.

        I think a RFE should be filled against JPA spec to discuss this further.

        Show
        Mitesh Meswani added a comment - Not targeting for 4.0. I think a RFE should be filled against JPA spec to discuss this further.

          People

          • Assignee:
            tware
            Reporter:
            craig_mcc
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated: