Re: Invoice number

Thus spake Oliver Elphick
>   >If so, why is no rollbackable an issue?  All you should need is unique
>   >numbers.  Not necessarily exactly sequential numbers.
> 
> For invoice numbers, it matters.
> 
> Numbers missing from such a sequence are likely to provoke questions from
> auditors and taxmen; why borrow trouble?

I solved this exact problem once before in another database (Progres) but
it should work here too.  What I did was select a maximum number of
concurrent transactions (I picked 10) and created a table with two
columns, a token and a number.  For every sequence that I needed I
seeded the table with 10 rows each with the name of the sequence so
that my next routine could reference it and with the number column
set from 1 to 10.  When I need a new number I simply find the smallest
number for that token, lock the row, use the number in my work and
commit everything when I was done.  In Postgres I was able to scan
through each number in order stopping at the first unlocked one so the
process didn't block.  Not sure how to apply that here.  Perhaps a
third column that you plug in your process ID or something so that you
do something like this.

UPDATE numbers SET reserve = [my ID]   WHERE seqname = [token] ANDseqnum = (SELECT MIN(seqnum) FROM numbers
WHEREseqname = [token] AND            reserve IS NULL);
 

You would have to have some way of cleaning these up pretty quickly if your
app crashed or failed to set reserve back to NULL.  The other issue here is
that strictly speaking you may not get your numbers sequentially but you
won't skip any.  In my experience the accountants have been OK with that.

Hmmm.  Perhaps "SELECT ... FOR UPDATE" would work instead of that extra
column.  Still need a way of skipping locked records though.

Perhaps in a future version of PostgreSQL we can have a first class serial
type that handles all of this.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.