Received: from druid.net (root@druid.net [198.96.119.226]) by hub.org (8.9.3/8.9.3) with ESMTP id MAA80645 for ; Thu, 1 Jul 1999 12:27:12 -0400 (EDT) (envelope-from darcy@druid.net) Received: from localhost (1740 bytes) by druid.net via sendmail with P:stdio/R:bind_hosts/T:inet_zone_bind_smtp (sender: ) (ident using unix) id for ; Thu, 1 Jul 1999 12:26:59 -0400 (EDT) (Smail-3.2.0.104 1998-Nov-20 #4 built 1999-Apr-13) Message-Id: Subject: Re: [SQL] begin/end/abort work for sequences? In-Reply-To: <377B7A2F.26209427@actarg.com> from Kyle Bateman at "Jul 1, 1999 08:24:47 am" To: kyle@actarg.com (Kyle Bateman) Date: Thu, 1 Jul 1999 12:26:59 -0400 (EDT) From: "D'Arcy" "J.M." Cain Cc: pgsql-sql@postgreSQL.org Reply-To: pgsql-sql@postgreSQL.org X-Mailer: ELM [version 2.4ME+ PL50 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Thus spake Kyle Bateman > Do you know if there is a workaround? In my particular situation, it is critical > that all instances of the sequence actually get used. If a transaction is > aborted, I lose an instance and everything gets messed up. I had to solve this once in another RDBMS (Progress) and this is how I did it. I had a table of available sequence numbers consisting of two fields, a name and a number. The table was seeded with 10 entries for each required sequence numbered 1 to 10. When I needed a sequence I started a transaction and grabbed and locked the lowest numbered record for that sequence that wasn't locked. I save the number then add 10 to it. If I abort the transaction, the number remains unchanged. Under this scheme 10 users can be working on the same sequence without locking others out. Numbers can be used out of sequence from time to time if someone aborts but they all get used eventually. -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.