Re: pg_dump versus SERIAL, round N

I wrote:
> Also, after thinking about the existing behavior of ALTER TABLE OWNER
> (it tries to keep ownership of dependent sequences equal to the table's
> ownership), we'd have to either abandon that or insist that you can
> only link a sequence to a table having the same owner.  So that's
> another reason for not allowing a sequence to be linked to multiple
> tables --- ALTER TABLE OWNER would inevitably create a mess.

After further reflection on that point, I'm thinking that the ALTER
command should explicitly use the notion of "ownership" rather than
referencing SERIAL as such.  So here's a concrete proposal:
ALTER SEQUENCE sequence_name OWNED BY table_name.column_nameALTER SEQUENCE sequence_name OWNED BY NONE

This requires no keywords we don't already have.  Restrictions would be

* you must have ownership permissions on the sequence

* in the first case, the table and sequence must have identical owners (not necessarily you, consider ownership by a
grouprole) and must be in the same schema.  This maintains invariants that are already preserved by ALTER TABLE.
 

I'm also inclined to change the type of the dependency from INTERNAL
to AUTO.  Per comments in dependency.h:
* DEPENDENCY_AUTO ('a'): the dependent object can be dropped separately* from the referenced object, and should be
automaticallydropped* (regardless of RESTRICT or CASCADE mode) if the referenced object* is dropped.* Example: a named
constrainton a table is made auto-dependent on* the table, so that it will go away if the table is dropped.**
DEPENDENCY_INTERNAL('i'): the dependent object was created as part* of creation of the referenced object, and is really
justa part of* its internal implementation.  A DROP of the dependent object will be* disallowed outright (we'll tell
theuser to issue a DROP against the* referenced object, instead).  A DROP of the referenced object will be* propagated
throughto drop the dependent object whether CASCADE is* specified or not.* Example: a trigger that's created to enforce
aforeign-key constraint* is made internally dependent on the constraint's pg_constraint entry.
 

Basically this change would mean that you'd be allowed to DROP the
sequence with CASCADE (hence removing all the DEFAULT expressions that
use it) without being forced to drop the owning column as such.  That
seems to square better with the idea that the column "owns" the
sequence.  In this new approach I don't think we are considering the
sequence as an integral part of the column's implementation, so
INTERNAL seems too strong.

BTW, will anyone object to doing this now, ie, for 8.2?  I claim it's a
bug fix not a new feature ;-)
        regards, tom lane