Re: [HACKERS] DROPping tables with SERIALs

Vadim Mikheev <vadim(at)krs(dot)ru> writes:
> Also note that currently SERIAL doesn't work as
> ppl expect -
> 1. SERIAL should generate value if input value
> is NULL or 0;

No, I think it should *only* substitute for NULL. Why assume
zero is special?

> 2. value generated should be max(this_field) + 1

That's not quite right. If current max(serial_field) is 100, and
I INSERT a tuple that gets serial 101, and then I DELETE that tuple,
should the next insertion be given serial 101 again? No. You do need
the separate sequence object as a record of the highest serial number
ever assigned, regardless of whether that value is still present in the
table.

What you really want is that if a non-null value is inserted into the
serial field, and it is larger than the current readout of the
associated sequence generator, then the sequence should be advanced to
equal that inserted value.

Another question is whether a SERIAL field should automatically be
UNIQUE (ie, create a unique index on it to prevent mistakes in manual
insertion of values for the field). I'm not sure that that should be
forced to happen, but I think that most users would want the uniqueness
constraint. Maybe this just means a documentation change, with "SERIAL
UNIQUE" being shown as the typical usage.

regards, tom lane