Received: from druid.net (druid.net [216.126.72.98]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBOEwS643549 for ; Sun, 24 Dec 2000 09:58:28 -0500 (EST) (envelope-from darcy@druid.net) Received: from localhost (2655 bytes) by druid.net via sendmail with P:stdio/R:bind_hosts/T:inet_zone_bind_smtp (sender: ) (ident using unix) id for ; Sun, 24 Dec 2000 09:57:21 -0500 (EST) (Smail-3.2.0.109 1999-Oct-27 #4 built 2000-Nov-20) Message-Id: From: darcy@druid.net (D'Arcy J.M. Cain) Subject: Re: Invoice number In-Reply-To: <200012211750.eBLHohP12000@linda.lfix.co.uk> "from Oliver Elphick at Dec 21, 2000 05:50:43 pm" To: Oliver Elphick Date: Sun, 24 Dec 2000 09:57:21 -0500 (EST) CC: Mike Castle , PostgreSQL-SQL X-Mailer: ELM [version 2.4ME+ PL78 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Archive-Number: 200012/268 X-Sequence-Number: 605 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 | 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.