Hello -
I'm using PG 8.2.4.
I am executing a function which includes the code fragment shown below. It
goes through the loop and then fails with the error message
ERROR: relation with OID 591161 does not exist
I'm using the serial data type as a way to record the order of the records
based on a specific sort. I thought that by dropping the table and
recreating it that I would start off with a fresh counter each time. I
suspect that I do not understand something about how the SQL is compiled and
stored, and that the OID is related to this.
I have included the output below the code.
Any guidance would be appreciated.
Thank you.
david
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
FETCH classIDCursor INTO rec; -- get first record;
WHILE FOUND LOOP
numClassesRead = numClassesRead + 1;
classIdArray = rec.class_id_dom;
currentClassId = classIdArray[1];
-- drop & recreate table for sorting
drop table if exists dom1_classid_sorted_temp;
create table dom1_classid_sorted_temp (id_dom integer, class_rank_dom
serial) without oids;
RAISE NOTICE 'Table created for class id: %', CAST(currentClassId AS TEXT);
insert into dom1_classid_sorted_temp (id_dom)
select id_dom from dom1_temp
WHERE class_id_dom && classIdArray
order by fast_score_dom desc, link_score_dom desc;
RAISE NOTICE 'Data inserted for class id: %', CAST(currentClassId AS TEXT);
-- write the class rank order back into the temporary table
update dom1_temp
SET class_rank_dom = dom1_classid_sorted_temp.class_rank_dom
from dom1_classid_sorted_temp where dom1_temp.id_dom =
dom1_classid_sorted_temp.id_dom;
RAISE NOTICE 'Data updated for class id: %', CAST(currentClassId AS TEXT);
elapsedTime = clock_timestamp() - startTime;
RAISE NOTICE 'Elapsed time: % for class id: %', CAST(elapsedTime AS
TEXT), CAST(currentClassId AS TEXT);
FETCH classIDCursor INTO rec; -- get next record;
END LOOP;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
NOTICE: table "dom1_classid_sorted_temp" does not exist, skipping
CONTEXT: SQL statement "drop table if exists dom1_classid_sorted_temp"
PL/pgSQL function "deletethisversioncreateclassandsyntryxranksfordomain1"
line 29 at SQL statement
NOTICE: CREATE TABLE will create implicit sequence
"dom1_classid_sorted_temp_class_rank_dom_seq" for serial column
"dom1_classid_sorted_temp.class_rank_dom"
CONTEXT: SQL statement "create table dom1_classid_sorted_temp (id_dom
integer, class_rank_dom serial) without oids"
PL/pgSQL function "deletethisversioncreateclassandsyntryxranksfordomain1"
line 31 at SQL statement
NOTICE: Table created for class id: 1
NOTICE: Data inserted for class id: 1
NOTICE: Data updated for class id: 1
NOTICE: Elapsed time: 00:06:34.315307 for class id: 1
NOTICE: Table created for class id: 2
ERROR: relation with OID 591161 does not exist
SQL state: 42P01
Context: SQL statement "INSERT INTO dom1_classid_sorted_temp (id_dom) select
id_dom from dom1_temp WHERE class_id_dom && $1 order by fast_score_dom
desc, link_score_dom desc"
PL/pgSQL function "deletethisversioncreateclassandsyntryxranksfordomain1"
line 33 at SQL statement
