Обсуждение: serial drop error

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.

Ed

"Ed L." <pgsql@bluepolka.net> writes:
> 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;

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.

            regards, tom lane

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

On Monday December 6 2004 9:45, Ed L. wrote:
> 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;
> > >
> > 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.

Looking into this a little farther, I see from the 7.3.4 docs it's not a
bug, it's a new feature.  Still, I'll whine that I don't see the point of
restricting the re-use/adoption of existing serial-created sequences, and
the restriction is biting me.  Yes, I see there are ways to work around
this in the future.  Doesn't help much managing existing clusters whose
schemas weren't adjusted in the move to 7.3 to account for this design
change.  I can see the point of *not* dropping the sequence unless the
owning column is dropped.  I just don't see the point of disabling the
useful ability to decouple the sequence-column association, and dropping
the default seems the most reasonable way to do that.

Ed

"Ed L." <pgsql@bluepolka.net> writes:
> I can see the point of *not* dropping the sequence unless the
> owning column is dropped.  I just don't see the point of disabling the
> useful ability to decouple the sequence-column association, and dropping
> the default seems the most reasonable way to do that.

Where we part ways is on the claim that this is useful.  As I said
before, if you think they are independent objects then you should create
'em that way.

            regards, tom lane

On Monday December 6 2004 11:50, Tom Lane wrote:
> "Ed L." <pgsql@bluepolka.net> writes:
> > I can see the point of *not* dropping the sequence unless the
> > owning column is dropped.  I just don't see the point of disabling the
> > useful ability to decouple the sequence-column association, and
> > dropping the default seems the most reasonable way to do that.
>
> Where we part ways is on the claim that this is useful.  As I said
> before, if you think they are independent objects then you should create
> 'em that way.

What was I thinking??  I so agree, this would be useless capability for
existing tables.  Being able to decouple the sequence/table dependency is
only ever useful in the absence of foresight to have avoided use of SERIAL
in the first place.  If one lacks that foresight, that's just too bad, they
can just find another way.

Ed