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

On 1/6/06, Sebastjan Trepca <trepca(at)gmail(dot)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 ;)