Programador de eventos

Para este tipo de tareas, en PostgreSQL se usa la extensión pg_cron. Permite crear tareas programadas con la sintaxis de cron (como en los sistemas Unix).

Con pg_cron puedes automatizar:

Primero hay que instalar pg_cron en el servidor, añadirlo a shared_preload_libraries y reiniciar PostgreSQL. Después, la extensión se crea en la base de datos:

MySQL 8.4
CREATE EXTENSION IF NOT EXISTS pg_cron;

Empecemos por lo más sencillo: una tarea que se ejecutará una sola vez en un momento determinado:

pg_cron no tiene un tipo de horario específico para una ejecución única. Para eso se usa un programador externo de la aplicación o se crea una tarea temporal que se elimina después de ejecutarse.

Por ejemplo, esta tarea borrará los registros de log antiguos la próxima vez que lleguen las 3:00 y después se eliminará a sí misma:

MySQL 8.4
SELECT cron.schedule(
    'cleanup_old_logs_once',
    '0 3 * * *',
    $command$
    DO $$
    BEGIN
        DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days';
        PERFORM cron.unschedule('cleanup_old_logs_once');
    END;
    $$;
    $command$
);

Normalmente, el horario '0 3 * * *' lanza la tarea todos los días a las 3:00. Pero tras la primera ejecución la tarea llama a cron.unschedule() con su propio nombre, así que no volverá a ejecutarse.

Lo más habitual es que las tareas tengan que ejecutarse periódicamente: cada día, cada hora o cada minuto:

MySQL 8.4
SELECT cron.schedule(
    'cleanup_old_logs',
    '0 3 * * *',
    'DELETE FROM logs WHERE created_at < NOW() - INTERVAL ''30 days'''
);

Esta tarea se ejecutará todos los días a las 3:00 y eliminará los registros de log con más de 30 días de antigüedad.

Analicemos la sintaxis:

• cron.schedule(): función para crear una tarea programada
• 'cleanup_old_logs': nombre de la tarea
• '0 3 * * *': horario en formato cron (minuto, hora, día del mes, mes, día de la semana)
• el último parámetro: el comando SQL que se ejecutará

Formato del horario cron:

MySQL 8.4
SELECT cron.schedule(
    'update_statistics_hourly',
    '0 * * * *',
    $$
    UPDATE product_stats SET
        total_sales = (SELECT SUM(amount) FROM orders WHERE product_id = product_stats.product_id),
        last_updated = NOW()
    $$
);

Esta tarea actualizará las estadísticas de ventas cada hora (al principio de cada hora).

Ejemplos de horarios:

• '*/5 * * * *': cada 5 minutos
• '0 * * * *': cada hora (en punto)
• '0 0 * * *': todos los días a medianoche
• '0 0 * * 0': todos los domingos a medianoche
• '0 9 1 * *': el día 1 de cada mes a las 9:00
• '30 seconds': cada 30 segundos en pg_cron 1.5 y posteriores

El intervalo en segundos se indica como una cadena aparte, no como un sexto campo de la expresión cron. pg_cron admite valores de 1 a 59 segundos.

A veces necesitas que una tarea funcione solo durante un periodo determinado.

En el horario de pg_cron no se puede indicar una fecha en la que la tarea termine automáticamente. Por ejemplo, una tarea que limpie los logs a diario durante los próximos 30 días se crea con un horario normal:

MySQL 8.4
SELECT cron.schedule(
    'temporary_log_cleanup',
    '0 0 * * *',
    $$DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days'$$
);

Para que la tarea se detenga sola, se usa el mismo truco que en el ejemplo de ejecución única: se crea la tarea stop_temporary_log_cleanup para una fecha dentro de 30 días. Su comando tiene este aspecto:

MySQL 8.4
DO $$
BEGIN
    PERFORM cron.unschedule('temporary_log_cleanup');
    PERFORM cron.unschedule('stop_temporary_log_cleanup');
END;
$$;

La primera llamada elimina la tarea de limpieza y la segunda, la tarea auxiliar. En el horario de la tarea auxiliar se indican el minuto, la hora, el día y el mes de la fecha que llegará dentro de 30 días.

Para ver las tareas programadas a las que tiene acceso el usuario actual:

MySQL 8.4
SELECT * FROM cron.job;

Un usuario normal solo ve sus propias tareas. Un superusuario o un rol con el atributo BYPASSRLS puede ver también las tareas de otros usuarios.

Para ver el historial de ejecución de las tareas:

MySQL 8.4
SELECT * FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 10;

Eliminar una tarea programada:

MySQL 8.4
SELECT cron.unschedule('cleanup_old_logs');

O por el ID de la tarea:

MySQL 8.4
SELECT cron.unschedule(42);  -- donde 42 es el jobid de la tabla cron.job

Modificar una tarea:

El horario, el comando y el estado de actividad de una tarea existente se pueden cambiar con cron.alter_job():

MySQL 8.4
SELECT cron.alter_job(
    42,
    schedule := '0 */2 * * *'
);

Aquí 42 es el jobid de la tarea en la tabla cron.job.

Aspectos importantes al trabajar con tareas de pg_cron

1. Permisos: normalmente un superusuario instala la extensión y, después, a los usuarios normales se les puede conceder USAGE sobre el esquema cron. La tarea se ejecuta con los permisos del usuario que la creó.

2. Zona horaria: las expresiones cron usan el ajuste cron.timezone, que por defecto es GMT. Puedes comprobar el valor actual con el comando SHOW cron.timezone;.

3. Intervalos: pg_cron 1.5 y posteriores admiten intervalos de 1 a 59 segundos. La frecuencia debe elegirse teniendo en cuenta el coste de la tarea y la carga.

4. Ejecuciones simultáneas: pg_cron no lanza a la vez varias instancias de la misma tarea. Si llega la hora de la siguiente ejecución mientras la tarea sigue en marcha, esa nueva ejecución se pone en cola.

5. Registro de ejecuciones: con el ajuste cron.log_run activado, la información sobre las ejecuciones se guarda en la tabla cron.job_run_details. Por defecto, este registro está activado.

¿Cuál es el intervalo mínimo que puedes usar para tareas recurrentes?

Las tareas programadas son una herramienta muy potente para automatizar tareas rutinarias en la base de datos. Ayudan a mantener los datos limpios, actualizar estadísticas y realizar operaciones de mantenimiento sin intervención de los desarrolladores. 🚀