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
Message-ID: <200512080006.44606.leif@solumslekt.org> (raw)
Hello,
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)
);
I'm doing a little cleanup, and perform this query:
pgslekt=> select * from event_citations where source_fk=553;
event_fk | source_fk
----------+-----------
2600 | 553
2592 | 553
2817 | 553
19919 | 553
19920 | 553
(5 rader)
Then I do an adjustment in the "sources" table:
pgslekt=> update sources set
parent_id=553,small_text='',large_text='23.04.1745 ved Solum: \"Abraham
Nielsøn Aafos 49 aar 5 dage\"' where source_id=554;
UPDATE 1
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
I run a quick check:
pgslekt=> select * from event_citations where event_fk=2600;
event_fk | source_fk
----------+-----------
(0 rader)
The record seems to have disappeared into thin air. There has not been
performed any inserts or updates in the database between the 'update'
and the 'delete' above. And the event won't go away.
This is hardly a practical problem, because an event that isn't linked
to a "person" through the "participants" table will never print
anywhere, and the referring "participant" disappeared. But I don't like
it anyway.
I'm running PostgreSQL 8.0.4.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: leif@solumslekt.org
Subject: Re: Error from trigger
In-Reply-To: <200512080006.44606.leif@solumslekt.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox