How to create a sequential number service with unique numbers in ASP.NET Core Web API, Entity Framework Core, and PostgreSQL?

Question

Two days ago I posted this question, I don't think I explained the problem right, so I will try to explain it better here.

Let's say I want to create a service, which returns a unique number when a request comes in.

This number should:

• start at 1
• be incremented by 1 each time the service gets called
• the same number should not be given to two users who request the service at the same time
• when the number reaches a certain threshold, the number gets reset back to 1
• I can manually reset it to 1 even if it didn't reach the threshold

Now I need to build that service in an ASP.NET Core Web API, and Entity Framework Core, and Postgres as the database.

Here is an example python code that implement what I need:

class Numbering:
    def __init__(self):
        self.number = 0

    def get_number(self):
        self.number += 1
        return self.number

    def reset(self):
        self.number = 0

num = Numbering()

for i in range(10):
    print(num.get_number())

num.reset()
print("number has been reset")

for i in range(10):
    print(num.get_number())

I want to create the same thing, but at a scale with multiple users requesting this service, no same numbers are given to two unique requests.

Answer 1

As example:

create sequence recycle_seq maxvalue 10  cycle;

create table inv_tbl(inv_no integer generated always as identity PRIMARY KEY, seq_id integer DEFAULT nextval('recycle_seq'));

insert into inv_tbl values (default, default), (default, default), (default, default), (default, default), (default, default), (default, default), (default, default), (default, default), (default, default), (default, default), (default, default);

select * from inv_tbl;
 inv_no | seq_id 
--------+--------
      1 |      1
      2 |      2
      3 |      3
      4 |      4
      5 |      5
      6 |      6
      7 |      7
      8 |      8
      9 |      9
     10 |     10
     11 |      1

The above creates a sequence that will only advance to 10 then will cycle back to 1. Obviously you would want a larger maxvalue. This sequence is then attached to the seq_id column as the DEFAULT value. Column inv_no uses generated always as identity to create a DEFAULT that will increment up to the maximum allowed by the integer type. This means when the 11 rows are inserted the inv_no will keep incrementing while the seq_id will roll over at 10 back to 1.

What this does is keep the mechanism on the database and reduce the need for coding on the client side. If you want to manually reset the sequence then you do:

alter sequence recycle_seq restart

That rolls the sequence back to 1.

For the caveats on using a sequence see here:

https://www.postgresql.org/docs/current/sql-createsequence.html

in the Notes section.