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.

The answer is still a Sequence. In your previous question I suggested GENERATED ALWAYS AS IDENTITY which is backed by a sequence. You may be better served just creating the seq_id with a DEFAULT of a predefined sequence. Then you can use the CYCLE parameter with the maxvalue parameter of the sequence to automatically wrap the numbering back to 1. Basically you are looking for a throw away number that can be passed out without concurrency issues. A potential downside is a sequence can have gaps. — Adrian Klaver
– Adrian Klaver, Commented Apr 28, 2024 at 17:50
you are absolutely right, and I searched about it was really good and what I need in the database for more efficiency. — Mohammad Zohar
– Mohammad Zohar, Commented Apr 28, 2024 at 17:57
but what I really want to know is how to approach the problem itself, what is the best and simplest way to deal with it as a numbering system, I mean the algorthim of how to deal with this. maybe a pseudo code or steps would help — Mohammad Zohar
– Mohammad Zohar, Commented Apr 28, 2024 at 18:00

Adrian Klaver · Accepted Answer · 2024-04-28 21:09:45Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related