This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Anuraag Veerapaneni – He is my work coordinator, and he got “OID does not exist” error in one of the function execution.
Later we found that the problem was in the function code. The temp table was creating and dropping within the WHILE LOOP.
Internally, WHILE LOOP also keeps one session, so the OBJECT ID of dropped temp table still there in the session of WHILE LOOP.
After dropping the temp table, it creates a new temp table in WHILE LOOP with the new object id but dropped temp table object id is still in the session so while selecting a temp table it will search for old Temp table which already dropped.
Our advice: please never write code to create or drop temp tables in the WHILE LOOP.
For better understanding, please visit below demonstration:
Create a test function:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
CREATE OR REPLACE FUNCTION fn_check_drop_temp_table() RETURNS integer AS $dbrnd$ DECLARE i INT; j OID; z OID; b OID; a OID; BEGIN i:=0; z:=Coalesce((SELECT relfilenode FROM pg_class WHERE relname LIKE '%tmp_drop_test%'),111111); RAISE NOTICE '%','No table found...'|| z; DROP TABLE IF EXISTS tmp_a; CREATE TABLE tmp_a (col1 int); WHILE i<=2 LOOP j:=coalesce((SELECT relfilenode FROM pg_class WHERE relname LIKE '%tmp_drop_test%'),1); RAISE NOTICE '%','1= No data, Other value= OID Data --'|| j; DROP TABLE IF EXISTS tmp_drop_test; -- Now drop temp table CREATE temp TABLE tmp_drop_test AS SELECT 1 AS int1; -- Create temp table again with same name a:=coalesce((SELECT relfilenode FROM pg_class WHERE relname LIKE '%tmp_drop_test%'),1); RAISE NOTICE '%','After creating temp table - OID of temp table:'|| a; INSERT INTO tmp_a SELECT * FROM tmp_drop_test; i:=i+1; END LOOP; RETURN 1; END; $dbrnd$ LANGUAGE PLPGSQL; |
Now, execute above function:
|
1 |
SELECT *FROM fn_check_drop_temp_table(); |
Got error in the result:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
NOTICE: No table found...111111 NOTICE: 1= No data, Other value= OID Data --1 NOTICE: After creating temp table - OID of temp table:27102093 NOTICE: 1= No data, Other value= OID Data --27102093 NOTICE: After creating temp table - OID of temp table:27102120 ERROR: relation with OID 27102093 does not exist CONTEXT: SQL statement "INSERT INTO tmp_a SELECT * FROM tmp_drop_test" PL/pgSQL function "fn_check_drop_temp_table" line 33 at SQL statement |
Leave a Reply