PostgreSQL DBMS_JOB compatibility extension

August 25, 2021

## PostgreSQL DBMS_JOB compatibility extension

pg_dbms_job is a new PostgreSQL extension to create, manage and use Oracle-style
DBMS_JOB scheduled job. The use and behavior is just like with the DBMS_JOB Oracle
package.

pg_dbms_job v1.0.1 has been released, this is the first release of the extension
which is compatible from PostgreSQL 9.1 to current.

It allows to manage scheduled jobs from a job queue or to execute immediately jobs asynchronously. A job definition consist on a code to execute, the next date of execution and how often the job is to be run. A job runs a SQL command, plpgsql code or an existing stored procedure.

If the submit stored procedure is called without the next_date (when) and interval (how often) attributes, the job is executed immediately in an asynchronous process. If interval is NULL and that next_date is lower or equal to current timestamp the job is also executed immediately as an asynchronous process. In all other cases the job is to be started when appropriate but if interval is NULL the job is executed only once and the job is deleted.

If a scheduled job completes successfully, then its new execution date is placed in next_date. The new date is calculated by evaluating the SQL expression defined as interval. The interval parameter must evaluate to a time in the future.

This extension consist in a SQL script to create all the objects related to its operation and a daemon that must be run attached to the database where jobs are defined. The daemon is responsible to execute the queued asynchronous jobs and the scheduled ones. It can be run on the same host of the database, where the jobs are defined, or on any other host. The schedule time is taken from the database host not where the daemon is running.

The number of jobs that can be executed at the same time is limited to 1000 by default. If this limit is reached the daemon will wait that a process ends to run a new one.

The use of an external scheduler daemon instead of a background worker is a choice, being able to fork thousands of sub-processes from a background worker is not a good idea.

The job execution is caused by a NOTIFY event received by the scheduler when a new job is submitted or modified. The notifications are polled every 0.1 second. When there is no notification the scheduler polls every job_queue_interval seconds (5 seconds by default) the tables where job definition are stored. This mean that at worst a job will be executed job_queue_interval seconds after the next execution date defined.

See pg_dbms_job [documentation](https://github.com/MigOpsRepos/pg_dbms_job#readme) for more information.

## Links & Credits

pg_dbms_job is an open project under the PostgreSQL license created by Gilles Darold at [MigOps Inc](https://migops.com/)
as part of the improvement of Ora2Pg. Any contribution to build a better tool is welcome. You can
send your ideas, features requests or patches using the GitHub tools.

**Links :**

* Download: [https://github.com/MigOpsRepos/pg_dbms_job/releases/](https://github.com/MigOpsRepos/pg_dbms_job/releases/)
* Support: use GitHub report tool at [https](https://github.com/MigOpsRepos/pg_dbms_job/issues)

## About pg_dbms_job

The pg_dbms_job extension is an original work of [MigOps Inc](https://migops.com/), MigOPs is specialized in migration to PostgreSQL and PostgreSQL support. If you need more information please [contact us](https://www.migops.com/contact-us/)

Documentation at [https://github.com/MigOpsRepos/pg_dbms_job#readme](https://github.com/MigOpsRepos/pg_dbms_job#readme)