Обсуждение: 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