Re: Questions about SERIAL type

reina(at)nsi(dot)edu (Tony Reina) writes:

> I was thinking of re-designing my database schema to use a SERIAL
> value as an indentification across tables (i.e. as a foreign key).
> I've been playing with some example tables and have found the
> following behavior from SERIAL:
>
> (1) I think SERIAL is defined as an int4. However, the upper bound
> seems to be 2^31 - 1 (217483647) not 2^32 - 1. I suppose this is
> because a generic int4 should have one bit for the sign
> (negative/positive). However, shouldn't SERIAL always be a positive
> number? Would it be correct to make it some kind of unsigned int4
> instead?

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.

> (2) The SERIAL number increases even if the transaction was aborted
> (e.g. if a repeated tuple were trying to be inserted into a unique
> table, the transaction fails, but the SERIAL gets incremented).
> I was hoping that VACUUM VERBOSE ANALYZE would somehow reclaim the
> lost SERIAL indicies. So, for example, if I had the table:

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.

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

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