Re: How to generate unique invoice numbers for each day

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.