Re: Error from trigger

"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