Re: Questions about SERIAL type

"G. Anthony Reina" <reina@nsi.edu> writes:

> Doug McNaught wrote:
> 
> > I don't think PG (or the SQL standard) has any concept of unsigned
> > numbers.  Besides, you can have sequences that have negative values at
> > some points, and even decrement rather than increment.  Some folks may
> > rely on this behavior.
> 
> When I tried setting the current value to -200  I got an error that the
> number was outside of the proper range.

You need to specify MINVALUE, MAXVALUE and INCREMENT explicitly to
play tricks like that.  See the docs for CREATE SEQUENCE.  

If you need it, you should be able to create a sequence that uses the
whole range from -2^31 to 2^31-1 with proper arguments to CREATE
SEQUENCE.

> So I'm not sure how people would be using negative values. It looks like from
> the documentation that the SERIAL type always increments by 1 so I'm not sure
> how they could use decrementing values. Unless, of course, they've changed
> the source code to do this. Perhaps I'm missing something here in the
> documentation (using PG 7.1.3, maybe 7.2beta has changed this?).

You didn't read the right part of the docs.  ;)  See CREATE SEQUENCE
in the SQL reference. 

> > How would this work?  Would the DB have to go through all tables
> > looking for REFERENCES constraints and update those rows referring to
> > a renumbered key?  What if you had a referencing column without a
> > REFERENCES constraint?  What if you had some kind of data external to
> > the database that relied on those primary keys staying the same?  Not
> > practical IMHO.
> >
> 
> Yes, it would have to do this which may be time consuming and possibly
> impractical. However, the VACUUM ANALYZE is doing an aweful lot of processing
> on the tables and the indicies already.

I'd be more concerned about the hairiness and maintainability of the
resulting code, actually.  ;)

> However, perhaps the other thing to do is to not increment the SERIAL value
> on an aborted transaction. I'm not sure why serial has to be incremented if
> the transaction fails. Of course, this won't take care of unused SERIAL
> numbers when DELETEs occur.

The reason we don't do it this way is that the sequence object would
have to be locked for the duration of every transaction that used it.
You'd get a lot of contention on that lock and a big slowdown of the
whole system.  And as you say it wouldn't address the DELETE issue. 

> I'm not sure about other database schemas which depend on the SERIAL values
> remaining the same for external consistency. You could still use an OID in
> that case I should think instead of SERIAL (?)

That's worse if anything.  ;)  

> > > I figure that I should never reach 2^31 - 1 transaction per table even
> > > with many aborted ones; however, I think these would be nice changes.
> >
> > What's going to happen AFAIK is that 64-bit sequences will be
> > available.  It's unlikely that overflow will be an issue with
> > those...  ;)
> >
> 
> That will definitely make overflow unlikely. Perhaps I'm just being too
> paranoid that somehow I'll get to the point where my SERIAL value is maxed
> out but I have large gaps from DELETED/UPDATED/ABORTED transactions.

Seriously, I wouldn't worry about it, unless you're incrementing
thousands of times a second, in which case you're in trouble for a lot 
of other reasons...

-Doug
-- 
Let us cross over the river, and rest under the shade of the trees.  --T. J. Jackson, 1863