Re: OID vs SERIAL? (Was: Re: [HACKERS] Sequences....)

Bruce Momjian <maillist@candle.pha.pa.us> writes:
>> I think it'd still be a good idea to have a system table containing the
>> mapping from SERIAL columns to (OIDs of) their associated sequences.
>> The atttypmod idea is just a trick to bypass having to do lookups in
>> this table for the most common operations on a SERIAL column.

> But what use would a new system table be, if the atttypmod can do it for
> us?

To handle the less common cases --- in particular, the reverse lookup:
given a sequence, is it the implementation of a SERIAL column somewhere?
(DROP SEQUENCE ought to refuse to drop it if so, I think.)

Also, assuming that we want inherited tables to share the parent's
sequence, I suspect there are cases where you need to be able to
find all the tables sharing a given sequence.  This would be rather
difficult if the only representation was atttypmod fields.  (You
could probably work out something reasonably efficient based on
the assumption that all the tables involved must be related by
inheritance --- but it wouldn't be as easy as a single SELECT,
and it *could not* be done in pure SQL because atttypmod isn't
an SQL concept.)

Basically I think that all this structural information ought to be
explicitly represented in regular SQL data structures where you can
manipulate it (SELECT on it and so forth).  We can use atttypmod as an
internal-to-the-implementation cache to avoid the most common lookup
that we'd otherwise need, but it'd be a design mistake not to have the
information represented in a more conventional form.

It might help to compare this issue to index and inheritance
relationships.  We have explicit representations in the system tables
of the inherits-from and is-an-index-of relationships; if we did not,
many tasks would become much harder.  The backend plays some tricks
internally to avoid constantly having to do fullblown lookups in those
tables, but the tables need to be there anyway.  I say we need to add
an explicit representation of the is-sequence-for-SERIAL relationship
for the same reasons, even if we can install an internal shortcut
that's used by some backend operations.
        regards, tom lane