How to trace file to database table

scherepanov · November 12

Hi,
System log contains errors like that:
/vertica1/xxx/423/02043dbe255c2a6a684cba961bca1bfc00c0000005239acf_0.gt: Input/output error
Logs contains errors from 3 files.
I managed to find one table with bad file, more by accident and luck than understanding how to find them.
Is it possible to find table by file name, without doing extensive filesystem scan or running Vertica command to verify all of database.
Thank you
Sergey

mosheg · December 25

To find a table associated with a specific file in Vertica you can query the relevant system tables.
While the exact file naming convention can vary between Vertica versions, EE and EON convention, you can try the following query example to trace a file back to its table:

SELECT DISTINCT
        vsr.NODE_NAME,
        sto.TOTAL_ROW_COUNT,
        sto.DELETED_ROW_COUNT,
        sto.USED_BYTES as COMPRESSED_USED_BYTES,
--        sto.DISK_SIZE as DISK_SIZE_Bytes, -- from Vertica v24.3
        pro.projection_schema_id,
        pro.projection_schema,
        pro.projection_id,
        pro.projection_name,
        pro.projection_basename,
        pro.owner_name,
        pro.anchor_table_id,
        pro.anchor_table_name,
        pro.is_up_to_date,
        p.partition_key,
        vsr.sal_storage_id,
        SUBSTRING(vsr.storage_path FROM 1) AS storage_path_txt,    -- on EON go FROM 5
        hash(SUBSTRING(vsr.storage_path FROM 1)) AS storage_path_hash    -- on EON go FROM 5
    FROM projections pro
    JOIN storage_containers sto ON sto.projection_id = pro.projection_id
    JOIN vs_storage_reference_counts vsr ON sto.sal_storage_id = vsr.sal_storage_id
    LEFT JOIN partitions p ON p.projection_id = pro.projection_id
    WHERE vsr.storage_type = 'ROS_BUNDLE'
-- for a specific table:       AND pro.anchor_table_name = 'MyTableName'
    ORDER BY vsr.NODE_NAME, pro.projection_schema_id, pro.projection_id;

Here is another example how to get the file name associated with a particular projection:

select delid,(select location_path from storage_locations where location_id=location) FileLocaton ,salstorageid FileName 
from vs_ros where delid in 
(select storage_oid from storage_containers where schema_name='public' and projection_name ilike 't_ros%') 
group by 1,2,3;

List all projections with no statistics or not up-to-date or not fault tolerance:

SELECT projection_schema, 
       projection_name, 
       anchor_table_name, 
       is_up_to_date, 
       has_statistics, 
       verified_fault_tolerance
from PROJECTIONS
WHERE 
-- projection_schema= 'my_schema_name' and
 ( not is_up_to_date  or  not has_statistics  or verified_fault_tolerance < 1);

Note that the elapsed time for these queries can be high on a large database.

How to trace file to database table

Answers