Re: Questions about SERIAL type

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.

db02=# create table test (id SERIAL);
NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL
column 'test.id'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'test_id_key' for
table 'test'
CREATE

db02=# select setval('test_id_seq', -200);
ERROR: test_id_seq.setval: value -200 is out of bounds (1,2147483647)

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

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

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.

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 (?)

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

-Tony

db02=# select version();
version
-------------------------------------------------------------
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)