Re: Questions about SERIAL type

On 28 Nov 2001, Tony Reina wrote:

> 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

IIRC in 7.2, there's 8 byte sequences and a serial8 pseudotype that
probably uses a signed int8.

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

Yeah, the tradeoff was made to go for the concurrency advantage. If
you need to rollback the sequence value if rollback is performed, you'd
need to wait until it's happened before the next insert would be able
to get the sequence value.

> I was hoping that VACUUM VERBOSE ANALYZE would somehow reclaim the
> lost SERIAL indicies. So, for example, if I had the table:

Ick. That sounds really ugly to me. That seems to be outside what
the system can reasonably be expected to handle. It'd be difficult
to determine the full set of in-database dependencies (say triggers
that do their own sort of integrity checks, views, functions, etc
that may join this field to another table) and probably impossible
to determine out of database ones (printed material, etc...).