Error drop_partitions forceSplit

Forum|Forum|7 years ago
September 16, 2019
5 replies
6 views

nandreev93

Hello
When I executed drop_partitions function Vertica returns an error:
ERROR 55000 ERROR 8430: Some storage containers must be split before the specified partition range can be dropped HINT: Set "forceSplit" to true to implicitly repartition the data
Command that is executed is as follows:
select drop_partitions('ods.table_name','2019-01-01','2019-01-01','true');
So forceSplit is already set to true. Table is partitioned hierarchicaly (year - month - day) and I get the error when I am trying to drop partition from inactive months/years , maybe this somehow affecting.
Almost the same error I get when using swap_partition_between_tables function.
What can cause the problem? And how can I fix it?

damienlesage
New Participant
Forum|Forum|7 years ago
September 30, 2019

Hello,
We have the same issue on Vertica Analytic Database v9.2.1-2.
What's the root cause? How to fix the issue?
Damien.

mircorighetti
New Participant
Forum|Forum|6 years ago
April 14, 2020

Hello, the same problem with 9.2.1. Someone found the solution?

mircorighetti
New Participant
Forum|Forum|5 years ago
January 28, 2021

I have the same problem in 9.3.1-7

Sudhakar_B
Participating Frequently
Forum|Forum|5 years ago
January 28, 2021

In version v9.3.1-0 , I don't see the problem.
My partitioning on sample schema table looks like below:

ALTER TABLE store.store_orders_fact 
      PARTITION BY date_ordered::DATE
GROUP BY (
CASE WHEN DATEDIFF('YEAR', (store_orders_fact.date_ordered)::date, '2016-12-10'::TIMESTAMPTZ(6)) >= 2
       THEN DATE_TRUNC('YEAR', (store_orders_fact.date_ordered)::date)
     WHEN DATEDIFF('MONTH', (store_orders_fact.date_ordered)::date, '2016-12-10'::TIMESTAMPTZ(6)) >= 2 
       THEN DATE_TRUNC('MONTH', (store_orders_fact.date_ordered)::date)
     ELSE DATE_TRUNC('DAY', (store_orders_fact.date_ordered)::date) END);

ALTER TABLE store.store_orders_fact REORGANIZE;

Hierarchical Partition distribution is like below:

dbadmin=> select ros_id, count(partition_key),min(partition_key) minkey, max(partition_key) maxkey
dbadmin-> from partitions 
dbadmin-> group by 1
dbadmin-> order by 3 ;
      ros_id       | count |   minkey   |   maxkey   
-------------------+-------+------------+------------
 45035996274381559 |   119 | 2012-01-02 | 2012-12-10
 45035996274381325 |   120 | 2013-01-01 | 2013-12-10
 45035996274381319 |   120 | 2014-01-01 | 2014-12-10
 45035996274381313 |    10 | 2015-01-01 | 2015-01-10
 45035996274381307 |    10 | 2015-02-01 | 2015-02-10
 45035996274381301 |    10 | 2015-03-01 | 2015-03-10
 45035996274381295 |    10 | 2015-04-01 | 2015-04-10
 45035996274381289 |    10 | 2015-05-01 | 2015-05-10
 45035996274381283 |    10 | 2015-06-01 | 2015-06-10
 45035996274381277 |    10 | 2015-07-01 | 2015-07-10
 45035996274381271 |    10 | 2015-08-01 | 2015-08-10
 45035996274381265 |    10 | 2015-09-01 | 2015-09-10
 45035996274381259 |    10 | 2015-10-01 | 2015-10-10
 45035996274381253 |    10 | 2015-11-01 | 2015-11-10
 45035996274381247 |    10 | 2015-12-01 | 2015-12-10
 45035996274381241 |    10 | 2016-01-01 | 2016-01-10
 45035996274381235 |    10 | 2016-02-01 | 2016-02-10
 45035996274381229 |    10 | 2016-03-01 | 2016-03-10
 45035996274381223 |    10 | 2016-04-01 | 2016-04-10
 45035996274381217 |    10 | 2016-05-01 | 2016-05-10
 45035996274381211 |    10 | 2016-06-01 | 2016-06-10
 45035996274381205 |    10 | 2016-07-01 | 2016-07-10
 45035996274381199 |    10 | 2016-08-01 | 2016-08-10
 45035996274381193 |    10 | 2016-09-01 | 2016-09-10
 45035996274381187 |    10 | 2016-10-01 | 2016-10-10
 45035996274381181 |     1 | 2016-11-01 | 2016-11-01
 45035996274381175 |     1 | 2016-11-02 | 2016-11-02
 45035996274381169 |     1 | 2016-11-03 | 2016-11-03
 45035996274381163 |     1 | 2016-11-04 | 2016-11-04
 45035996274381157 |     1 | 2016-11-05 | 2016-11-05
 45035996274381151 |     1 | 2016-11-06 | 2016-11-06
 45035996274381145 |     1 | 2016-11-07 | 2016-11-07
 45035996274381139 |     1 | 2016-11-08 | 2016-11-08
 45035996274381133 |     1 | 2016-11-09 | 2016-11-09
 45035996274381127 |     1 | 2016-11-10 | 2016-11-10
 45035996274381121 |     1 | 2016-12-01 | 2016-12-01
 45035996274381115 |     1 | 2016-12-02 | 2016-12-02
 45035996274381109 |     1 | 2016-12-03 | 2016-12-03
 45035996274381103 |     1 | 2016-12-04 | 2016-12-04
 45035996274381097 |     1 | 2016-12-05 | 2016-12-05
 45035996274381091 |     1 | 2016-12-06 | 2016-12-06
 45035996274381085 |     1 | 2016-12-07 | 2016-12-07
 45035996274381079 |     1 | 2016-12-08 | 2016-12-08
 45035996274381073 |     1 | 2016-12-09 | 2016-12-09
 45035996274381067 |     1 | 2016-12-10 | 2016-12-10
