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_name
ALTER 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 group role) 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 automatically dropped
* (regardless of RESTRICT or CASCADE mode) if the referenced object
* is dropped.
* Example: a named constraint on 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 just a part of
* its internal implementation. A DROP of the dependent object will be
* disallowed outright (we'll tell the user to issue a DROP against the
* referenced object, instead). A DROP of the referenced object will be
* propagated through to drop the dependent object whether CASCADE is
* specified or not.
* Example: a trigger that's created to enforce a foreign-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