Help Center> Data Warehouse Service (DWS)> Troubleshooting> Database Use> Error "could not open relation with OID xxxx" Is Reported During Table Size Query
Updated on 2023-04-18 GMT+08:00
View PDF

Error "could not open relation with OID xxxx" Is Reported During Table Size Query

Symptom

When pg_table_size is used to query the size of a table, the error "could not open relation with OID xxxx" is reported.

Possible Causes

The table does not exist. NULL or or an error is returned.

Solution

  1. Use the exception method to to ignore this error. Return -1 for the tables that do not exist. Execute the following function: 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
    		 
    CREATE OR REPLACE FUNCTION public.pg_t_size(tab_oid OID,OUT retrun_code text) 
RETURNS text 
LANGUAGE plpgsql 
AS $$ DECLARE   
v_sql text;  
ts text;   
BEGIN   
V_SQL:='select pg_size_pretty(pg_table_size('||tab_oid||'))';   
EXECUTE IMMEDIATE V_SQL into ts;   
IF ts IS NULL    
THEN RETRUN_CODE:=-1;   
ELSE     
return ts;   
END IF;  
EXCEPTION   
WHEN OTHERS THEN   
RETRUN_CODE:=-1;  
END$$;
  2. Run the following commands: 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
    		 
    call public.pg_t_size('1','');
 retrun_code
-------------
 -1
(1 row)

select oid from pg_class limit 2;
 oid
------
 2662
 2659
(2 rows)

call public.pg_t_size('2662','');
 retrun_code
-------------
 120 KB
(1 row)
Parent topic: Database Use