Обсуждение: Error from trigger
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 INTEGERREFERENCES 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
"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
On Thursday 08 December 2005 00:23, Tom Lane wrote: >Is there a reason you don't just mark the FK reference as ON DELETE >CASCADE, rather than using a handwritten trigger? I could have done that, of course. I'm still a little shaky on "best practice" with these things. Besides, I haven't found out yet how to alter the table to make the reference cascading. And I wanted to experiment with simple triggers. >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. Right on target. Thank you. A few days ago, as I have already related on this list, I did the following to fix a bad design with inherited tables: pgslekt=> create table event_cits ( pgslekt(> event_fk integer references events (event_id), pgslekt(> source_fk integer references sources (source_id), pgslekt(> PRIMARY KEY (event_fk, source_fk) pgslekt(> ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "event_cits_pkey" for table "event_cits" CREATE TABLE pgslekt=> insert into event_cits (select event_fk, source_fk from event_citations); INSERT 0 29139 pgslekt=> drop table event_citations cascade; NOTICE: drop cascades to rule _RETURN on view event_notes NOTICE: drop cascades to view event_notes DROP TABLE pgslekt=> drop table citations; DROP TABLE pgslekt=> alter table event_cits rename to event_citations; ALTER TABLE pgslekt=> \i views_and_functions.sql I've run a VACUUM FULL ANALYZE, but that doesn't seem to cure the situation. Is there a way to fix it, short of a full dump, drop, and reload? -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE
"Leif B. Kristensen" <leif@solumslekt.org> writes:
> On Thursday 08 December 2005 00:23, Tom Lane wrote:
>> 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.
> Right on target. Thank you. A few days ago, as I have already related on
> this list, I did the following to fix a bad design with inherited
> tables:
> ...
> I've run a VACUUM FULL ANALYZE, but that doesn't seem to cure the
> situation. Is there a way to fix it, short of a full dump, drop, and
> reload?
Just starting a fresh session should make the problem go away, or if
that's not practical update the function definition using ALTER FUNCTION
or CREATE OR REPLACE FUNCTION. (You don't need to actually *change*
anything about the function, just issue a command that could change it.)
If that doesn't make the error go away then we need to look more
closely at what's causing it.
regards, tom lane
On Thursday 08 December 2005 05:11, Tom Lane wrote: >Just starting a fresh session should make the problem go away, or if >that's not practical update the function definition using ALTER > FUNCTION or CREATE OR REPLACE FUNCTION. (You don't need to actually > *change* anything about the function, just issue a command that could > change it.) > >If that doesn't make the error go away then we need to look more >closely at what's causing it. I did an "/etc/init.d/postgresql restart" and the problem went away. Thanks again. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE