PGAgent - Scheduling a job to drop my old partitions

Question

I have a script below which is running fine when i'm executing directly via PGAdmin however when i schedule it to run using PGAgent though, it's showing successful but my partitions were still untouched.

Below is the PostgreSQL version our company is currently using. "PostgreSQL 14.2 (EnterpriseDB Advanced Server 14.2.1) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit"

DECLARE
  TYPE tbl_arr IS VARRAY(5) OF VARCHAR2(30);
  partncnt INTEGER;
  drop_sql varchar2(500); 
  tbnames tbl_arr;  
  t_partition_name  user_tab_partitions.partition_name%TYPE;  
  
BEGIN
  tbnames := tbl_arr('MYTABLE');         
    FOR i IN 1..1 LOOP
              select count(1) into partncnt from user_tab_partitions where table_name=tbnames(i);                      
              IF (partncnt) > 7 THEN                         
                        select partition_name into t_partition_name from user_tab_partitions where table_name=tbnames(i) and partition_position=2;                   
                        drop_sql := 'alter table '|| tbnames(i) ||' drop partition ' || t_partition_name;             
                        execute immediate drop_sql;                              
              ELSE
                          DBMS_OUTPUT.PUT_LINE('No Partition to drop');   
              END IF;             

  END LOOP;      
END;

Checking on it's last run you can see that it's running successfully:

screenshot of successful ran job

Answer 1

Manage to find the root cause apparently for postgres you will need to ensure that PGAgent is running for the user/schema. As soon as we had it running., the job were been executed successfully base on the interval i've set.