Creating and Managing Scheduled Jobs

Context

Time-consuming jobs, such as summarizing statistics or synchronizing data from another database, affect service performance if they are performed during the daytime and incur overtime hours if performed at night. To solve this problem, the database is compatible with the scheduled job function in the ORA database. You can create scheduled jobs that are automatically triggered to reduce O&M workload.

This function calls APIs provided by the DBE_SCHEDULER and DBE_TASK packages to create scheduled jobs, execute jobs automatically, delete jobs, and modify job attributes (including job ID, the enabled/disabled status of a job, job triggering time, triggering interval, and job contents). The DBE_SCHEDULER API is recommended to ensure high availability and reliability and support more flexible job scheduling. For details about the API description and migration guide examples, see DBE_SCHEDULER.

Managing Scheduled Jobs

1. Create a test table.

   1	gaussdb=# CREATE TABLE test(id int, time date);

   If the following information is displayed, the creation is successful:

   1	CREATE TABLE

2. Create a customized stored procedure.

   1 2 3 4 5 6 7 8 9

   gaussdb=# CREATE OR REPLACE PROCEDURE PRC_JOB_1() AS N_NUM integer :=1; BEGIN FOR I IN 1..1000 LOOP INSERT INTO test VALUES(I,SYSDATE); END LOOP; END; /

   If the following information is displayed, the creation is successful:

   1	CREATE PROCEDURE

3. Create a job.
   • Create a job with unspecified job_id and execute the PRC_JOB_1 stored procedure every minute.

     1 2 3 4 5

     gaussdb=# call dbe_task.submit('call public.prc_job_1(); ', sysdate, 'interval ''1 minute''', :a); job ----- 1 (1 row)

   • Specify job_id to create a job. The value of job_id ranges from 1 to 32767.

     1 2 3 4 5

     gaussdb=# call dbe_task.id_submit(2,'call public.prc_job_1(); ', sysdate, 'interval ''1 minute'''); isubmit --------- (1 row)

4. View details of jobs created by the current user.

   1 2 3 4 5

   gaussdb=# select job,dbname,start_date,last_date,this_date,next_date,broken,status,interval,failures,what from my_jobs; job | dbname | start_date | last_date | this_date | next_date | broken | status | interval | failures | what -----+--------+---------------------+----------------------------+----------------------------+---------------------+--------+--------+---------------------+----------+--------------------------- 1 | testdb | 2017-07-18 11:38:03 | 2017-07-18 13:53:03.607838 | 2017-07-18 13:53:03.607838 | 2017-07-18 13:54:03 | n | s | interval '1 minute' | 0 | call public.prc_job_1(); (1 row)

5. Stop a job.

   1 2 3 4 5

   gaussdb=# call dbe_task.finish(1,true); broken -------- (1 row)

6. Start a job.

   1 2 3 4 5

   gaussdb=# call dbe_task.finish(1,false); broken -------- (1 row)

7. Modify job attributes.
   • Modify the Next_date parameter information about a job.

     1 2 3 4 5 6

     -- Set Next_date of Job1 to 1 hour so that Job1 will be executed in one hour. gaussdb=# call dbe_task.next_time(1, sysdate+1.0/24); next_date ----------- (1 row)

   • Modify the Interval parameter about a job.

     1 2 3 4 5 6

     -- Set Interval of Job1 to 1. gaussdb=# call dbe_task.interval(1,'sysdate + 1.0/24'); interval ---------- (1 row)

   • Modify the What parameter about a job.

     1 2 3 4 5 6

     -- Set What to the SQL statement insert into public.test values(333, sysdate+5); for Job1. gaussdb=# call dbe_task.content(1,'insert into public.test values(333, sysdate+5);'); what ------ (1 row)

   • Modify Next_date, Interval, and What parameters about a job.

     1 2 3 4 5

     gaussdb=# call dbe_task.update(1, 'call public.prc_job_1();', sysdate, 'interval ''1 minute'''); change -------- (1 row)

8. Delete a job.

   1 2 3 4 5

   gaussdb=# call dbe_task.cancel(1); remove -------- (1 row)

9. View the job execution status.

   If a job fails to be automatically executed (that is, the value of job_status is 'f'), contact the administrator to view the pg_log run log to view the failure information of the job.

   From detail error msg, you can see the failure causes.

   LOG:  Execute Job Detail: 
           job_id: 1 
           what: call public.test();  
           start_date: 2017-07-19 23:30:47.401818 
           job_status: failed 
           detail error msg: relation "test" does not exist 
           end_date: 2017-07-19 23:30:47.401818 
           next_run_date: 2017-07-19 23:30:56.855827 

10. Set job permissions.
    • During the creation of a job, the job is bound to the user and database that created the job. Accordingly, the user and database are added to dbname and log_user columns in the pg_job system catalog, respectively.
    • If the current user is a database administrator, system administrator, or the user (log_user of pg_job) who created the job, the user has permissions to delete or modify job parameters using the remove, change, next_data, what, or interval parameter. Otherwise, the system displays a message indicating that the user has no permissions to perform operations on this job.
    • If the current database is the one that created a job, (that is, dbname in pg_job), you can delete or modify parameter settings of the job using the cancel, update, next_data, content, or interval parameter.
    • When deleting the database that created a job, (that is, dbname in pg_job), the system automatically deletes the job records of the database.
    • When deleting the user who created a job, (that is, log_user in pg_job), the system automatically deletes the job records of the user.

11. Manage job concurrency.
    You can configure the GUC parameter job_queue_processes to adjust the number of jobs running at the same time.

    • Setting job_queue_processes to 0 indicates that the scheduled job function is disabled and all jobs will not be executed.
    • Setting job_queue_processes to a value that is greater than 0 indicates that the scheduled job function is enabled and this value is the maximum number of jobs that can be concurrently processed.

    Too many concurrent jobs consume many system resources, so you need to set the number of concurrent jobs to be processed. If the current number of concurrent jobs reaches the value of job_queue_processes and some of them expire, these jobs will be postponed to the next polling period. Therefore, you are advised to set the polling interval (the Interval parameter of the submit API) based on the execution duration of each job to avoid the problem that jobs in the next polling period cannot be properly processed because of overlong job execution time.

    Note: For clusters that do not use jobs, set job_queue_processes to 0 to disable job functions to reduce the resource consumption.