From: "Leif B. Kristensen" <leif@solumslekt.org>
To: pgsql <pgsql-sql@postgresql.org>
Subject: Error from trigger
Date: Thu, 8 Dec 2005 00:06:44 +0100
User-Agent: KMail/1.8.1
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200512080006.44606.leif@solumslekt.org>

Hello,
I have a trigger that will delete records referring to an "events" table=20
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 =3D OLD.event_id;
    DELETE FROM participants WHERE event_fk =3D 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=20
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)
);

I'm doing a little cleanup, and perform this query:

pgslekt=3D> select * from event_citations where source_fk=3D553;
 event_fk | source_fk
=2D---------+-----------
     2600 |       553
     2592 |       553
     2817 |       553
    19919 |       553
    19920 |       553
(5 rader)

Then I do an adjustment in the "sources" table:

pgslekt=3D> update sources set=20
parent_id=3D553,small_text=3D'',large_text=3D'23.04.1745 ved Solum: \"Abrah=
am=20
Niels=F8n Aafos 49 aar 5 dage\"' where source_id=3D554;
UPDATE 1

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

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

I run a quick check:

pgslekt=3D> select * from event_citations where event_fk=3D2600;
 event_fk | source_fk
=2D---------+-----------
(0 rader)

The record seems to have disappeared into thin air. There has not been=20
performed any inserts or updates in the database between the 'update'=20
and the 'delete' above. And the event won't go away.=20

This is hardly a practical problem, because an event that isn't linked=20
to a "person" through the "participants" table will never print=20
anywhere, and the referring "participant" disappeared. But I don't like=20
it anyway.

I'm running PostgreSQL 8.0.4.
=2D-=20
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE