From: darcy@druid.net
To: Oliver Elphick <olly@lfix.co.uk>
Cc: Mike Castle <dalgoda@ix.netcom.com>
Cc: PostgreSQL-SQL <pgsql-sql@postgresql.org>
Subject: Re: Invoice number
Date: Sun, 24 Dec 2000 09:57:21 -0500 (EST)
Message-ID: <m14ACaP-000AWoC@druid.net> (raw)
In-Reply-To: <200012211750.eBLHohP12000@linda.lfix.co.uk>
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] AND
seqnum = (SELECT MIN(seqnum) FROM numbers
WHERE seqname = [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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: darcy@druid.net, olly@lfix.co.uk, dalgoda@ix.netcom.com
Subject: Re: Invoice number
In-Reply-To: <m14ACaP-000AWoC@druid.net>
References: <200012211750.eBLHohP12000@linda.lfix.co.uk> <m14ACaP-000AWoC@druid.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox