Received: from www.actarg.com (root@[209.180.91.25]) by hub.org (8.9.3/8.9.3) with ESMTP id TAA37962 for ; Thu, 1 Jul 1999 19:24:28 -0400 (EDT) (envelope-from kyle@actarg.com) Received: from actarg.com (root@tao.actarg.com [192.168.1.1]) by www.actarg.com (8.8.7/8.8.7) with ESMTP id RAA21840 for ; Thu, 1 Jul 1999 17:27:18 -0600 Received: from actarg.com (kyle@chi.actarg.com [192.168.2.16]) by actarg.com (8.8.7/8.8.7) with ESMTP id RAA14718 for ; Thu, 1 Jul 1999 17:24:09 -0600 Sender: kyle@actarg.com Message-ID: <377BF9E4.975A808E@actarg.com> Date: Thu, 01 Jul 1999 17:29:40 -0600 From: Kyle Bateman Organization: Action Target Inc X-Mailer: Mozilla 4.6 [en] (X11; I; Linux 2.0.36 i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgreSQL.org Subject: Re: [SQL] begin/end/abort work for sequences? References: Content-Type: multipart/alternative; boundary="------------364845F3B45B2A056A58F9CA" --------------364845F3B45B2A056A58F9CA Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit "D'Arcy J.M. Cain" wrote: > 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. Thanks for taking the time to reply! -- ---------------------------------------------------- Kyle Bateman President, Action Target Inc. "Viva Yo!" kyle@actarg.com (801)377-8033x101 ---------------------------------------------------- --------------364845F3B45B2A056A58F9CA Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit "D'Arcy J.M. Cain" wrote:
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 <darcy@{druid|vex}.net>   |  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.

Thanks for taking the time to reply!
-- 
 ----------------------------------------------------
 Kyle Bateman       President, Action Target Inc.
 "Viva Yo!"         kyle@actarg.com (801)377-8033x101
 ----------------------------------------------------
  --------------364845F3B45B2A056A58F9CA--