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

Search Discussions

  • Tom Lane at Aug 18, 2007 at 5:12 am

    "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
  • David Monarchi at Aug 18, 2007 at 3:08 pm
    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
    On 8/17/07, Tom Lane wrote:

    "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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedAug 17, '07 at 3:48p
activeAug 18, '07 at 3:08p
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

David Monarchi: 2 posts Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase