BUG #15962: Cannot drop table that previously had a 'serial' column

Started by PG Bug reporting formover 6 years ago3 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

The following bug has been logged on the website:

Bug reference: 15962
Logged by: Steven Morris
Email address: steven.morris@hypermediasystems.com
PostgreSQL version: Unsupported/Unknown
Operating system: PostgreSQL 9.3.9 on x86_64-unknown-linux-gnu, com
Description:

I had a table that was defined as ''search_id serial NOT NULL'' which
apparently hit the value limit for ''integer''
Rather than changing the original table from serial/integer To bigint in
case that was not the issue,
I renamed the table and created a new table using ''bigint'' using the same
sequence name like so.. which did work

search_id bigint NOT NULL DEFAULT
nextval('d2_search_history_search_id_seq'::regclass)

But now we have determined that the old/original table/data was no longer
needed and it could be dropped BUT when doing so I got the

ERROR: cannot drop table d2_search_history_2019050 because other objects
depend on it
DETAIL: default for table d2_search_history column search_id depends on
sequence d2_search_history_search_id_seq
HINT: Use DROP ... CASCADE to drop the dependent objects too.
********** Error **********
ERROR: cannot drop table d2_search_history_2019050 because other objects
depend on it
SQL state: 2BP01
Detail: default for table d2_search_history column search_id depends on
sequence d2_search_history_search_id_seq
Hint: Use DROP ... CASCADE to drop the dependent objects too.

So at this point I tried changing it by several ways like below but it still
is insisting that it cannot be dropped without cascade.
I do Not want to drop the sequence since I defined the newer table to use
it.
--CREATE SEQUENCE public.tmp_seq;
--ALTER TABLE public.d2_search_history_20190520 ALTER COLUMN search_id SET
DEFAULT nextval('tmp_seq'::regclass);
--ALTER TABLE d2_search_history_2019050 DROP CONSTRAINT
d2_search_history_pkey;
--ALTER TABLE d2_search_history_2019050 DROP COLUMN search_id;
ERROR: cannot drop table d2_search_history_2019050 column search_id because
other objects depend on it
--ALTER TABLE d2_search_history_2019050 ALTER COLUMN search_id DROP
DEFAULT;

Looking at the properties of the sequence I still see it tied to the older
table
Column d2_search_history_2019050.search_id auto
Column d2_search_history.search_id normal

Any suggestions on how to remove the dependency it still has ? or will I
have to make a New sequence for the new table and call it ?

#2David G. Johnston
david.g.johnston@gmail.com
In reply to: PG Bug reporting form (#1)
Re: BUG #15962: Cannot drop table that previously had a 'serial' column

On Fri, Aug 16, 2019, 4:25 PM PG Bug reporting form <noreply@postgresql.org>
wrote:

The following bug has been logged on the

Looking at the properties of the sequence I still see it tied to the older

table
Column d2_search_history_2019050.search_id auto
Column d2_search_history.search_id normal

Any suggestions on how to remove the dependency it still has ? or will I
have to make a New sequence for the new table and call it ?

Alter sequence?

David J.

Show quoted text
#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: David G. Johnston (#2)
Re: BUG #15962: Cannot drop table that previously had a 'serial' column

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Fri, Aug 16, 2019, 4:25 PM PG Bug reporting form <noreply@postgresql.org>
wrote:

Any suggestions on how to remove the dependency it still has ? or will I
have to make a New sequence for the new table and call it ?

Alter sequence?

ALTER SEQUENCE ... OWNED BY, in particular, should help here.

regards, tom lane