0

I'm noticing something interesting with hibernate_sequence in my PostgreSQL database. Up until now, the sequence (which is used by all tables in my database) has been incrementing by 1. That's not the case anymore, however.

Currently, hibernate_sequence shows a value of 43270. If I SELECT nextval('hibernate_sequence'); it will correctly report 43271.

When I insert data into my table, however, the sequence ID is now 1872053!

For the life of me, I cannot figure out why but it is affecting some database scripts that I have running to transfer data on a nightly basis between different environments.

I'm hoping it does not have anything to do with the following article which states that declaring @GeneratedValue(strategy=GenerationType.SEQUENCE) "uses HiLo strategy which allocates IDs in blocks of 50 by default." Hibernate use of PostgreSQL sequence does not affect sequence table

If that were the case, would there be such a big jump in sequence ID's? From 43271 to 1872053?

It's almost as if there is a "disconnect" with the hibernate_sequence. When I INSERT a second event, the ID is 1872053 but the hibernate_sequence value is no longer changing (it will still stay at 43421).

Are there any other possible explanations?

Thanks!

Code example:

@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE)
private Integer id;
Community
  • 1
  • 1
littleK
  • 19,521
  • 30
  • 128
  • 188
  • You should show us the source code. But yes, the sequence strategy uses a HILO algorithm. – JB Nizet Sep 05 '12 at 18:54
  • I just find it strange that hibernate_sequence is no longer changing? It's static at 43271. It is not updating after I insert new data. Yet, the new data seems to be on some kind of sequence as it is 1872053, 1872054, 1872055, etc. Also, why would the sequence number jump so high? – littleK Sep 05 '12 at 19:32
  • That's how a HILO algorithm works: it increments the sequence once, and then is able to do N inserts until it reincrements the sequence You don't show us the source code, so ti could also be that Hibernate is not using the sequence you think it should be using. – JB Nizet Sep 05 '12 at 19:34
  • Thanks. I'm not really familiar with hibernate, but I posted some example code in the original question. Are you looking for anything specific (certain config file, etc)? I did a search for "hibernate_sequence" in my projects, and nothing came up. – littleK Sep 05 '12 at 19:46
  • `hibernate_sequence` is the sequence hibernate uses if you don't name an explicit one. I'm wondering if the table has a `serial` field (ie an `integer` with a `DEFAULT nextval('tablename_colname_id_seq')`, and you're somehow using that sequence instead of `hibernate_sequence`. Try `\d tablename` in `psql`, and if there's a default try `select * from tablename_colname_id_seq` (or whatever is in the `nextval(..)` call) to see what the next sequence value is for that sequence. If it's close to the ones you can't explain, that means you're probably getting IDs from the table's own sequence. – Craig Ringer Sep 06 '12 at 01:06
  • Hi Craig, that does not seem to be the case. After performing \d tablename, there is no column of type serial. I should mention that I have altered hibernate_sequence in the past - the scripts that I am running are for a bi-directional data transfer between two databases. In order to avoid conflict, we altered hibernate_sequence on the second environment to 1,000,000, with the sequence on the first environment being 40,000. Since then, we've been transferring data back and forth. Perhaps modifying those sequences (which worked well up until this point) and transferring large/small... – littleK Sep 06 '12 at 11:42
  • ...sequence ID's back and forth could mess something up? – littleK Sep 06 '12 at 11:42

1 Answers1

1

I believe I am experiencing what is described in the following post, which is also related to the HiLo algorithm: hibernate oracle sequence produces large gap

Community
  • 1
  • 1
littleK
  • 19,521
  • 30
  • 128
  • 188