I've got a problem where I have to keep and increment an item number area inside a table, however the dpi ought to be unique within given logical domain, not across table globally.

A good example could be multiple Companies arranging multiple Jobs Job.Number ought to be unique inside a business.

And So I essentially have to make certain that concurrent job creation procedures don't produce Jobs with similar Number.

Presently I see only one method to implement that (in postresql):

Lock the table having a self-securing kind of lock, say "SHARE UPDATE EXCLUSIVE" so the rest of the procedures of the type need to queue and wait, thus making certain that MAX() function always returns unique value.

However there appears to become a huge drawback for the reason that solution - it basically produces a bottleneck for those Place procedures into Jobs table.

I do not think I'm able to use Postgreql sequences, because:

  1. I don't want to produce a new sequence for every start up business
  2. It may have gaps

can you suggest every other ways to cope with this problem?

Thanks!

To begin with, if you just need a definite number, why not make use of a sequence to create it?

If your shared sequence isn't ok, because will generate "gaps" (i.e. Business #1 jobs might be designated 1,2,5,6,23 and Business #2 jobs might get 4,7,8,20 and so forth) or for reasons uknown, why not develop a table of "job counters":

> Business ID  Job Counter

----------------------------

> Business #1  23

> Business #2   3

> Business #3  11

> Business #4  76

So if you need to create the next Project for Business #2 you need to lock just the Business#2 row, increment it, and proceed. Presuming Postgres can lock in the record level, you'd make things more scalable by doing this.

How about Choose * FOR UPDATE statement?

Also I wish to observe that CREATE SEQUENCE includes a "CACHE" option, therefore it can function faster should you worries about some gaps.