Re: Dump serials as serial -- not a sequence

Rod Taylor <rbt(at)zort(dot)ca> writes:
> Hmm.. We'll need somekind of marker on the sequence to determine
> whether its a serial or not when (if) dependencies will be able to reach
> sequences within nextval (<seqname>.nextval support). Should the
> is_serial marker remain on sequences (as applied in the patch)?

No, it's not necessary and AFAICS it just offers another way to break
existing clients that look at sequences.

Notice I was recommending that you look for an internal-type dependency.
The only way to create one that links a column and a sequence is a
SERIAL declaration. A link that might someday be created because of
the dependency expression would just be a normal dependency.

> We still need a lot of of the hoop jumping in pg_dump to account for
> PRIMARY KEY's on a serial column. The implicitly created unique index
> at creation of a serial column doesn't allow ALTER TABLE ADD PRIMARY
> KEY() to work nicely -- especially since they generally get the same
> name.

The "serial primary key" hack in analyze.c does make that a bit ugly,
but one simple answer is not to try to dump as a serial if there's a
primary key on the column.

Now that I think about it, having pg_dump produce SERIAL declarations
at all is a big performance loser -- you don't really want the index
to exist while you are loading the table.

A radical answer to that is to get rid of the implied UNIQUE constraint
associated with SERIAL, thus making the combinations "serial unique" and
"serial primary key" actually mean something. I fear this won't fly :-(
but it might be worth proposing. If we have to maintain backwards
compatibility, perhaps pg_dump could DROP the unique constraint just
after making the table, and re-create it (possibly as a primary key
rather than plain unique) when it makes indexes.

regards, tom lane