Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

GenerationType.SEQUENCE: incremented sequence values do not seem to be reflected properly in DBeaver #2056

Open
BertSuffys opened this issue Dec 25, 2024 · 0 comments

Comments

@BertSuffys
Copy link

This is likely not an issue but more a lack of understanding on my behalf, but i just cant seem to find any answers to this. I am keeping track of entity IDs using GenerationType.SEQUENCE for which i create my sequencers manually. Consider the following:

Mapping of my CmsLanguage id:

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "cms_language_seq")
  @SequenceGenerator(name = "cms_language_seq", sequenceName = "cms_language_seq")
  public Long getId() {
    return id;
  }
  public void setId(Long id) {
    this.id = id;
  }

Creation of my CmsLanguage table (DBeaver):

CREATE SEQUENCE cms_language_seq;
CREATE TABLE cms_language (
    id BIGINT PRIMARY KEY default nextval('cms_language_seq'),
    lang_enum VARCHAR(2) NOT NULL UNIQUE,
    cms_configuration_id BIGINT NOT NULL,
    CONSTRAINT fk_cms_configuration FOREIGN KEY (cms_configuration_id) REFERENCES cms_configuration(id) ON DELETE CASCADE
);

Some initial inserts (DBeaver):

delete from cms_language;
delete from cms_languagelang;

SELECT setval('cms_language_seq', 1, false);
SELECT setval('cms_languagelang_seq', 1, false);

INSERT INTO cms_language (id, lang_enum, cms_configuration_id) VALUES (nextval('cms_language_seq'), 'nl', 1);
INSERT INTO cms_language (id, lang_enum, cms_configuration_id) VALUES (nextval('cms_language_seq'), 'fr', 1);
INSERT INTO cms_language (id, lang_enum, cms_configuration_id) VALUES (nextval('cms_language_seq'), 'en', 1);

INSERT INTO cms_languagelang (id, langtext, translated_id, lang) VALUES (nextval('cms_languagelang_seq'), 'Nederlands', 1, 'nl');
INSERT INTO cms_languagelang (id, langtext, translated_id, lang) VALUES (nextval('cms_languagelang_seq'), 'Néerlandais', 1, 'fr');
INSERT INTO cms_languagelang (id, langtext, translated_id, lang) VALUES (nextval('cms_languagelang_seq'), 'Dutch', 1, 'en');

INSERT INTO cms_languagelang (id, langtext, translated_id, lang) VALUES (nextval('cms_languagelang_seq'), 'Frans', 2, 'nl');
INSERT INTO cms_languagelang (id, langtext, translated_id, lang) VALUES (nextval('cms_languagelang_seq'), 'Français', 2, 'fr');
INSERT INTO cms_languagelang (id, langtext, translated_id, lang) VALUES (nextval('cms_languagelang_seq'), 'French', 2, 'en');

INSERT INTO cms_languagelang (id, langtext, translated_id, lang) VALUES (nextval('cms_languagelang_seq'), 'Engels', 3, 'nl');
INSERT INTO cms_languagelang (id, langtext, translated_id, lang) VALUES (nextval('cms_languagelang_seq'), 'Anglais', 3, 'fr');
INSERT INTO cms_languagelang (id, langtext, translated_id, lang) VALUES (nextval('cms_languagelang_seq'), 'English', 3, 'en');

The problem:
Upon running currval('cms_language_seq') and currval('cms_languagelang_seq') in DBeaver, i get 3 and 9 respectively, as expected.
When i then launch my api, and start making inserts via my repositories, everything seems to be working fine. the next sequential ids are being generated. However, when i then request the current sequence values in DBeaver, they have not updated. They remain 3 and 9. It seems as though Hibernate starts off with the initially provided sequence values, makes a copy of those sequences internally, and uses this copy to continue generating sequential ids. A virtual sequence if you will.
Secondly, when i then shut down my application and restart, the "internal" current sequence values do not remain updated as they seemed to be from the last inserts via my repository, but it seems like they are 3 and 9 again. Because entities have now been persisted in my database with higher IDs, i get duplicate primary key value violation errors.

If someone could enlighten me about what i am missing here, it would be highly appreciated.

Thanks in advance,

Bert

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant