FilesExpand file tree

 main

/

drop_partitions.sql

Copy path

Blame

More file actions

Blame

More file actions

Latest commit

 

History

History

History

42 lines (37 loc) · 1.24 KB

 main

/

drop_partitions.sql

Top

File metadata and controls

• Code
• Blame

42 lines (37 loc) · 1.24 KB

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

-- Drop all partitions for CompletedJob and FailedJob tables

-- Run this script in your PostgreSQL 'jobs' database

-- Drop all CompletedJob partitions

DO $$

DECLARE

partition_name TEXT;

BEGIN

FOR partition_name IN

SELECT relid::regclass::text

FROM pg_partition_tree('"CompletedJob"'::regclass)

WHERE parentrelid IS NOT NULL

LOOP

EXECUTE 'DROP TABLE IF EXISTS ' || partition_name || ' CASCADE';

RAISE NOTICE 'Dropped partition: %', partition_name;

END LOOP;

END $$;

-- Drop all FailedJob partitions

DO $$

DECLARE

partition_name TEXT;

BEGIN

FOR partition_name IN

SELECT relid::regclass::text

FROM pg_partition_tree('"FailedJob"'::regclass)

WHERE parentrelid IS NOT NULL

LOOP

EXECUTE 'DROP TABLE IF EXISTS ' || partition_name || ' CASCADE';

RAISE NOTICE 'Dropped partition: %', partition_name;

END LOOP;

END $$;

-- Verify partitions are dropped

SELECT 'CompletedJob partitions remaining:' as info, COUNT(*) as count

FROM pg_partition_tree('"CompletedJob"'::regclass)

WHERE parentrelid IS NOT NULL;

SELECT 'FailedJob partitions remaining:' as info, COUNT(*) as count

FROM pg_partition_tree('"FailedJob"'::regclass)

WHERE parentrelid IS NOT NULL;