Thread: remnants of deleted table causing problems
Hi everyone, Here's a strange little problem. A few days ago I wanted to delete a column from my table named 'event'. I followed the advice in the FAQ (select old data into new table minus the column to delete, rename old table, rename new table, etc.) and all appeared well. I also recreated the sequence that I'd been using. Now I'm getting an error while trying to update a row in a different table! Here's the query: UPDATE location SET state = 'IA' WHERE location_id = 18; and the error: ERROR: Relation 'new_event' does not exist I used 'new_event' as the name of the newly created 'event' table. I subsequently renamed it back to 'event'. I've looked all through the database looking for rogue references to 'new_event', but I can't find any. Anyone have an idea what's going on? -Tim -- Tim Wilson | Visit Sibley online: | Check out: Henry Sibley HS | http://www.isd197.org | http://www.zope.com W. St. Paul, MN | | http://slashdot.org wilson@visi.com | <dtml-var pithy_quote> | http://linux.com
Tim Wilson <wilson@visi.com> writes: > I used 'new_event' as the name of the newly created 'event' table. I > subsequently renamed it back to 'event'. I've looked all through the > database looking for rogue references to 'new_event', but I can't find > any. I'd bet on a referential integrity trigger causing the problem. Up till very recently, RENAME TABLE wasn't smart enough to adjust RI trigger parameters. (Don't recall if the fix is in 7.2 or not; it may only be in development sources.) regards, tom lane
On Tue, May 28, 2002 at 03:03:37PM -0400, Tom Lane wrote: > Tim Wilson <wilson@visi.com> writes: > > I used 'new_event' as the name of the newly created 'event' table. I > > subsequently renamed it back to 'event'. I've looked all through the > > database looking for rogue references to 'new_event', but I can't find > > any. > > I'd bet on a referential integrity trigger causing the problem. Up till > very recently, RENAME TABLE wasn't smart enough to adjust RI trigger > parameters. (Don't recall if the fix is in 7.2 or not; it may only be > in development sources.) So I should drop the 'location_exists' constraint in the 'event' table and recreate it? -Tim -- Tim Wilson | Visit Sibley online: | Check out: Henry Sibley HS | http://www.isd197.org | http://www.zope.com W. St. Paul, MN | | http://slashdot.org wilson@visi.com | <dtml-var pithy_quote> | http://linux.com
Tim Wilson <wilson@visi.com> writes: >> I'd bet on a referential integrity trigger causing the problem. Up till >> very recently, RENAME TABLE wasn't smart enough to adjust RI trigger >> parameters. (Don't recall if the fix is in 7.2 or not; it may only be >> in development sources.) > So I should drop the 'location_exists' constraint in the 'event' table > and recreate it? If it is RI triggers, you'll need to drop the triggers manually --- there's no support for this in ALTER TABLE :-(. I believe you can find some discussion of how to do it on techdocs.postgresql.org. regards, tom lane