While loading data into the HPE Vertica database, you may run into this issue :

[code lang=SQL]
[Vertica][VJDBC](2245) ERROR: Attempted to create too many ROS containers for projection x.y
[/code]

You may also notice that the error disapears if you wait a few minutes before restarting your data loading process. Only to reappear a little while later.

The issue comes from the vertica Read Optimized Store (ROS).

When loading data into Vertica, it is first stored in a Write Optimized Store (WOS). Then, every 5 minutes by default, the tuple mover pushes the content of the WOS to the ROS, where it is indexed and compressed for optimal read operations.

The ROS organizes data into containers. Each time the tuple movers pushes data to the ROS, a new container is created. Vertica allows a maximum of 1024 containers per projection. When this limit is hit, the tuple mover fails, and the transaction is rolled back, causing the “Attempted to create too many ROS containers for projection” error.

This issue is automatically fixed by Vertica through a mergeout process, run every 10 minutes by default, which will merge existing containers into larger ones, hence going under the 1024 limit.

Instead of waiting for the next automatic mergeout, or changing the mergeout schedule, you can fix the error by catching the exception and trigerring the merge manually using this SQL statement :
[code lang=SQL]
SELECT DO_TM_TASK(‘mergeout’);
[/code]

Before triggering the merge, you can also be smarter and check how close the projection is to the 1024 containers limit using the following SQL query :
[code lang=SQL]
select projection_name, ros_count from projection_storage
[/code]