"Could not open relation with OID x" while deleting a row
От | Sebastjan Trepca |
---|---|
Тема | "Could not open relation with OID x" while deleting a row |
Дата | |
Msg-id | cd329af80601060314v6c64982avf1d6dc5dded7c4d8@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: "Could not open relation with OID x" while deleting a row
|
Список | pgsql-general |
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
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
В списке pgsql-general по дате отправления: