Customize pg_cron to mimic Oracle’s DBMS_SCHEDULER jobs
schedule sub-minute jobs in Amazon RDS/Aurora PostgreSQL
While many are familiar with basic database job scheduling tasks like nightly backups or hourly reports, modern business requirements often demand more precise timing control. Consider workflows in real-time billing systems that must process transactions at exact sub-minute intervals, or applications that require precise data sampling. When organizations migrate from Oracle to Amazon Relational Database Service (Amazon RDS) for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition , maintaining this level of scheduling precision requires additional effort. Although PostgreSQL's pg_cron extension offers robust scheduling capabilities for most tasks, certain business-critical workflows demand even finer-grained timing control, particularly when sub-minute precision is essential for regulatory compliance or operational continuity.
In this post, we demonstrate how to use pg_cron's APIs to schedule jobs at sub-minute intervals - down to every second. We show you how you can implement precise job scheduling to address your operational needs.
The pg_cron extension version 1.5.0 and above is supported on Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible from versions 12.15, 13.11, 14.8, 15.3 and 16 or later. Refer to Schedule jobs with pg_cron on your Amazon RDS for PostgreSQL or Amazon Aurora for PostgreSQL databases for additional use cases and manageability of the pg_cron extension with Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible.
Solution overview
Our solution covers how to use pg_cron to schedule executing code stored in Amazon Aurora for PostgreSQL including stored procedures, function, and anonymous code blocks. We provide step-by-step instructions to set up the following scheduler job patterns in Amazon Aurora, mirroring the features of Oracle's dbms_scheduler :
· Timestamp or calendar
· Intervals of 0–59 seconds or sub-minute job
· Immediate or only one time
The first two patterns we discuss are built-in capabilities of pg_cron, whereas the third pattern requires us to build a custom solution. To implements the patterns, we demonstrate the following steps:
a. Configure the pg_cron extension in the Aurora PostgreSQL database.
b. Create the required tables, functions, and procedures to test the jobs.
c. Test patterns 1 and 2.
2. Schedule jobs to run immediately or only one time
a. Create supporting objects in the database.
b. Test pattern 3 (running jobs immediately or only once).
Pre-requisites
· Aurora PostgreSQL-Compatible version v12.15 or higher
· PgAdmin 4
Set up the pg_cron extension and implement native functionalities
Modify the custom parameter group associated with your PostgreSQL DB instance by adding pg_cron to the shared_preload_libraries parameter value. See Amazon Aurora PostgreSQL parameters to learn more about parameters. This change requires a database restart.
CREATE EXTENSION pg_cron;
By default, when you create the pg_cron extension, objects related to this extension are created under the postgres database and respective schedules will happen in the postgres database.
If you want to change the database, you can update the static parameter cron.database_name in the parameter group, which requires a restart of the database. To schedule jobs for other databases within your PostgreSQL DB instance, see the example in Scheduling a cron job for a database other than the default database .
The pg_cron extension creates a cron schema and has two tables, cron.job and cron.job_run_details, to maintain the jobs that are in the queue or jobs that have already run. To optimize pg_cron, refer to the documentation and modify the parameters in the database parameter group. The following table describes the functionality of each object type created with the extension
Object | Object Type | Functionality |
|---|---|---|
cron.job | Table | Maintains the scheduled job queue |
cron.job_run_details | Table | Maintains the job run details |
cron.schedule | Function | Schedules the job |
cron.unschedule | Function | Unschedules the job from the queue |
The following SQL script sets up a new schema named mydemo, creates a user with the same name, grants the user access to the cron schema, and creates a sequence and a table named instest within the mydemo schema. The instest table is designed to store records with an auto-incrementing ID, a name column, and a test_date column for storing timestamps.
CREATE SCHEMA mydemo;CREATE USER mydemo WITH PASSWORD 'mydemo';ALTER SCHEMA mydemo OWNER TO mydemo;GRANT USAGE ON SCHEMA cron TO mydemo;CREATE SEQUENCE mydemo.instest_id_seq;CREATE TABLE IF not EXISTS mydemo.instest(id INTEGER NOT NULL DEFAULT NEXTVAL('instest_id_seq'),name text collate pg_catalog."default",test_date timestamp without TIME zone);After the table is created, schedule the job using pg_cron to run at 22:32 hours to insert a record in the instest table. The following command implements the first pattern, which creates a schedule, and the job will be queued in the cron.job table. After the job runs, the cron.job_run_details table will be updated with the acquired job status.
SELECT cron.schedule('test cron','32 22 * * *','insert into mydemo.instest(name,test_date) values (‘’job1’’,clock_timestamp())');schedule---------1221(1 row)
Checking instest table before the schedule starts
SELECT * FROM mydemo.instest;(0 rows)The command creates a new job ‘job1’ in calendar format, created with jobid 1221. As highlighted in the following screenshot, it shows ‘job1’ ran successfully and inserted a record in the table according to the cron schedule.
SELECT * FROM cron.job_run_details WHERE jobid=1221;jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time1221 | 2423 | 17143 | postgres | postgres | insert into mydemo.instest(name,test_date) values ('job1',clock_timestamp()) | succeeded | INSERT 0 1 | 2023-08-02 22:32:00.061557+00 | 2023-08-02 22:32:00.077512+00(1 row)Checking instest table after the schedule
postgres=> select * from mydemo.instest;id | name | test_date906 | job1 | 2023-08-02 22:32:00.066674(1 row)Similarly, with pg_cron extension v1.5.0, you can schedule the jobs with an interval of 1–59 seconds, as detailed in the pg_cron documentation . This gives you more flexibility and enhanced capabilities to schedule jobs more frequently. To test the second pattern, let’s create another job using updated syntax in pg_cron that runs periodically every 30 seconds:
SELECT cron.schedule('sub minute jobs','30 seconds','insert into mydemo.instest(name,test_date) values (''job1'',clock_timestamp())');The command creates a sub minute job in an interval format, created with jobid 1222.** As shown in the following snippet, it started running every 30 seconds from the time it first scheduled. jobid 1222 continues to run periodically until the cron is unscheduled.
select jobid,database,command,status,start_time,end_time from cron.job_run_details where jobid=1222;jobid | database | command | status | start_time | end_time1222 | postgres | insert into mydemo.instest(name,test_date) values ('job1',clock_timestamp()) | succeeded | 2023-08-02 23:06:09.168133+00 | 2023-08-02 23:06:09.169089+001222 | postgres | insert into mydemo.instest(name,test_date) values ('job1',clock_timestamp()) | succeeded | 2023-08-02 23:06:39.165192+00 | 2023-08-02 23:06:39.166147+001222 | postgres | insert into mydemo.instest(name,test_date) values ('job1',clock_timestamp()) | succeeded | 2023-08-02 23:07:09.163107+00 | 2023-08-02 23:07:09.164079+001222 | postgres | insert into mydemo.instest(name,test_date) values ('job1',clock_timestamp()) | succeeded | 2023-08-02 23:07:39.167306+00 | 2023-08-02 23:07:39.168276+001222 | postgres | insert into mydemo.instest(name,test_date) values ('job1',clock_timestamp()) | succeeded | 2023-08-02 23:08:09.167324+00 | 2023-08-02 23:08:09.171208+00(5 rows)As shown in the following screenshot, records were inserted for every 30 seconds, which matches with start_time.
select * from mydemo.instest;906 | job1 | 2023-08-02 22:32:00.066674907 | job1 | 2023-08-02 23:06:09.162023908 | job1 | 2023-08-02 23:06:39.165192909 | job1 | 2023-08-02 23:07:09.164981910 | job1 | 2023-08-02 23:07:39.169693911 | job1 | 2023-08-02 23:08:09.169239(6 rows)Schedule jobs to run immediately and only one time
In this section, we focus on how to run jobs immediately or only one time using pg_cron APIs that resemble Oracle’s dbms_scheduler . To support this use case, we have to extend `pg_cron` functionality by creating custom procedures and functions in PostgreSQL such as to “create random job names”, schedule jobs to run “immediately and only time”, and unscheduled jobs to remove them from the queue.
We recommend creating a dedicated schema called “dbms_job” to isolate and centralize job scheduling functions. This separation provides better security control through a single schema, simplifies audit tracking, and keeps application schemas clean. Since job scheduling impacts database performance and resources, explicitly grant access only to schemas that need to create immediate, one-time jobs. This controlled approach prevents unauthorized job creation and ensures better governance.
CREATE SCHEMA dbms_job;CREATE user dbms_job with PASSWORD 'dbms_job';ALTER SCHEMA dbms_job OWNER TO dbms_job;GRANT USAGE ON SCHEMA cron TO dbms_job;The following table summarizes the parameters.
Object | Functionality |
|---|---|
dbms_job.generate_job_name | Generates the custom job name |
dbms_job.submit_delay | Schedules the job to run immediately or only one time |
dbms_job.reschedule_job | Unschedules the job after it runs and avoid infinite
execution |
dbms_job.check_job_completion | Cleans up the one-time jobs that have run and still in the
queue |
Generate a custom job name
In Oracle, you can create job using the CREATE_JOB procedure to create a single job or create multiple jobs in a single transaction. With the create_job procedure, you can customize to have a dynamic and unique job name based on the type of job or objects in use. This can be helpful in cases where you want to make sure each scheduled job has a distinct and identifiable name created in a single transaction.
To create a similar functionality in PostgreSQL, you can create a custom function called generate_job_name in the dbms_job schema using PL/pgSQL. By default, this function generates a random job name with the prefix CRON_JOB$_****** . However, if your job requires a custom prefix, input the parameter prefix; this is concatenated with $_EXECUTE_ONCE.****** See the following code:
CREATE OR REPLACE FUNCTION dbms_job.generate_job_name(prefix text)RETURNS textLANGUAGE plpgsqlCOST 100 VOLATILEPARALLEL UNSAFEAS $body$DECLARE_job_id text;BEGIN-- get next value from sequence using nextvalSELECT nextval('cron.jobid_seq')::text INTO _job_id;IF prefix IS NULL THENRETURN 'CRON_JOB$_' || _job_id;ELSERETURN prefix || '$_EXECUTE_ONCE' || _job_id;END IF;END;$body$;Schedule the job to run immediately
To replicate Oracle's RUN_JOB procedure functionality in PostgreSQL, we create a procedure dbms_job.submit_delay that enables immediate job execution. While Oracle directly supports one-time immediate job execution, PostgreSQL's pg_cron requires a workaround since it's primarily designed for recurring jobs. The dbms_job.submit_delay procedure/function implements this workaround, It will create a job that starts after 1 second.
It takes the following two input parameters
· l_job_name – The name of the job to be scheduled
· l_job – A command that needs to be run
CREATE OR REPLACE PROCEDURE dbms_job.submit_delay( IN l_job_name text,IN l_job text)LANGUAGE 'plpgsql'AS $body$DECLARE_job_current_seqid bigint;BEGIN-- capture the job ID returned by cron.scheduleSELECT cron.schedule(l_job_name, '1 second', l_job)INTO _job_current_seqid;-- Return completion message for the first jobRAISE notice 'First schedule of jobid % created at %', _job_current_seqid, clock_timestamp();END;$body$;Important: After execution, you'll need to reschedule the job to a past time to prevent future runs, since pg_cron doesn't natively support one-time immediate execution like Oracle's RUN_JOB. In the next section we show a possible workaround you can use to prevent future executions.
Reschedule a job
Unlike Oracle's scheduler that allows setting both start and end dates, pg_cron doesn't natively support one-time job execution or end dates. To mimic Oracle's behavior and prevent recurring executions, we created a way to effectively 'stop' jobs after their first run.
The dbms_job.reschedule_job procedure provides this functionality by rescheduling a job to run in the past, effectively preventing future executions:
CREATE OR REPLACE PROCEDURE dbms_job.reschedule_job(IN job_name text)LANGUAGE plpgsqlAS $BODY$DECLARE_current_time text;_job_current_seqid bigint;_job_what text;BEGIN-- Get the time in the pastSELECT to_char(clock_timestamp() - interval '1 minute', 'MI HH24 DD MM *')INTO _current_time;-- Get the command for the jobSELECT DISTINCT j.commandINTO _job_whatFROM cron.job_run_details jJOIN cron.job jd ON j.jobid = jd.jobidWHERE jd.jobname = job_name;-- Schedule the job and capture its ID directlySELECT cron.schedule(job_name, _current_time, _job_what)INTO _job_current_seqid;RAISE NOTICE 'Job % has been rescheduled at %', _job_current_seqid, clock_timestamp();END;$BODY$;ALTER PROCEDURE dbms_job.reschedule_job(text) OWNER TO postgres;This procedure is particularly useful for batch jobs where manual intervention to stop recurring executions isn't practical.
Configure a job to run immediately or one time
In this section, we explore an example of implementing the third pattern, with a simple use case of record insertion in the table using a database job scheduler. This job runs only one time and the solution has two components
· Stored procedure mydemo.proc_onetimejob_run
· Function mydemo.func_sleep_ins
The stored procedure mydemo.proc_onetimejob_run is designed to generate a job name based on a provided prefix and schedule a one-time job to run immediately. The procedure includes the following steps:
1. The procedure takes an input parameter job_prefix of type text.
2. It declares a variable _job_name of type varchar(255).
3. Inside the procedure body, it calls the function mydemo.generate_job_name with the provided job_prefix to generate a unique job name, which is stored in the _job_name variable.
4. It constructs a SQL statement l_job that calls the function mydemo.func_sleep_ins with the generated _job_name as an argument.
5. It uses the dbms_job.submit_delay function to schedule the l_job SQL statement to run immediately and only one time.
CREATE OR REPLACE PROCEDURE mydemo.proc_onetimejob_run(IN job_prefix text)LANGUAGE plpgsqlAS $body$DECLARE_job_name varchar(255);BEGIN-- Generate a job name based on the provided prefix_job_name := dbms_job.generate_job_name(job_prefix);-- Create schedule using EXECUTE with proper parameter passingCALL dbms_job.submit_delay(_job_name,format('SELECT mydemo.func_sleep_ins(%L)', _job_name));END;$body$;ALTER PROCEDURE mydemo.proc_onetimejob_run(text) owner TO mydemo;The function mydemo.func_sleep_ins is designed to insert a record into the mydemo.instest table with the provided job name and the current timestamp. The function includes the following steps:
1. The function takes an input parameter l_job_name of type text.
2. It declares a variable result of type integer.
3. Inside the function body, it inserts a new record into the mydemo.instest table with the values 'RecordInserted_by_' || l_job_name for the NAME column and clock_timestamp() for the test_date column.
4. It calls the dbms_job.reschedule_job function with the l_job_name as an argument. This function is likely used to reschedule the job to a past timestamp, making sure the job doesn't run more than one time.
5. It assigns the value 42 to the result variable.
6. Finally, it returns the result value.
CREATE OR REPLACE FUNCTION mydemo.func_sleep_ins(l_job_name text)RETURNS integerLANGUAGE 'plpgsql'COST 100VOLATILE PARALLEL UNSAFEAS $BODY$BEGIN-- Insert job execution recordINSERT INTO mydemo.instest (name, test_date)VALUES ('Before_' || l_job_name, clock_timestamp());-- Prevent future runs by rescheduling to pastCALL dbms_job.reschedule_job(l_job_name);RETURN 42;END;$BODY$;ALTER FUNCTION mydemo.func_sleep_ins(text) owner TO mydemo;
Testing the Solution
After you successfully create the objects with the preceding code, let’s test how to create a job to insert a test record. When we run the procedure proc_onetimejob_run, it automatically creates a job that was prefixed with TESTV3_3, runs it immediately (with a 1-second delay), and automatically reschedules the job to a past timestamp to make sure it runs only one time. See the following code:
call mydemo.proc_onetimejob_run(``'TESTV3_3'``);NOTICE: First schedule of ``jobid 1247`` created at 2023-08-14 05:39:55.425445+00CALLValidate Data
select * from mydemo.instest;-[ RECORD 1 ]--------------------------------id | 952name | Before_TESTV3_3$_EXECUTE_ONCE1247test_date | 2023-08-14 05:39:56.50712The following output indicates the only a single record is inserted at 2023-08-14 05:39:56 and it matches with the start_time logged in the cron.job_run_details table. Additionally, the column schedule in the cron.job table that manages the job queue has been updated with a cron format (38 05 14 08 *)**** and it’s given a past timestamp.
Validate Cron tables
select jobid,command,status,start_time,end_time from cron.job_run_details ;-[ RECORD 1 ]------------------------------------------------------------jobid | 1247command | SELECT mydemo.func_sleep_ins('``TESTV3_3``$_EXECUTE_ONCE1247')status | succeededstart_time | 2023-08-14 05:39:56.504218+00end_time | 2023-08-14 05:39:56.510535+00select jobid,schedule,command,jobname from cron.job;-[ RECORD 1 ]----------------------------------------------------------jobid | 1247schedule | 38 05 14 08 *command | SELECT mydemo.func_sleep_ins('TESTV3_3$_EXECUTE_ONCE1247')jobname | TESTV3_3$_EXECUTE_ONCE1247In summary, a job is created with the prefix TESTV3_3 with jobid 1247. From cron.job_run_details, we see that job has been run immediately and one time, and the mydemo.instest table has a record inserted successfully. We also observed that the schedule has been updated to a past date timestamp in the cron.job table, to make sure it won’t run again.
Maintenance and cleanup
In order to keep pg_cron healthy, it’s recommended to perform regular cleanup activities of the job queue that were created to run one time and immediately. These jobs are required to be removed from the queue because they won’t be run again. To configure this maintenance to occur at a regular frequency, you can use a custom function that identifies the job status based on job status and job name. In the following snippet, we unschedule jobs with job_name as ontimestamp with the status succeeded:
CREATE OR replace FUNCTION mydemo.check_job_completion()returns voidLANGUAGE 'plpgsql'cost 100 volatilePARALLEL unsafeAS$body$DECLARE_job_name VARCHAR(255) := 'onetimestamp';_job_status text;BEGIN``-- Get the status of the job``SELECT j.statusINTO _job_statusFROM cron.job_run_details jjoin cron.job jdON j.jobid = jd.jobidWHERE jd.jobname = _job_nameORDER BY j.end_time DESC limit 1;-- Check if the job has completedIF _job_status = 'succeeded' THEN*-- Unscheduling the job*perform cron.unschedule(_job_name);RAISE notice 'Job "%s" is successfully unscheduled.', _job_name;ELSERAISE warning 'Job "%s" is either running or has not completed yet.', _job_name;END IF;END;$body$;Apart from removing the immediate jobs that were scheduled to run only one time from the queue, it’s important to conduct frequent maintenance of the cron.job and cron.job_run_details tables because the records in cron.job_run_details are not cleaned automatically. Every user that can schedule cron jobs also has permission to delete their own cron.job_run_details records.
Especially when you have jobs that run every few seconds, it can be a good idea to clean them up regularly, which can be done using pg_cron itself:
-- Delete old cron.job_run_details records of the current user every day at noon
SELECT cron.schedule('delete-job-run-details', '0 12 * * *', $$DELETE FROM cron.job_run_details WHERE end_time < now() - interval '7 days'$$);You can unschedule all jobs from the cron.job table by running the command select cron.unschedule(jobname), and then drop the extension by running the command drop extension pg_cron.
Conclusion
By combining the capabilities of the pg_cron extension and custom procedures and functions, you can build a robust job scheduling system in Amazon RDS for PostgreSQL and Aurora PostgreSQL-Compatible that closely resembles the behavior of Oracle's dbms_job. This setup enables you to define jobs that run at intervals of less than a minute, effectively catering to sub-minute job scheduling requirements. The capabilities of this custom solution can provide flexibility and efficiency in managing different types of jobs within the database.
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article