capturing drop_partitions status

prad33p2301 · November 2024

Hi,

I am having a stored procedure for dropping partitions older than 180days which looks like this

`
--/
CREATE OR REPLACE PROCEDURE deleteOldData() LANGUAGE PLvSQL AS '

DECLARE timestamptodrop date := SELECT (GETUTCDATE() -180)::date;

BEGIN
PERFORM
SELECT /+label(myselectquery)/ DROP_Partitions(
''public.mytable'',
timestamptodrop,
timestamptodrop
);

END;
';
/
`

this procedure is executed at 3AM UTC everyday

what I need to do is capture the o/p of DROP_Partitions function so that I can send out an email about the status.

In some cases DROP_Partitions returns 'Partition does not exist' or 'Partition Dropped' or any error message
how can I capture all this?

I know I can query query_requests with request_label and use the data from that table for gettign to know the status of the query execution (failed or succes), duration etc . But I want to capture the actual o/p pf drop_partition

mosheg · November 2024

Please try the following:

-- Create simple partitioned table
CREATE TABLE t (c INT NOT NULL) PARTITION BY (c);

-- Insert initial data
INSERT INTO t SELECT 201907;

-- Merge partitions
SELECT do_tm_task('mergeout', 't');

-- View initial partition
SELECT partition_key
FROM partitions
WHERE projection_name = 't_super'
ORDER BY partition_key;

-- Update to create new partition
UPDATE t SET c = 202007;

-- Merge updated partitions
SELECT do_tm_task('mergeout', 't');

-- View both partitions
SELECT partition_key
FROM partitions
WHERE projection_name = 't_super'
ORDER BY partition_key;

\echo 'Create stored procedure for dropping partitions'
CREATE OR REPLACE PROCEDURE deletePartition(partition_to_drop INT)
LANGUAGE PLvSQL AS $$
DECLARE
  drop_result VARCHAR(1000);
BEGIN
  SELECT /*+label(drop_partitions_query)*/ DROP_Partitions(
    'public.t',
    partition_to_drop,
    partition_to_drop
  ) INTO drop_result;

  RAISE NOTICE 'DROP_Partitions result: %', drop_result;

EXCEPTION
  WHEN OTHERS THEN
    RAISE EXCEPTION 'Error in deletePartition: %', SQLERRM;
END;
$$;

\echo 'Test case 1: Should succeed - dropping old partition'
CALL deletePartition(201907);

\echo 'View partitions after successful drop'
SELECT partition_key
FROM partitions
WHERE projection_name = 't_super'
ORDER BY partition_key;

\echo 'Test case 2: Should fail - attempting to drop non-existent partition'
CALL deletePartition(201908);

Run time output:

 partition_key
---------------
 201907
 202007
(2 rows)

Create stored procedure for dropping partitions
CREATE PROCEDURE
Test case 1: Should succeed - dropping old partition
vsql:test3.sql:52: NOTICE 2005:  DROP_Partitions result: Partition dropped
 deletePartition
-----------------
               0
(1 row)

View partitions after successful drop
 partition_key
---------------
 202007
(1 row)

Test case 2: Should fail - attempting to drop non-existent partition
vsql:test3.sql:61: NOTICE 2005:  DROP_Partitions result: Partition does not exist
 deletePartition
-----------------
               0
(1 row)

capturing drop_partitions status

Answers