Re: guaranteeing that a sequence never skips

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sunday 03 October 2004 10:21 am, Scott Marlowe wrote:
> On Sun, 2004-10-03 at 08:58, David Garamond wrote:
> > Am I correct to assume that SERIAL does not guarantee that a sequence
> > won't skip (e.g. one successful INSERT gets 32 and the next might be 34)?
> >
> > Sometimes a business requirement is that a serial sequence never skips,
> > e.g. when generating invoice/ticket/formal letter numbers. Would an
> > INSERT INTO t (id, ...) VALUES (SELECT MAX(col)+1 FROM t, ...) suffice,
> > or must I install a trigger too to do additional checking?
>
> You will have to lock the whole table and your parallel performance will
> be poor.
>

There was a thread about this a while back. I'm using a separate counter table
and stored procs that increment the value of the counter - similar to nextval
used for sequences. My "nextval" locks the "counterrow" in question using
"...for update". So while I'm generating the record that requires the
sequential number I'm in the same stored proc and therefor in a transaction.
If I have to roll back, the counter number in the countertable will roll back
too. You just have to make sure your routine to completely generate whatever
you have to generate doesn't take long, because parallel uses of the same
thing will block until your proc commits or rolls back.

UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFBYD6KjqGXBvRToM4RAgFOAKCeJnwA6PnXquCrUMwGbR9tQZBxdgCdGqyy
nwNbHafAiInSX+WTh5Uzb4o=
=Uixo
-----END PGP SIGNATURE-----