Обсуждение: BUG #15962: Cannot drop table that previously had a 'serial' column
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 ?
Re: BUG #15962: Cannot drop table that previously had a 'serial' column
От
"David G. Johnston"
Дата:
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.
"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