On this page
Help Center/ GaussDB(DWS)/ Troubleshooting/ Database Use/ Error "could not open relation with OID xxxx" Is Reported During Table Size Query

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

Updated on 2024-03-08 GMT+08:00

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)
    
Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback