Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to delete the oldest partition?

When you need to drop the oldest partition consider the following example:

CREATE TABLE public.my_table
(
    order_no int,
    order_date timestamp NOT NULL,
    shipper varchar(20),
    ship_date date
)
PARTITION BY DATE(order_date);

\set DEMO_ROWS 10

INSERT INTO public.my_table
WITH myrows AS (SELECT
row_number() over()    AS order_no,
NOW() - RANDOMINT(30) AS order_date
FROM ( SELECT 1 FROM ( SELECT NOW() as se union all
SELECT NOW() + :DEMO_ROWS - 1 AS se) a timeseries ts AS '1 day' OVER (ORDER BY se)) b)
SELECT order_no, order_date, 'something', order_date AS ship_date
FROM myrows
ORDER BY order_no;
COMMIT;

WITH my_list AS
(SELECT max(order_date) as oldest from public.my_table)
SELECT date(order_date),
       CASE WHEN order_date = oldest THEN 'Will be droped' ELSE 'Will keep' END as Status
FROM public.my_table, my_list ORDER BY 1;

-- Option A:
\set OLDEST `vsql -XAtc "SELECT max(order_date)::date from public.my_table;"`
\set OLDEST '''':OLDEST''''
SELECT DROP_PARTITIONS ('my_table', :OLDEST, :OLDEST);

SELECT DATE(order_date) FROM public.my_table ORDER BY 1;

-- Option B:
WITH my_list AS
(SELECT max(order_date) as oldest from public.my_table)
SELECT date(order_date),
       CASE WHEN order_date = oldest THEN 'Will be droped' ELSE 'Will keep' END as Status
FROM public.my_table, my_list ORDER BY 1;

CREATE OR REPLACE FUNCTION my_drop(d2d DATE) RETURN VARCHAR(100)
   AS BEGIN
     RETURN 'SELECT DROP_PARTITIONS (''my_table'', ''' || d2d || ''' , ''' || d2d || ''');';
   END;

\t
\o | vsql
SELECT my_drop((SELECT max(order_date)::DATE from public.my_table));
\o
\t

SELECT DATE(order_date) FROM public.my_table ORDER BY 1;

Here is the run time output:

CREATE TABLE public.my_table
(
    order_no int,
    order_date timestamp NOT NULL,
    shipper varchar(20),
    ship_date date
)
PARTITION BY DATE(order_date);
CREATE TABLE
INSERT INTO public.my_table
WITH myrows AS (SELECT
row_number() over()    AS order_no,
NOW() - RANDOMINT(30) AS order_date
FROM ( SELECT 1 FROM ( SELECT NOW() as se union all
SELECT NOW() + 10 - 1 AS se) a timeseries ts AS '1 day' OVER (ORDER BY se)) b)
SELECT order_no, order_date, 'something', order_date AS ship_date
FROM myrows
ORDER BY order_no;
 OUTPUT
--------
     10
(1 row)

COMMIT;
COMMIT

WITH my_list AS
(SELECT max(order_date) as oldest from public.my_table)
SELECT date(order_date),
       CASE WHEN order_date = oldest THEN 'Will be droped' ELSE 'Will keep' END as Status
FROM public.my_table, my_list ORDER BY 1;
    date    |     Status
------------+----------------
 2020-05-01 | Will keep
 2020-05-02 | Will keep
 2020-05-06 | Will keep
 2020-05-07 | Will keep
 2020-05-07 | Will keep
 2020-05-10 | Will keep
 2020-05-13 | Will keep
 2020-05-20 | Will keep
 2020-05-22 | Will keep
 2020-05-24 | Will be droped
(10 rows)

SELECT DROP_PARTITIONS ('my_table', '2020-05-24', '2020-05-24');
  DROP_PARTITIONS
-------------------
 Partition dropped
(1 row)

SELECT DATE(order_date) FROM public.my_table ORDER BY 1;
    DATE
------------
 2020-05-01
 2020-05-02
 2020-05-06
 2020-05-07
 2020-05-07
 2020-05-10
 2020-05-13
 2020-05-20
 2020-05-22
(9 rows)

WITH my_list AS
(SELECT max(order_date) as oldest from public.my_table)
SELECT date(order_date),
       CASE WHEN order_date = oldest THEN 'Will be droped' ELSE 'Will keep' END as Status
FROM public.my_table, my_list ORDER BY 1;
    date    |     Status
------------+----------------
 2020-05-01 | Will keep
 2020-05-02 | Will keep
 2020-05-06 | Will keep
 2020-05-07 | Will keep
 2020-05-07 | Will keep
 2020-05-10 | Will keep
 2020-05-13 | Will keep
 2020-05-20 | Will keep
 2020-05-22 | Will be droped
(9 rows)

CREATE OR REPLACE FUNCTION my_drop(d2d DATE) RETURN VARCHAR(100)
   AS BEGIN
     RETURN 'SELECT DROP_PARTITIONS (''my_table'', ''' || d2d || ''' , ''' || d2d || ''');';
   END;
CREATE FUNCTION

Showing only tuples.
SELECT my_drop((SELECT max(order_date)::DATE from public.my_table));
  DROP_PARTITIONS
-------------------
 Partition dropped
(1 row)

Tuples only is off.
SELECT DATE(order_date) FROM public.my_table ORDER BY 1;
    DATE
------------
 2020-05-01
 2020-05-02
 2020-05-06
 2020-05-07
 2020-05-07
 2020-05-10
 2020-05-13
 2020-05-20
(8 rows)

Answers

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.