Re: Questions about SERIAL type

Doug McNaught wrote:

> "G. Anthony Reina" <reina(at)nsi(dot)edu> writes:
>
>
> You need to specify MINVALUE, MAXVALUE and INCREMENT explicitly to
> play tricks like that. See the docs for CREATE SEQUENCE.
>
>

Ahhh. I found it now. I was looking at the documentation from an older on-line
version of Bruce's book and didn't see the MAXVALUE, MINVALUE stuff. I guess the
default for a serial column is MINVALUE 1, MAX VALUE 2^31-1, INCREMENT +1.

> 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.
>

Okay, yes I can see the lock problem now. That makes sense.

>
>
> >
> > 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...
>
>

I figured that I was just being overly cautious. 2^31 transactions is quite a lot.
With the move to int8 the point should be moot.

p.s.

Now that I look at the CREATE SEQUENCE documentation, it appears to have a CYCLE
flag which wraps the sequence around if it were to reach the MAXVALUE. Does anyone
know if it wraps around to the next unused value? Or, if an index already exists
at SERIAL value = MINVALUE, then will the INSERT get an error about duplicate
insertions?

-Tony