We use Vertica 9.2.1, and I notice that there are not all ROS containers in storage_containers table. I can see such containers in the file system. Also, I can see them in the catalog snapshots and txnlogs. Is it a bug?
One more place to check:
select * from vs_ros where salstorageid = '024c798a2f7f249bca07c9c89acd8882015000012911933b';
You could also search for storage_oid for similar files, the last 3 digits of the storage_oid become the folder number, in this case search on
select * from vs_ros where rosid::VARCHAR LIKE '%371';
Storage containers lists all the ROS containers, and a ros container can be a group of files (for example we can have a file per column), its sal_storage_id would be derived from one of the files, that's why you don't see all the sal_storage_ids in the table storage_containers.
In vs_ros you have the list of all the files.
Please check v_monitor.delete_vectors to see if the files are listed there. If so, you can remove delete vectors by running SELECT PURGE(); or SELECT PURGE_TABLE('<tableName'>);
No, there are no files:select * from v_monitor.delete_vectors where sal_storage_id = '024c798a2f7f249bca07c9c89acd8882015000012911933b'select * from v_monitor.storage_containers where sal_storage_id = '024c798a2f7f249bca07c9c89acd8882015000012911933b'
select * from v_monitor.delete_vectors where sal_storage_id = '024c798a2f7f249bca07c9c89acd8882015000012911933b'
select * from v_monitor.storage_containers where sal_storage_id = '024c798a2f7f249bca07c9c89acd8882015000012911933b'
but exists in the file system:$ ls -lh /var/vertica/DWH/v_dwh_node0012_data/371/024c798a2f7f249bca07c9c89acd8882015000012911933b_0.gt-rw------- 1 dbadmin dbadmin 1.3G Jan 18 2020 /var/vertica/DWH/v_dwh_node0012_data/371/024c798a2f7f249bca07c9c89acd8882015000012911933b_0.gt
$ ls -lh /var/vertica/DWH/v_dwh_node0012_data/371/024c798a2f7f249bca07c9c89acd8882015000012911933b_0.gt
-rw------- 1 dbadmin dbadmin 1.3G Jan 18 2020 /var/vertica/DWH/v_dwh_node0012_data/371/024c798a2f7f249bca07c9c89acd8882015000012911933b_0.gt
Hooray! It's there. But... rosid is null. Perhaps this is the reason for the absence of rows in the storage_containersselect rosid, salstorageid from vs_ros where salstorageid = '024c798a2f7f249bca07c9c89acd8882015000012911933b';
select rosid, salstorageid from vs_ros where salstorageid = '024c798a2f7f249bca07c9c89acd8882015000012911933b';
Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.