Thread: 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