Re: serial drop error

On Sun, 05 Dec 2004 14:54:38, Tom Lane wrote:
> On Sunday December 5 2004 12:34, Ed L. wrote:
> > The following queries result in a dropped sequence, but IMO should not:
> >
> > create table foo(id serial);
> > create table bar(id integer not null nextval('foo_id_seq'::text));
> > alter table foo alter column id drop default;
> > drop table foo;
> >
> > Once dependence between foo and foo_id_seq has been removed, a drop of
> > foo should not drop foo_id_seq, particularly if someone else is using
> > it as a default. This occurs in 7.3.4 and 7.4.6.
>
> I don't think that follows at all. The sequence is associated with the
> column because of use of the "serial" declaration; dropping the default
> expression doesn't change that.
>
> If you want to use a single sequence to feed two different columns,
> declare it as an object in its own right. If I were to change anything
> at all about the above example, it would to find a way to disallow you
> from referencing the foo.id sequence for bar.id, because you are poking
> into the internals of the SERIAL abstraction when you do that.

I can see the logic of your argument. But I argue it is more consistent
(and practical) to view this the other way around: it is the SERIAL
abstraction that is (usefully) poking into DBA's domain. My sense of
SERIAL usage has always been that it was syntactic sugar to make sequence
creation and setup easier, but that it is not designed to create an
iron-clad barrier to manipulating the underlying sequence as one can do
with other sequences. The automatic deletion of the sequence object
created via SERIAL was more very useful help along these lines, but again,
should become (and not designed to become?) an iron-clad barrier, IMO.
Making it an iron-clad rule that one cannot re-use sequence/adopt sequence
objects created by serial seems to me to be a needless and
counter-productive restriction, destroying some very useful admin paths
(e.g., sharing the sequence). The great help of autodeletion just needs to
be a little smarter.

Ed