0

I'm trying to implement automation on pg_partman maintenance using pg_cron, but the cron job needs to run with a role that doesn't have LOGIN permissions. My though was that I could do something like this:

SELECT cron.schedule('Partition Maintenance', '0 1 1 * *', $$set role automation_role;CALL partman.run_maintenance_proc()$$);

But this fails with the following error:

34 |    38 |    2614 | partition | postgres        | set role automation_role;CALL partman.run_maintenance_proc()                               | failed    | ERROR: invalid transaction termination                                                                 +| 2023-03-20 15:00:00.158652+00 | 2023-03-20 15:00:00.37488+00
   |       |         |          |                 |                                                                                            |           | CONTEXT: PL/pgSQL function partman.run_maintenance_proc(integer,boolean,boolean) line 43 at COMMIT      |                               |

I tried to find any examples online but couldn't find any. Does anyone knows if it's possible to run multiple statements on pg_cron?

0 Answers0