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

On 1/6/06, Sebastjan Trepca <trepca@gmail.com> wrote:

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

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 ;)