Error drop_partitions forceSplit

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?

Comments

  • 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.

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

  • I have the same problem in 9.3.1-7

  • 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

  • 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=> 
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file