"ROS container is missing partition keys" after failed re-partition of table

bmurrell

I'm running Vertica v12. I re-partitioned a table which eventually failed with lack of space on some nodes.
I ran a DO_TM_TASK to try and move it along, and also inserted 1 row (commited, then removed the rows) into many partitions to nudge a mergeout.
However, running a DUMP_TABLE_PARTITION_KEYS, it shows "ROS container is missing partition keys".
How do I selectively move this along ?

bmurrell

Also, why does a reorganize use way more temporary space than the original table?
Table is 100Gb per node, and node has 1.4Tb free.

VValdar

I'm not a fond of such heavy in-place reorganization - and this is not particular to Vertica.
Try create a new table with the good partition / projection instructions then insert select * from... (if needed you can feed partition by partition).
If it goes as expected, simply rename your tables.

bmurrell

I tried creating a new empty table with the target partitioning. It was loading 1m rows a minutes. At that rate it would take 11 days to load and I have no way to track the delta. There is back-filling from time to time so can't guarantee old partitions won't change.

VValdar

Oh that's VERY slow - and slow like "10 years ago on postgresql on my laptop that would already be slow".

You should definitely:
1. run a scrutinize to make sure your cluster is okay
2. engage with our support to check the original issue (ROS container is missing partition) and the size of the repartition,
3. engage with our professional services to see if things can be tuned (configuration, model, queries...).

mosheg

A fast way to remove a failed partition can be done via the following statement:

SELECT * FROM PARTITION_STATUS WHERE TABLE_SCHEMA = 'YourSchemaName' AND TABLE_NAME = 'YourTableName';
ALTER TABLE YourSchemaName.YourTableName REMOVE PARTITIONING;
SELECT * FROM PARTITION_STATUS WHERE TABLE_SCHEMA = 'YourSchemaName' AND TABLE_NAME = 'TableName';