Django, accessing PostgreSQL sequence

Question

In a Django application I need to create an order number which looks like: yyyymmddnnnn in which yyyy=year, mm=month, dd=day and nnnn is a number between 1 and 9999.

I thought I could use a PostgreSQL sequence since the generated numbers are atomic, so I can be sure when the process gets a number that number is unique.

So I created a PostgreSQL sequence:

CREATE SEQUENCE order_number_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9999
START 1
CACHE 1
CYCLE;

This sequence can be accessed as a tables having one row. So in the file checkout.py I created a Django model to access this sequence.

class OrderNumberSeq(models.Model):
    """
    This class maps to OrderNumberSeq which is a PostgreSQL sequence.
    This sequence runs from 1 to 9999 after which it restarts (cycles) at 1.
    A sequence is basically a special single row table.
    """
    sequence_name = models.CharField(max_length=128, primary_key=True)
    last_value = models.IntegerField()
    increment_by = models.IntegerField()
    max_value = models.IntegerField()
    min_value = models.IntegerField()
    cache_value = models.IntegerField()
    log_cnt = models.IntegerField()
    is_cycled = models.BooleanField()
    is_called = models.BooleanField()

    class Meta:
        db_table = u'order_number_seq'

I set the sequence_name as primary key as Django insists on having a primary key in a table.

The I created a file get_order_number.py with the contents:

def get_new_order_number():
    order_number = OrderNumberSeq.objects.raw("select sequence_name, nextval('order_number_seq') from order_number_seq")[0]

    today = datetime.date.today()
    year = u'%4s' % today.year
    month = u'%02i' % today.month
    day = u'%02i' % today.day

    new_number = u'%04i' % order_number.nextval
    return year+month+day+new_number

now when I call 'get_new_order_number()' from the django interactive shell it behaves as expected.

>>> checkout.order_number.get_new_order_number()
u'201007310047'
>>> checkout.order_number.get_new_order_number()
u'201007310048'
>>> checkout.order_number.get_new_order_number()
u'201007310049'

You see the numbers nicely incrementing by one every time the function is called. You can start multiple interactive django sessions and the numbers increment nicely with no identical numbers appearing in the different sessions.

Now I try to use call this function from a view as follows:

import get_order_number

order_number = get_order_number.get_new_order_number()

and it gives me a number. However next time I access the view, it increments the number by 2. I have no idea where the problem is.

Answer 1

The best solution I can come up with is: don't worry if your order numbers are sparse. It should not matter if an order number is missing: there is no way to ensure that order numbers are contiguous that will not be subject to a race condition at some point.

Your biggest problem is likely to be convincing the pointy-haired ones that having 'missing' order numbers is not a problem.

For more details, see the Psuedo-Key Neat Freak entry in SQL Antipatterns. (note, this is a link to a book, which the full text of is not available for free).