Thread: drop table cascade doesn't drop manual sequences

Hello folks,

I'm new to postgres and I'm using version 8.1

Here's the problem anyway:

If I insert a sequence later on table creation with alter table, drop
table cascade simply doesn't drop this sequence even when I specify
CASCADE.

works
####################

create table bla(id serial);
drop table bla CASCADE;
select * from pg_class were relkind = 'S' => nothing


doesn't
####################

create table bla(id integer);
create sequence bla_id_seq;
alter table bla alter column id set default nextval('bla_id_seq');

drop table bla CASCADE;
select * from pg_class were relkind = 'S' => 'bla_id_seq'


Is this supposed to happen or am I missing something?


Thanks in advance,
Guilherme

On 10/10/07, Guilherme <antoniolo@gmail.com> wrote:
> Hello folks,
>
> I'm new to postgres and I'm using version 8.1
>
> Here's the problem anyway:
>
> If I insert a sequence later on table creation with alter table, drop
> table cascade simply doesn't drop this sequence even when I specify
> CASCADE.

This is normal.

> works
> ####################
>
> create table bla(id serial);
> drop table bla CASCADE;
> select * from pg_class were relkind = 'S' => nothing

Here, the sequence was created as a dependent object of the table.

> doesn't
> ####################
>
> create table bla(id integer);
> create sequence bla_id_seq;
> alter table bla alter column id set default nextval('bla_id_seq');
>
> drop table bla CASCADE;
> select * from pg_class were relkind = 'S' => 'bla_id_seq'

Here the sequence was created independently.  this method is often
used when a sequence needs to be shared by >1 table:

create table bla(id integer);
create table bla2(id integer);
create sequence bla_id_seq;
alter table bla alter column id set default nextval('bla_id_seq');
alter table bla2 alter column id set default nextval('bla_id_seq');

Now, if i drop table bla2 should I lose the sequence that I assigned
to be used by bla?

"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> On 10/10/07, Guilherme <antoniolo@gmail.com> wrote:
>> If I insert a sequence later on table creation with alter table, drop
>> table cascade simply doesn't drop this sequence even when I specify
>> CASCADE.

> This is normal.

In 8.2 and up you can use ALTER SEQUENCE ... OWNED BY ... to establish a
link that will make a manually created sequence go away when its "owner"
column is dropped.  In 8.1 that aspect of SERIAL is hidden magic :-(

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> schrieb:

> "Scott Marlowe" <scott.marlowe@gmail.com> writes:
> > On 10/10/07, Guilherme <antoniolo@gmail.com> wrote:
> >> If I insert a sequence later on table creation with alter table, drop
> >> table cascade simply doesn't drop this sequence even when I specify
> >> CASCADE.
>
> > This is normal.
>
> In 8.2 and up you can use ALTER SEQUENCE ... OWNED BY ... to establish a
> link that will make a manually created sequence go away when its "owner"
> column is dropped.  In 8.1 that aspect of SERIAL is hidden magic :-(

Really no way to recognize with pg_* or information_schema.* ? I can't
believe this, but i don't know a way...


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Andreas Kretschmer <akretschmer@spamfence.net> writes:
> Tom Lane <tgl@sss.pgh.pa.us> schrieb:
>> In 8.2 and up you can use ALTER SEQUENCE ... OWNED BY ... to establish a
>> link that will make a manually created sequence go away when its "owner"
>> column is dropped.  In 8.1 that aspect of SERIAL is hidden magic :-(

> Really no way to recognize with pg_* or information_schema.* ? I can't
> believe this, but i don't know a way...

Well, ALTER OWNED BY works by adding or removing a pg_depend entry, and
if you wanted to get down and dirty you could do that manually in
earlier releases.  I wouldn't recommend it though ...

            regards, tom lane