drop table cascade doesn't drop manual sequences

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