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?