Schedule purge of log tables

[JBHighHair]

Here is how to schedule a daily purge of log tables timetable.log and timetable.execution_log
Use whatever interval you desire (rather than 120 days)

Using SQL...

DO $$
DECLARE v_chain_id bigint;
BEGIN
    -- Get the chain id
    INSERT INTO timetable.chain (chain_name, max_instances, live, run_at) VALUES ('pg_timetable Purge Logs', 1, TRUE, '0 0 * * *')
    RETURNING chain_id INTO v_chain_id;

    -- Add task
    INSERT INTO timetable.task (chain_id, task_name, task_order, kind, command) 
    SELECT v_chain_id, 'Purge log', 10, 'SQL', '-- Purge old records in timetable.log.
WITH rows_to_delete AS (
SELECT pid FROM timetable.log
WHERE pid IN ( SELECT pid FROM timetable.log WHERE ts < now() - interval ''120 days'' )
LIMIT 10000 -- delete in blocks
)
DELETE FROM timetable.log
WHERE pid IN (SELECT pid FROM rows_to_delete);
';

    -- Add task
    INSERT INTO timetable.task (chain_id, task_name, task_order, kind, command) 
    SELECT v_chain_id, 'Purge execution_log', 20, 'SQL', '-- Purge old records in timetable.execution_log.
WITH rows_to_delete AS (
SELECT txid FROM timetable.execution_log
WHERE txid IN ( SELECT txid FROM timetable.execution_log WHERE last_run < now() - interval ''120 days'' )
LIMIT 10000 -- delete in blocks
)
DELETE FROM timetable.execution_log
WHERE txid IN (SELECT txid FROM rows_to_delete);';

END $$

Using YAML Chains....

chains:

  - name: "pg_timetable Purge Logs"
    schedule: "0 0 * * *"  # Midnight daily
    live: true # enable/disable chain
    max_instances: 1 # max parallel executions
    self_destruct: false # delete after success
    exclusive: false # pause other chains while running

    tasks:
      - name: "Purge log"
        kind: "SQL"
        command: |
                 -- Purge old records in timetable.log.
                 WITH rows_to_delete AS (
                 SELECT pid FROM timetable.log
                 WHERE pid IN ( SELECT pid FROM timetable.log WHERE ts < now() - interval '120 days' )
                 LIMIT 10000 -- delete in blocks
                 )
                 DELETE FROM timetable.log
                 WHERE pid IN (SELECT pid FROM rows_to_delete);
      - name: "Purge execution_log"
        kind: "SQL"
        command: |
                 -- Purge old records in timetable.execution_log.
                 WITH rows_to_delete AS (
                 SELECT txid FROM timetable.execution_log
                 WHERE txid IN ( SELECT txid FROM timetable.execution_log WHERE last_run < now() - interval '120 days' )
                 LIMIT 10000 -- delete in blocks
                 )
                 DELETE FROM timetable.execution_log
                 WHERE txid IN (SELECT txid FROM rows_to_delete);