Re: How to generate unique invoice numbers for each day

On 16 Jan 2011, at 18:56, Andrus Moor wrote:

> My Visual FoxPro application works OK in this case.
> I used FLOCK() to lock invoice header table (FLOCK() waits indefinitely until lock is obtained and reads fresh data from disk),
>
> used
>
> SELECT MAX( CAST( SUBSTRING(invoiceno,8) AS INT ) )+1
> FROM invoices
> WHERE date= m.invoice_date
>
> to get next free number, inserted invoice and unlocked the table.

If you really need gapless sequences, then you would do something rather similar in Postgres.

Instead of the whole database file you only lock the file for the table containing the sequence. That's called serialization, something you can't get around if you require gapless sequences. The documentation does a much better job at explaining serialization than I could, I'm sure.
That's also why people were suggesting ways around that requirement, as it's a costly feature with regards to database performance.

There are blogs about how to create gapless sequences in Postgres, so I won't go into detail about them.

> Customer expects Postgres to be more powerful than FoxPro . He don't understand why this stops working after upgrade.

And he is right too! To the customer the database is probably that thing that they put their data in and that does all the magic stuff for them. To you it's that thing that you implement all that magic stuff in! Half of what the customer calls his database is what you implemented.

Your customer doesn't care about details like that though, he cares about a working system. The database (the part that we call the database) does it's thing rather well in 99.99% of the cases - usually when the database (as seen from the customer's point of view) doesn't do what it's supposed to do, the problem is in the business logic that you put in that database.

So to summarise: What's failing her isn't the database, it's you.

Where you failed, heck, I don't know... You probably didn't get all the requirements right, or you forgot to test this particular part of the application, or whatever. Don't fuss about it too much though, it's almost impossible to not fail somewhere with complicated applications like this. Consider it a lesson learned.

Just don't blame the database for it, especially not on a mailing-list about that database ;)

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4d333dbf11702139115944!