Cannot execute deployment when there are non-up-to-date safe projections for table
So we have a 3 node vertica cluster running on AWS r3.4xlarge and we've loaded some data ~ 1.2B rows into one table. I've done a couple of aggregation queries and provided those when using the DB Designer but I get this error when it tries to deploy the design...
[{"timestamp":1438110468356,"label":"Design in progress: Initializing design","percentage":0},{"timestamp":1438110469145,"label":"Design in progress: Design started","percentage":0},{"timestamp":1438110470079,"label":"Design in progress: Design in progress: Analyze statistics phase","percentage":0},{"timestamp":1438110471208,"label":"Analyzing data statistics: public.vcf_lines_flex","percentage":25},{"timestamp":1438110472981,"label":"Analyzing data statistics: public.vcf_lines_flex_keys","percentage":50},{"timestamp":1438110474424,"label":"Analyzing data statistics: public.vcf_lines","percentage":75},{"timestamp":1438110498705,"label":"Analyzing data statistics: public.erp_batch_patient_info","percentage":100},{"timestamp":1438110499995,"label":"Analyzing data statistics: --DONE--","percentage":0},{"timestamp":1438110500446,"label":"Warning during design: Table public.vcf_lines_flex has no statistics or data. As a result, the proposed projections on this table may be suboptimal. HINT: Load data or statistics into this table.","percentage":100},{"timestamp":1438110500791,"label":"Warning during design: Table public.vcf_lines_flex_keys has no statistics or data. As a result, the proposed projections on this table may be suboptimal. HINT: Load data or statistics into this table.","percentage":100},{"timestamp":1438110501569,"label":"Warning during design: Table public.vcf_lines_flex has no statistics or data. As a result, no correlations were read into this table. HINT: Load data or statistics into this table.","percentage":100},{"timestamp":1438110501746,"label":"Warning during design: Table public.vcf_lines_flex_keys has no statistics or data. As a result, no correlations were read into this table. HINT: Load data or statistics into this table.","percentage":100},{"timestamp":1438110501956,"label":"Design in progress: Design in progress: Query optimization phase","percentage":0},{"timestamp":1438110502315,"label":"Optimizing query performance: Extracting interesting columns","percentage":14},{"timestamp":1438110506835,"label":"Optimizing query performance: Enumerating sort orders","percentage":29},{"timestamp":1438110507184,"label":"Optimizing query performance: Setting up projection candidates","percentage":43},{"timestamp":1438110508298,"label":"Optimizing query performance: Assessing projection candidates","percentage":57},{"timestamp":1438110509616,"label":"Optimizing query performance: Choosing best projections","percentage":71},{"timestamp":1438110510135,"label":"Optimizing query performance: Calculating estimated benefit of best projections","percentage":86},{"timestamp":1438110510451,"label":"Optimizing query performance: Complete","percentage":100},{"timestamp":1438110511047,"label":"Design in progress: Design in progress: Storage optimization phase","percentage":0},{"timestamp":1438110511820,"label":"Warning during design: Cannot determine the best encoding options for some columns in table public.vcf_lines_flex due to insufficient data. HINT: Load more data into this table.","percentage":100},{"timestamp":1438110513546,"label":"Optimizing storage footprint: vcf_lines_flex_DBD_1_seg_analytics_v2_b0","percentage":17},{"timestamp":1438110514152,"label":"Optimizing storage footprint: vcf_lines_flex_DBD_1_seg_analytics_v2_b1","percentage":33},{"timestamp":1438110514704,"label":"Optimizing storage footprint: vcf_lines_flex_keys_DBD_2_seg_analytics_v2_b0","percentage":50},{"timestamp":1438110518503,"label":"Optimizing storage footprint: vcf_lines_flex_keys_DBD_2_seg_analytics_v2_b1","percentage":67},{"timestamp":1438110518684,"label":"Optimizing storage footprint: vcf_lines_DBD_3_seg_analytics_v2","percentage":83},{"timestamp":1438110518884,"label":"Optimizing storage footprint: erp_batch_patient_info_DBD_4_rep_analytics_v2","percentage":100},{"timestamp":1438110524393,"label":"All done: ","percentage":0},{"timestamp":1438110524480,"label":"Design in progress: Design completed successfully","percentage":0},{"timestamp":1438110525388,"label":"Generating deployment script: Setting up deployment metadata","percentage":0},{"timestamp":1438110527843,"label":"Generating deployment script: Identifying projections to be deployed","percentage":0},{"timestamp":1438110528232,"label":"CREATE_DEPLOYMENT: N/A","percentage":0},{"timestamp":1438110528234,"label":"Generating deployment script: Identified all projections to be deployed","percentage":0},{"timestamp":1438110529302,"label":"POPULATE_DEPLOYMENT: N/A","percentage":0},{"timestamp":1438110535756,"label":"Generating deployment script: Identifying projections to be dropped","percentage":0},{"timestamp":1438110536162,"label":"Error during deployment: Deployment did not complete successfully. ERROR - Cannot execute deployment when there are non-up-to-date safe projections for table vcf_lines","percentage":100},{"timestamp":1438110536162,"label":"DESIGN_DEPLOYMENT: Cannot execute deployment when there are non-up-to-date safe projections for table vcf_linesCannot execute deployment when there are non-up-to-date safe projections for table vcf_lines","percentage":0},{"timestamp":1438110536791,"label":"DEPLOYMENT: N/A","percentage":0}]
thanks
steve
Comments
Hi,
It looks like projections are not up to date for the table vcf_lines. Try refreshing all projections for the table before proceeding for deployment.
select REFRESH('<tablename>') will refresh projections for a particular table.
select REFRESH() will refresh all non up to date projections.
For more information on REFRESH, please visit the following URL
https://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/VerticaFunctions/REFRESH.htm?Highlight=refresh
-Regards,
Sruthi
Yes, I ended up flushing the non up to date projections and that seemed to work...
Hi,
Good to know that it worked
-Regards,
Sruthi