[Vertica][VJDBC](5065) ERROR: Too many ROS containers exist for the following projections:

zhaoyunye

vertica version: Vertica Analytic Database v10.0.0-0
this is my table:

create table dc_run_job_txn (
    id                      varchar(80)           not null
        primary key,
    job_definition_id       varchar(80)           not null,
    job_definition_snapshot long varchar(1048576) not null,
    last_job_status         varchar(40),
    last_job_execution_time int,
    create_time             int                   not null,
    update_time             int                   not null,
    last_job_error_detail   varchar(1000)
);

table projections:

select get_projections('dc_run_job_txn');

Current system K is 0. 
# of Nodes: 1. 
Table dc_formula.dc_run_job_txn has 1 projections. 

Projection Name: [Segmented] [Seg Cols] [# of Buddies] [Buddy Projections] [Safe] [UptoDate] [Stats]
----------------------------------------------------------------------------------------------------
dc_formula.dc_run_job_txn_super [Segmented: Yes] [Seg Cols: "dc_formula.dc_run_job_txn.id"] [K: 0] [No buddy projections] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]

The following DML statement will be executed with high frequency:

UPDATE dc_run_job_txn SET last_job_status = 'running', last_job_execution_time = 1592382202606, update_time = 1592382202606 WHERE id = '5ee1b24a78627a71b4477445'

I learned that WOS was abandoned in vertica 10.
Here are some parameters I configured:

ROSPerStratum -1
MoveOutInterval 300
MergeOutInterval 30

We just started using vertica and we are very confused about this issue. Please help us, thank you very much!

Nimmi_gupta

Can you check the following parameters
select get_config_parameter('ContainersPerProjectionLimit');
select get_config_parameter('FilesPerProjectionLimit');

Nimmi_gupta

The error you have mentioned
ERROR 5065: Too many ROS containers exist for the following projections:
This could be because either mergeout is not catching up or you have not committed the transaction or there are lot of partitions (in your case you have not partitioned the table)
Check the commit after the update. Committing often would result in Low ROS count.

mosheg

In addition, with Vertica you can avoid DELETEs, UPDATEs and MERGE, to reduce delete vectors and improve performance.
I learned this from Maurizio to manage data versioning and save the row “insert/update/delete” time history.
How the method works?
Instead of doing deletes or update to your data, try to use Live Aggregate Projection (LAP) TOP-K with the last version of the data.
In the base (anchor) table you would just do INSERTs.
The anchor table will always increase its size since we insert new data to “replace” or “hide” the old data.
For example:

DROP TABLE my_table cascade;

CREATE TABLE my_table(
subject_id  int,
subject     varchar(10),
insert_date datetime default sysdate())
ORDER BY subject_id;

CREATE PROJECTION my_topk_LAP (subject_id, insert_date, subject)
AS SELECT subject_id, insert_date, subject
   FROM my_table
LIMIT 1 OVER (PARTITION BY subject_id ORDER BY insert_date DESC);

COPY my_table (subject_id,subject) FROM STDIN DELIMITER ',' ABORT ON ERROR;
1,A
2,B
3,C
4,D
\.

\echo 'When we want to update B with New B, we just INSERT new B with its subject_id:'
INSERT INTO my_table (subject_id,subject) values (2,'New B');
COMMIT;

SELECT subject_id, subject, insert_date FROM my_topk_LAP order by 1;
 subject_id | subject |        insert_date
------------+---------+----------------------------
          1 | A       | 2020-06-22 17:37:03.767162
          2 | New B   | 2020-06-22 17:37:03.803268
          3 | C       | 2020-06-22 17:37:03.767162
          4 | D       | 2020-06-22 17:37:03.767162
(4 rows)

SELECT subject_id, subject, insert_date FROM my_table order by 1;
 subject_id | subject |        insert_date
------------+---------+----------------------------
          1 | A       | 2020-06-22 17:37:03.767162
          2 | New B   | 2020-06-22 17:37:03.803268
          2 | B       | 2020-06-22 17:37:03.767162
          3 | C       | 2020-06-22 17:37:03.767162
          4 | D       | 2020-06-22 17:37:03.767162
(5 rows)

From time to time (every several months) you can “purge” the anchor table to get rid of old versions of the data.
Like so:
Step1: CREATE TABLE new_base_table AS SELECT * from LAP (you create a new table with the “last version” of the data)
Step2: DROP TABLE base_table CASCADE (drop the old base table, it’s projections and LAP)
Step3: ALTER TABLE new_base_table RENAME base_table (rename the “last version” of the data)
Step4: recreate LAP
From now on you continue INSERTing into the base table as usual.

qinchaofeng

Please add hint 'direct' while using delete or insert .