Thread: ERROR: relation with OID XXXX does not exist

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

"David Monarchi" <david.e.monarchi@gmail.com> writes:
> 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 think the problem is here:

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

plpgsql tries to cache plans for its queries, and dropping a table
invalidates any already-made plans for that table.  Can you TRUNCATE
the table instead?

PG 8.3 will be smarter about this sort of thing.

            regards, tom lane

Thanks, Tom.  I'll look forward to 8.3.

If I understand correctly, TRUNCATE is equivalent to DELETE FROM.  My problem was that I also needed to reset the serial attribute.  My solution was the following

  -- clear and reset counter
  delete from dom1_classid_sorted_temp;
  perform setval('dom1_classid_sorted_temp_class_rank_dom_seq'::regclass,1,false);


Thanks again.

Best,
david