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


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

"Ed L." <pgsql (AT) bluepolka (DOT) net> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

On Sun, 05 Dec 2004 14:54:38, Tom Lane wrote:
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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

"Ed L." <pgsql (AT) bluepolka (DOT) net> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Monday December 6 2004 11:50, Tom Lane wrote:
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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html