Re: How to generate unique invoice numbers for each day
От | Andrus Moor |
---|---|
Тема | Re: How to generate unique invoice numbers for each day |
Дата | |
Msg-id | BDAF753EE8794B0CA0C08E12B1A49808@server обсуждение исходный текст |
Ответ на | Re: How to generate unique invoice numbers for each day (Andy Colson <andy@squeakycode.net>) |
Ответы |
Re: How to generate unique invoice numbers for each day
Re: How to generate unique invoice numbers for each day |
Список | 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.
В списке pgsql-general по дате отправления: