HPE Vertica : DROP_PARTITION dynamic predicate value

Question

From the Vertica Docs:
DROP_PARTITION ( table_name , partition_value [ , ignore_moveout_errors, reorganize_data ])

Can the partition_value predicate be dynamic by any method?

I want to drop the partition based on MAX(partition_col_val) condition from another staging table.
Vertica also does not support variable creation as such, where I can keep the MAX(partition_col_val).

Is there any workaround possible?

Answer 1

You cannot use sub-querys to generate a dynamic predicate value for you drop_partition.

Normally i treat the lack of the dynamic feature with a script that will generate the drop_partition sql expresion into a .sql file and i execute in the next step.

See an example here :

create table tblone (id int not null) partition by id;

insert into tblone values (1);
commit;

-- flush the output of the query into a file

\o /tmp/file.sql
select 
'SELECT DROP_PARTITION(''public.tblone'','||
max(id)||
');' from tblone;

-- execute the content of the file

\i /tmp/file.sql
--this is the content.
SELECT DROP_PARTITION('public.tblone',1);

This is when you have partition based on non dates and data that needs to be derived from other data sets.

In case you have date as your partition key or a derived value from a data column you could use a internal function to dynamically populate the drop_partition key value:

    drop table tblone cascade;
create table tblone (id date not null) partition by 
 (((date_part('year', id) * 100) + date_part('month', id)));

insert into tblone values (getdate());
commit;

dbadmin=> select * from tblone;
     id
------------
 2017-01-04
(1 row)


dbadmin=> SELECT DROP_PARTITION('tblone',(date_part('year', getdate()) * 100) + date_part('month', getdate()));
  DROP_PARTITION
-------------------
 Partition dropped
(1 row)

dbadmin=> select * from tblone;
 id
----
(0 rows)

-you can always play with the getdate() to get the current or last month or any periods you wish to.

Another option is to use the vsql cmd line variables Example

dbadmin=> drop table tblone cascade;
DROP TABLE
dbadmin=> create table tblone (id int not null) partition by id;
CREATE TABLE
dbadmin=> insert into tblone values (1);
      1

dbadmin=> commit;
COMMIT
dbadmin=> select * from tblone;
 id
----
  1
(1 row)

-- show only tuples

dbadmin=> \t
Showing only tuples.

-- spit the max value into a file

dbadmin=> \o /tmp/file
dbadmin=> select max(id) from tblone;
dbadmin=> \o
dbadmin=> \t
Tuples only is off.

-- set the value of the variable to the file content(your max value)

dbadmin=> \set maxvalue `cat /tmp/file`
dbadmin=> \echo :maxvalue
       1

-- run the drop partition using the variable

dbadmin=> SELECT DROP_PARTITION('tblone',:maxvalue);
  DROP_PARTITION
-------------------
 Partition dropped
(1 row)

dbadmin=> select * from tblone;
 id
----
(0 rows)

• i hope this helped :)

An easy way to drop many partitions from a table using a single line of code would be using MOVE_PARTITIONS_TO_TABLE into a dummy table and then drop the dummy table - this will require no lock on the main table and dropping the dummy table is an cheap task for the database (will be a bulk drop_partition's).

1. create dummy table from base table (including projections).
2. generate dynamic MOVE_PARTITIONS_TO_TABLE('source','from partition','to partition','target/dummy table').
3. drop dummy table.

see small example (is not 100 complete- you can adapt it) is the same approach (generate & execute)

    \o /tmp/file.sql
select 'create dummy table as source table including projections;';
select 
    'SELECT MOVE_PARTITIONS_TO_TABLE(''source'','''||
    :minpartition()||
    ''','''||
    :maxpartition()||
    ''',''target/dummy table'')'
      from tblone;
select 'drop table dummy cascade';

-- execute the content of the file

    \i /tmp/file.sql
--make sure the content of the file is correct content

**

BTW - if you look for Vertica Database Articles and Scripts from time to time i post at http://wwww.aodba.com

**