Help Center/ GaussDB(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

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)