Re: How to generate unique invoice numbers for each day

From: "Andrus Moor" <kobruleht2(at)hot(dot)ee>
To: "Andy Colson" <andy(at)squeakycode(dot)net>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: How to generate unique invoice numbers for each day
Date: 2011-01-16 17:00:58
Message-ID: BDAF753EE8794B0CA0C08E12B1A49808@server
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Andy,

>> SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7),
>> '^[0-9]*'),'')::int),0)+1
>> FROM invoice
>> where date= ?invoicedate
>>
>> is used to get next free invoice number if new invoice is saved.
>>
>> If multiple invoices are saved concurrently from different processes,
>> they will probably get same number.

> I understand this is a business rule, and you cant change it.

Yes. This is customer requirement and I cannot change it.
Is it reasonable/how to implement the following:

1. plpgsql command obtains some lock
2. It uses

SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7),
'^[0-9]*'),'')::int),0)+1
FROM invoice
where date= ?invoicedate

to get next number for invoice date day

3. It adds new invoice with this numbder to database

4. It releases the lock.

Or is it better to maintain separate sequence or separate table of free
numbers for every day ?

using sequence approach:

Application checks for sequence name in form invoiceno_yyyymmdd
if sequence does not exist it is created. For concurrent adding second
caller gets sequence exists exception and in this case this query can
re-executed.
Next value is obtained from sequence

sequneces older than 1 year should be purded automatically since max 1 year
backward numbers may created.
Which is best solution ?

using new record approach:

1. lock table
2. in this day sequnece does notr exist, add it
3. get next value for day, increment number in this table
4. unlock the table.

Which command should be used to obtain exclusise write access to table (lock
some reosurce or semaphore) ?

Andrus.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sefer Tov 2011-01-16 17:14:55 Efficient count(distinct x) query question
Previous Message Alban Hertroys 2011-01-16 16:51:45 Re: Record with a field consisting of table rows