"Could not open relation with OID x" while deleting a row

[Sebastjan Trepca]

Hi,

postgres just started to report this error yesterday when I ran a user function which deletes rows in a lot of tables.
I get an error:

"Could not open relation with OID 18789"

The function looks like this:

CREATE OR REPLACE FUNCTION delete_photo(int8)
  RETURNS bool AS
$BODY$
DECLARE
gid RECORD;
photo RECORD;
BEGIN
    select into photo * from photos where "ID"=$1;
    FOR gid in select * from tag where "ObjectID"=$1 LOOP
        UPDATE tags_history SET "TagCount"="TagCount"-1 WHERE "Tag"=gid."Tag" AND "Date"=current_date;
        IF NOT FOUND THEN
            INSERT INTO tag_history ("Tag","TagCount") VALUES (gid."Tag",-1);
        END IF;
    END LOOP;
    
    DELETE FROM comment WHERE "OwnerID"=$1;
    DELETE FROM tag WHERE "ObjectID"=$1 and "Type"='photos';
    DELETE FROM favorite WHERE "ObjectID"=$1 AND "Type"='photos';
    
    FOR gid in select * from rels where "ObjectID"=$1 AND "Role"='pool' LOOP
        UPDATE poster SET "ObjectCount"="ObjectCount"-1 WHERE "GroupID"=gid."OwnerID" AND "Author"=photo."Author";
    END LOOP;
    DELETE FROM rels WHERE "ObjectID"=$1 AND "Role"='pool';
    DELETE FROM rest WHERE "ObjectID"=$1 AND "Type"='photos';
    DELETE FROM rest3 WHERE "ObjectID"=$1 AND "Type"='photos';
    DELETE FROM photos WHERE "ID"=$1; <<<<-------ERROR IS RAISED HERE
    
    RETURN True;

END;


The weird thing is that if I move the last delete statement to the second row, the function starts to work.

Why? What was going on here?

Thanks, Sebastjan

[Jaime Casanova]

do you delete and recreate the table 'photos'? if so then problem was
that the function know a table for its OID and because the OID of
table changed then the function doesn't found it anymore...

when you move the line in the function, the function was recompiled
and the new table's OID was used... a way to know if that guess is
correct is to let the line in its original position and the rerun the
function... if you get the same error then something else is
happennig...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly