Bug in sequence dependency checking

Started by Ruslan A Dautkhanovalmost 23 years ago3 messagesbugs
Jump to latest
#1Ruslan A Dautkhanov
rusland@scn.ru

Hello all,

PostgreSQL do not remove it's internal dependency when I DROP DEFAULT (it was nextval(...)),
so I still can't remove the sequence. Please see the real example bellow:

isbs=# \d ttPrefixes
Table "public.ttprefixes"
Column | Type | Modifiers
--------+---------+---------------------------------------------------------------
ttpid | integer | not null default nextval('public.ttprefixes_ttpid_seq'::text)
ttid | integer | not null default nextval('public.ttprefixes_ttid_seq'::text)
prefix | text |
descr | text |
dton | date |
dtoff | date |
Indexes: ttprefixes_pkey primary key btree (ttpid)
Foreign Key constraints: $1 FOREIGN KEY (ttid) REFERENCES teltypes(ttid) ON UPDATE NO ACTION ON DELETE
CASCADE

isbs=# alter table ttPrefixes alter ttid DROP DEFAULT;
ALTER TABLE

isbs=# \d ttPrefixes
Table "public.ttprefixes"
Column | Type | Modifiers
--------+---------+---------------------------------------------------------------
ttpid | integer | not null default nextval('public.ttprefixes_ttpid_seq'::text)
ttid | integer | not null
prefix | text |
descr | text |
dton | date |
dtoff | date |
Indexes: ttprefixes_pkey primary key btree (ttpid)
Foreign Key constraints: $1 FOREIGN KEY (ttid) REFERENCES teltypes(ttid) ON UPDATE NO ACTION ON DELETE
CASCADE

isbs=# DROP sequence public.ttprefixes_ttpid_seq;
ERROR: Cannot drop sequence ttprefixes_ttpid_seq because table ttprefixes column ttpid requires it
You may drop table ttprefixes column ttpid instead

isbs=# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.3.2 on i386-unknown-freebsd4.7, compiled by GCC 2.95.4
(1 row)

See last DROP command - it's not executed, but the ttprefixes table don't really "require" the sequence -
I had dropped the DEFAULT! Is it possible to force deletion of such sequences ? Thanks for advance.

--
best regards,
Ruslan A Dautkhanov rusland@scn.ru

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Ruslan A Dautkhanov (#1)
Re: Bug in sequence dependency checking

Ruslan A Dautkhanov <rusland@scn.ru> writes:

PostgreSQL do not remove it's internal dependency when I DROP DEFAULT
(it was nextval(...)), so I still can't remove the sequence.

Ideally we'd prevent you from doing DROP DEFAULT, too. That's mucking
with the internals of the SERIAL implementation. Drop the column if you
don't want it anymore.

regards, tom lane

#3Gaetano Mendola
mendola@bigfoot.com
In reply to: Tom Lane (#2)
Re: Bug in sequence dependency checking

Ruslan A Dautkhanov <rusland@scn.ru> wrote:

Hello all,
PostgreSQL do not remove it's internal dependency when I DROP DEFAULT (it

was nextval(...)),

so I still can't remove the sequence. Please see the real example bellow:

[ CUTTED ]

you did:
alter table ttPrefixes alter ttid DROP DEFAULT;

may be inded you should do
alter table ttPrefixes alter ttpid DROP DEFAULT;

in order to remove the serial
public.ttprefixes_ttpid_seq

Regards
Gaetano