Re: Roll Back dont roll back counters

--- "Roderick A. Anderson" <raanders(at)tincan(dot)org>
wrote:
> On Thu, 16 Aug 2001, Martijn van Oosterhout wrote:
>
> > Why do people care about holes anyway? I've never
> understood that...
>
> The single seat syndrome? (It's my database and I'm
> the only one using
> it.)

If this is the case then simply use the setval
command, insert with an explicit value in the serial
column, or better yet, don't rollback or abort
transactions :).

> Trying to put intelligence into the field? (I can
> tell the order the
> entries were made in the table using this field.)

You can tell the order the entries were made whether
or not their are "holes" in your sequence. No matter
how many aborted transactions you might have had the
bigger sequence numbers were inserted last :). A
simple "SELECT * FROM my_table ORDER BY
my_serial_field" will happily sort your table
chronologically.

On the other hand, you could probably use a
combination of explicit locks a non-SERIAL integer
primary key, and a select statement like "SELECT
my_primary_key FROM my_table ORDER BY my_primary_key
DESC LIMIT 1" to fetch your current highest primary
key value. You could then add one to this number on
your insert. It would serialize inserts to your table
(not a big deal if you are the only one using it), and
it would require more work (and more discipline) when
programming, but you wouldn't get any holes.

Good Luck,
Jason

__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/