To: "Leif B. Kristensen" <leif@solumslekt.org>
cc: pgsql <pgsql-sql@postgresql.org>
Subject: Re: Error from trigger 
In-reply-to: <200512080006.44606.leif@solumslekt.org> 
References: <200512080006.44606.leif@solumslekt.org>
Comments: In-reply-to "Leif B. Kristensen" <leif@solumslekt.org>
	message dated "Thu, 08 Dec 2005 00:06:44 +0100"
Date: Wed, 07 Dec 2005 18:23:05 -0500
Message-ID: <4507.1133997785@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Leif B. Kristensen" <leif@solumslekt.org> writes:
> I have a trigger that will delete records referring to an "events" table 
> upon deletion. I have used it without problems for a number of times:

> CREATE OR REPLACE FUNCTION delete_event_cascade() RETURNS TRIGGER AS $$
> BEGIN
>     DELETE FROM event_citations WHERE event_fk = OLD.event_id;
>     DELETE FROM participants WHERE event_fk = OLD.event_id;
>     RETURN OLD;
> END;
> $$ LANGUAGE plpgsql;

> CREATE TRIGGER delete_event_cascade
> BEFORE DELETE ON events
>     FOR EACH ROW EXECUTE PROCEDURE delete_event_cascade();

> The event_citations table is a cross-reference between events and 
> sources, and goes like this:

> CREATE TABLE event_citations (
>     event_fk            INTEGER REFERENCES events (event_id),
>     source_fk           INTEGER REFERENCES sources (source_id),
>     PRIMARY KEY (event_fk, source_fk)
> );

Is there a reason you don't just mark the FK reference as ON DELETE
CASCADE, rather than using a handwritten trigger?

> And then, as I try to delete event #2600, I get the following message:

> pgslekt=> delete from events where event_id=2600;
> ERROR: relation with OID 1141502 does not exist
> CONTEXT: SQL statement "DELETE FROM event_citations WHERE event_fk= $1 "
> PL/pgSQL function "delete_event_cascade" line 2 at SQL statement

Offhand this looks like you might have dropped and recreated the
event_citations table?  If so it's just the known problem that
plpgsql caches plans and doesn't throw them away when the referenced
objects change.

			regards, tom lane