From: | Todd Kover <kovert(at)omniscient(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | renaming+recreating table w/default sequence causes dependency seq issue |
Date: | 2012-08-08 00:10:25 |
Message-ID: | 201208080010.q780APDf006070@guinness.omniscient.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
I saw issues around renaming tables and not renaming sequences in the TODO
list but did not see anything about this. Apologies if I missed it.
This is with a 9.1.4 server (enterprisedb download on mac os/x lion) and also
seen on 9.1.3 built from netbsd pkgsrc.
It appears that something is amiss if you try to drop a table that has been
renamed that used to have a default mapping to a sequence:
Given this:
---<snip>---
drop table IF EXISTS foo;
drop table IF EXISTS foo_v26;
create table foo (id serial not null, bar integer );
alter table foo alter column id drop default;
alter table foo rename to foo_v26;
create table foo (id integer not null, bar integer );
alter table foo alter id SET DEFAULT nextval('foo_id_seq');
drop table foo_v26;
---<snip>---
everthing works as expected until the final drop, which says:
jazzhands=> drop table foo_v26;
ERROR: cannot drop table foo_v26 because other objects depend on it
DETAIL: default for table foo column id depends on sequence foo_id_seq
HINT: Use DROP ... CASCADE to drop the dependent objects too.
however...
jazzhands=> \d foo;
Table "public.foo"
Column | Type | Modifiers
--------+---------+--------------------------------------------------
id | integer | not null default nextval('foo_id_seq'::regclass)
jazzhands=> \d foo_v26;
Table "public.foo_v26"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
Interestingly, I can drop table foo without any complaints.
It seems like the dependency did not move (it also seems like its
backwards but that's probably all me).
Sadly, if I move setting the default to after I drop the old table, the
sequence goes away, so I am still digging into a work around.
thanks,
-Todd
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2012-08-08 01:16:18 | Fwd: [BUGS] Small bug in psqlodbc-09.01 prevents interoperability with LISTSERV |
Previous Message | Robert Haas | 2012-08-07 20:34:21 | Re: event triggers patch breaks with -DCLOBBER_CACHE_ALWAYS |