(45 rows)

dbadmin=>

Now I'll drop 2012-02-01 partition below:

dbadmin=> select drop_partitions('store.store_orders_fact','2012-02-01','2012-02-01','true');
  drop_partitions  
-------------------
 Partition dropped
(1 row)

dbadmin=> 
dbadmin=> select ros_id, count(partition_key),min(partition_key) minkey, max(partition_key) maxkey
dbadmin-> from partitions
dbadmin-> where  partition_key < '2015-05-01'
dbadmin-> group by 1
dbadmin-> order by 3 ;
      ros_id       | count |   minkey   |   maxkey   
-------------------+-------+------------+------------
 45035996274381761 |     9 | 2012-01-02 | 2012-01-10
 45035996274381767 |   109 | 2012-02-02 | 2012-12-10
 45035996274381325 |   120 | 2013-01-01 | 2013-12-10
 45035996274381319 |   120 | 2014-01-01 | 2014-12-10
 45035996274381313 |    10 | 2015-01-01 | 2015-01-10
 45035996274381307 |    10 | 2015-02-01 | 2015-02-10
 45035996274381301 |    10 | 2015-03-01 | 2015-03-10
 45035996274381295 |    10 | 2015-04-01 | 2015-04-10
(8 rows)

dbadmin=>

Note the 9 Partition keys sitting in separate ROS container.
Ran the MERGEOUT...

select do_tm_task('mergeout');
dbadmin=> select ros_id, count(partition_key),min(partition_key) minkey, max(partition_key) maxkey
dbadmin-> from partitions
dbadmin-> where  partition_key < '2015-05-01'
dbadmin-> group by 1
dbadmin-> order by 3 ;
      ros_id       | count |   minkey   |   maxkey   
-------------------+-------+------------+------------
 45035996274381947 |   118 | 2012-01-02 | 2012-12-10
 45035996274381325 |   120 | 2013-01-01 | 2013-12-10
 45035996274381319 |   120 | 2014-01-01 | 2014-12-10
 45035996274381313 |    10 | 2015-01-01 | 2015-01-10
 45035996274381307 |    10 | 2015-02-01 | 2015-02-10
 45035996274381301 |    10 | 2015-03-01 | 2015-03-10
 45035996274381295 |    10 | 2015-04-01 | 2015-04-10
(7 rows)

dbadmin=>

Let's DROP some more partition keys...

dbadmin=> select drop_partitions('store.store_orders_fact','2012-01-01','2012-04-01','true');
  drop_partitions  
-------------------
 Partition dropped
(1 row)

dbadmin=> select ros_id, count(partition_key),min(partition_key) minkey, max(partition_key) maxkey
dbadmin-> from partitions
dbadmin-> where  partition_key < '2015-05-01'
dbadmin-> group by 1
dbadmin-> order by 3 ;
      ros_id       | count |   minkey   |   maxkey   
-------------------+-------+------------+------------
 45035996274382127 |    89 | 2012-04-02 | 2012-12-10
 45035996274381325 |   120 | 2013-01-01 | 2013-12-10
 45035996274381319 |   120 | 2014-01-01 | 2014-12-10
 45035996274381313 |    10 | 2015-01-01 | 2015-01-10
 45035996274381307 |    10 | 2015-02-01 | 2015-02-10
 45035996274381301 |    10 | 2015-03-01 | 2015-03-10
 45035996274381295 |    10 | 2015-04-01 | 2015-04-10
(7 rows)

dbadmin=>

ALL Clean !
Can you please share the details?
Version and Step-by-Step to recreate the problem?
Also please include the relevant out-put of query from partitions tables.
Thanks

Sudhakar_B
Participating Frequently
Forum|Forum|5 years ago
January 28, 2021

As a follow-up...

dbadmin=> select drop_partitions('store.store_orders_fact','2012-01-01','2012-07-01');
ERROR 8430:  Some storage containers must be split before the specified partition range can be dropped
HINT:  Set "forceSplit" to true to implicitly repartition the data
dbadmin=> select drop_partitions('store.store_orders_fact','2012-01-01','2012-07-01','true');
  drop_partitions  
-------------------
 Partition dropped
(1 row)

dbadmin=>

Sign up

Please log in or register:

Welcome to the Rocket Forum!

Please log in or register: