0

I'm developing a system that allows to organise paper documents in folders. When folder is full, user hits 'New folder' button. This action calls nextval() of sequence of folders.

Here is how this table of documents looks like:

IDDOC FOLDER PARTNER USER
1     1      1341    56
....
700   2      4532    56

The only problem is that gaps between folder numbers from sequence are pretty big.

Folder numbers I have now: 69, 89, 109, 129, 149, 169, 209.

What should I use instead of sequence to avoid it?

Ruslan
  • 393
  • 1
  • 14

3 Answers3

3

The main purpose of sequences is to provide a source of guaranteed unique identifiers. These are technical keys and generally speaking their actual value should be irrelevant. I mean - do you even need to show the folder number to the user?

If you're that bothered use NOCACHE. That will minimise the gaps you're seeing, and as long as you don't create folders very often you won't notice the performance hit from not caching a slack handful of numbers. You still may get gaps, if a transaction is rolled back or fails for any other reason but they should be infrequent; if they're not you have bigger things to worry about than the numbering of your folders!

Other ways of generating a monotonically incrementing series are either cumbersome to implement (triggers over code control tables) or not guaranteed to be unique (using max(id)+1 in a trigger). If you want to use a code control table - that is a table which specifically tracks the last assigned folder ID - you should have a look at a previous answer I wrote which should how to do implement one. The one advantage of a code control table is that we can maintain counts with a group. So you could have a run of folder ids for each user, and increment them independently.

APC
  • 144,005
  • 19
  • 170
  • 281
  • 1
    Thankh you! I will try NOCHACHE. Folder number is used to store paper documents in archive. Archive employee can easily find a right document when he or she knows a folder number. Small gaps would be ok in this case. – Ruslan Aug 20 '19 at 13:36
  • 1
    @APC The code control table solution you linked to is very nice, but not guaranteed to be gapless for the same reasons a sequence is not gapless: namely, there is no guarantee that the caller puts the returned ID into the table and commits it without errors. I think gapless is usually a poor requirement and one that is often dropped when people realize its high technical costs and low business value. Still, I thought maybe it was worth mentioning since this question was specifically about gapless sequences. – Matthew McPeak Aug 20 '19 at 14:48
3

You can't without forcing your database to be serializable. That will mean very poor throughput due to no parallel updates, i.e. every transaction will have to wait for the one before it (to see if the number is used). Also, no rollbacks so you'll have to select from a counter table (also bad).

You are best to ignore the gaps. If you cannot do that you can do one of two things.

1) Assign the sequence number then periodically go back and fill in the gaps with dummy records.

2) Keep doing what you do and also have an unassigned "real" sequence number that you periodically fill in after the event when all your transactions are committed.

Generally though, the "no gap" requirement is bogus.

LoztInSpace
  • 5,584
  • 1
  • 15
  • 27
  • My experience with these kind of identifiers is that serialization is rarely an issue. Users probably create a folder once a day or once a week; torrents of concurrent updates is just not something that happens. – APC Aug 20 '19 at 13:49
  • Perhaps in this case it's ok but as a general pattern it's not a good option. – LoztInSpace Aug 20 '19 at 14:19
1

As @APC says sequences ensure uniqueness.

If you want to have "serial numbering" for the folder columns, then don't use a sequence. You can store the "last used folder value" on a separate table, and use it manually to get a new number each time you need one. Make sure you raise the transaction level to SERIALIZABLE when you perform these updates.

The Impaler
  • 45,731
  • 9
  • 39
  • 76