Re: Dump serials as serial -- not a sequence

On Sat, 2002-08-17 at 14:16, Tom Lane wrote:
> Rod Taylor <rbt@zort.ca> writes:
> > - Add an is_serial boolean attribute to sequence tables.
> > - Add serial4 and serial8 domains with respective serial and bigserial
> > aliases (similar to int4 and int8 to integer and bigint)
> > - Jump through hoops to deal with a primary key'd serial column, alter
> > table add primary key doesn't work for these.
> > - Fix type sanity checks to account for domains.
> > End result, pg_dump no longer uses sequences for serials and psql shows
> > serials rather than integer columns.

> The bottom line is that I do not think it's worth the trouble to change
> serial into a domain, because the entire motivation for this was to make
> pg_dump simpler, and a look at your patch shows that it's made pg_dump's
> life harder not easier.

I was also trying to make it easier for frontends to determine an
auto-incrementing column from one with a simple default. Right now the
test is a default with nextval() in it.  However, I could also
accommodate that with a simple pg_is_serial(table oid, attnum) style
function -- pg_dump can use it too I suppose.

> I think the right way to attack this is to leave the backend code and
> database representation for serials alone.  pg_dump should be taught to
> recognize a serial-column association on the basis of there being an
> internal-type dependency between an int column and a sequence.  Since
> the dependency only exists between the sequence and the original parent
> table, there's no problem with doing the wrong thing for child tables.
> (Since int columns are probably more common than sequence objects,
> I'd be inclined to do the join against pg_depend only when looking at
> a sequence object, and if we get a match then to look through pg_dump's
> internal list of tables to find and mark the corresponding column.)

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

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 hoops pushed PRIMARY KEY definition back into the CREATE
TABLE statement when required.

> psql's \d command could also be made to recognize serials the same
> way, though I am inclined to leave it alone.  I rather like the fact
> that \d shows me the DEFAULT expression for a serial, because that
> way I can easily see the name of the underlying sequence, in case
> I want to do manual operations on the sequence.

I didn't change the \d command in psql with this version of the patch
for the very reasons you mentioned.

> Do you want to attack it this way, or shall I?

I'll see what I can come up with based on your comments to this message.

All of that said, the corrections to the type definition regress tests
should probably be corrected to account for domains anyway -- will
submit as independent patch.