Re: Dump serials as serial -- not a sequence

Rod Taylor <rbt(at)zort(dot)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.

I think we have to reject this patch. The killer problem with turning
serial columns into a separate serial domain type is that it will break
existing clients, which are expecting the column type to look like plain
int4 (or int8 if they are new enough to know about bigserial). As an
example, the ODBC driver will fail to recognize "serial4" as a numeric
datatype at all; I suspect the same is true of JDBC.

We could run around and try to fix the clients --- indeed, I suspect
the ODBC/JDBC drivers will need some kind of generic way of dealing
with domain types. (Anyone for exposing getBaseType as a SQL function?)
However, the lack of domain support in old clients doesn't break those
clients as long as they're dealing with existing schemas, which haven't
got domains ... unless we try to change serial into a domain. Then we'd
definitely be creating a compatibility issue for existing applications.

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.

The reason this isn't good for pg_dump is that inherited serial columns
don't work nicely for pg_dump if it tries to use the column datatype
to track them. What pg_dump wants is to consider only the parent
table to have a true "serial" column, while treating child tables as
plain int columns with a default clause, same as it ever was. But
expressing serial-ness as a datatype causes it to inherit, and then
you have to jump through hoops to stop the child tables from being
treated as having serial columns.

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

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.

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

regards, tom lane