migopsrepos / pg_dbms_job Goto Github PK

View Code? Open in Web Editor NEW

39.0 39.0 7.0 122 KB

License: PostgreSQL License

Makefile 0.57% Perl 22.70% PLpgSQL 60.77% Raku 15.96%

pg_dbms_job's People

Contributors

Stargazers

Watchers

Forkers

datadevopscloud zhuomingliang xuebiaoyan cvas71 terry1504 hnusttyj530 nettrash

pg_dbms_job's Issues

Keeping entries in the table for the duration of the task

Hi,
is it possible to change the behavior of entries about tasks in tables?
This behavior is completely different from oracle

Asynchronous tasks:
Currently, when the job is running, a record in the table does not exist. Appears when the quest ends.
Example:

do
$$
declare
j bigint;
begin
j:=dbms_job.submit('begin perform pg_sleep(30); end;');
end;
$$

Monitor: when record appears....
SELECT * FROM dbms_job.all_async_jobs;
You can see the session:
select * from pg_stat_activity where query like '%pg_sleep(30)%' and pid<>pg_backend_pid();

Scheduled tasks:
The entry in the table remains until the task starts, then the record disappears from the table.

The idea is to be able to monitor job tables from inside the job.

Regards Peter

Connection Issue with password that contains #

It seems that the password with # in pg_dbms_job.conf isn't loaded correctly.
If I add 1234#abc as the password, it always pick up 1234 as the password and resulted as 'password authentication failed'.
Any workaround can be used here? I tried to escape the #, but it didn't work.

Running on standby causes readonly connection error

Hi,

we run Pg in a clustered environment using patroni. We want to run the daemon on each of the Pg nodes connecting to local server (localhost) but will only execute if it is primary (we can handle the primary check in the plpgsql job itself).
However the daemon on the secondary keep throwing "cannot execute UPDATE in read-only transaction". This is expected.
So

Is there a way to stop the daemon throwing that error and just sit doing nothing while the node is in recovery?
Is there an inbuilt log rotation?

perl regression not pass in pg15

[postgres@qbtp15 pg_dbms_job]$ make installcheck
/bin/prove
t/01_lint.t ............ ok   
t/02_basic.t ........... ok     
t/03_async.t ........... 5/7 
#   Failed test 'Found SET
# 1 async job in the history'
#   at t/03_async.t line 43.
t/03_async.t ........... 7/7 # Looks like you failed 1 test of 7.
t/03_async.t ........... Dubious, test returned 1 (wstat 256, 0x100)
Failed 1/7 subtests 
t/04_signal.t .......... 5/8 
#   Failed test 'Found SET
# 1 async job in the history'
#   at t/04_signal.t line 37.
t/04_signal.t .......... 7/8 
#   Failed test 'Found SET
# 2 async job in the history'
#   at t/04_signal.t line 56.
# Looks like you failed 2 tests of 8.
t/04_signal.t .......... Dubious, test returned 2 (wstat 512, 0x200)
Failed 2/8 subtests 
t/05_async_error.t ..... 4/5 
#   Failed test 'Found SET
# 1 async job in the history'
#   at t/05_async_error.t line 26.
t/05_async_error.t ..... 5/5 # Looks like you failed 1 test of 5.
t/05_async_error.t ..... Dubious, test returned 1 (wstat 256, 0x100)
Failed 1/5 subtests 
t/06_queue_interval.t .. 4/6 
#   Failed test 'No async job found in the history: SET
# 0'
#   at t/06_queue_interval.t line 26.
t/06_queue_interval.t .. 5/6 
#   Failed test 'Found SET
# 1 async job in the history'
#   at t/06_queue_interval.t line 34.
t/06_queue_interval.t .. 6/6 # Looks like you failed 2 tests of 6.
t/06_queue_interval.t .. Dubious, test returned 2 (wstat 512, 0x200)
Failed 2/6 subtests 
t/07_scheduled.t ....... 4/11 
#   Failed test 'No async job found in the history: SET
# 0'
#   at t/07_scheduled.t line 26.
t/07_scheduled.t ....... 5/11 
#   Failed test 'Found SET
# 1 async job in the history'
#   at t/07_scheduled.t line 34.
t/07_scheduled.t ....... 6/11 
#   Failed test 'Found SET
# 2 async job in the history'
#   at t/07_scheduled.t line 42.
t/07_scheduled.t ....... 7/11 
#   Failed test 'Call to broken procedure'
#   at t/07_scheduled.t line 49.
t/07_scheduled.t ....... 8/11 
#   Failed test 'Found SET
# 3 async job in the history'
#   at t/07_scheduled.t line 56.

#   Failed test 'Call to broken procedure'
#   at t/07_scheduled.t line 61.
t/07_scheduled.t ....... 10/11 
#   Failed test 'Found SET
# 5 async job in the history'
#   at t/07_scheduled.t line 68.
t/07_scheduled.t ....... 11/11 # Looks like you failed 7 tests of 11.
t/07_scheduled.t ....... Dubious, test returned 7 (wstat 1792, 0x700)
Failed 7/11 subtests 
t/08_procedures.t ...... 4/21 ERROR:  syntax error at or near "7"
LINE 2: 7);
        ^

#   Failed test 'Removing job SET
# 7'
#   at t/08_procedures.t line 30.

#   Failed test 'Job SET
# 7 have been removed'
#   at t/08_procedures.t line 33.
t/08_procedures.t ...... 8/21 ERROR:  syntax error at or near "8"
LINE 2: 8, date_trunc('day', current_timestamp) + '1 year'::interval...
        ^

#   Failed test 'Change next_date for job SET
# 8'
#   at t/08_procedures.t line 51.

#   Failed test 'New next_date for job SET
# 8 have been modified'
#   at t/08_procedures.t line 56.
ERROR:  syntax error at or near "8"
LINE 2: 8, 'date_trunc(''day'', current_timestamp) + ''1 month''::in...
        ^

#   Failed test 'Change interval for job SET
# 8'
#   at t/08_procedures.t line 60.

#   Failed test 'New interval for job SET have been modified'
#   at t/08_procedures.t line 65.
ERROR:  syntax error at or near "8"
LINE 2: 8, 'BEGIN PERFORM version(); END;');
        ^

#   Failed test 'Change what for job SET
# 8'
#   at t/08_procedures.t line 73.

#   Failed test 'New what for job SET
# 8 have been modified'
#   at t/08_procedures.t line 78.
ERROR:  syntax error at or near "8"
LINE 2: 8, NULL, NULL, NULL);
        ^

#   Failed test 'Change all for job SET
# 8 to NULL'
#   at t/08_procedures.t line 82.

#   Failed test 'Job SET
# 8 is the same, nothing changed'
#   at t/08_procedures.t line 87.
ERROR:  syntax error at or near "8"
LINE 2: 8, 'VACUUM ANALYZE;', date_trunc('day', current_timestamp) +...
        ^

#   Failed test 'Change all for job SET
# 8'
#   at t/08_procedures.t line 91.

#   Failed test 'Job SET
# 8 have been executed and removed'
#   at t/08_procedures.t line 96.
t/08_procedures.t ...... 21/21 # Looks like you failed 12 tests of 21.
t/08_procedures.t ...... Dubious, test returned 12 (wstat 3072, 0xc00)
Failed 12/21 subtests 
t/09_run.t ............. 1/12 ERROR:  syntax error at or near "9"
LINE 2: 9, true);
        ^

#   Failed test 'Call to broken procedure'
#   at t/09_run.t line 30.
t/09_run.t ............. 6/12 
#   Failed test 'Good, no job found in the history'
#   at t/09_run.t line 36.
ERROR:  syntax error at or near "9"
LINE 2: 9, false);
        ^

#   Failed test 'Call to run procedure for immediate execution of job SET
# 9'
#   at t/09_run.t line 41.

#   Failed test 'Job SET
# 9 have been executed in foreground'
#   at t/09_run.t line 46.
ERROR:  syntax error at or near "9"
LINE 2: 9, true);
        ^

#   Failed test 'Call to run procedure for background execution of job SET
# 9'
#   at t/09_run.t line 51.
t/09_run.t ............. 10/12 
#   Failed test 'Found SET
# 2 async job in the history'
#   at t/09_run.t line 57.
ERROR:  relation "t1" does not exist
LINE 1: SET ROLE regress_dbms_job_user; SELECT count(*) FROM t1;
                                                             ^

#   Failed test 'Job SET
# 9 have been executed in background, SET rows'
#   at t/09_run.t line 62.
t/09_run.t ............. 12/12 # Looks like you failed 7 tests of 12.
t/09_run.t ............. Dubious, test returned 7 (wstat 1792, 0x700)
Failed 7/12 subtests 

Test Summary Report
-------------------
t/03_async.t         (Wstat: 256 Tests: 7 Failed: 1)
  Failed test:  6
  Non-zero exit status: 1
t/04_signal.t        (Wstat: 512 Tests: 8 Failed: 2)
  Failed tests:  5, 8
  Non-zero exit status: 2
t/05_async_error.t   (Wstat: 256 Tests: 5 Failed: 1)
  Failed test:  4
  Non-zero exit status: 1
t/06_queue_interval.t (Wstat: 512 Tests: 6 Failed: 2)
  Failed tests:  4-5
  Non-zero exit status: 2
t/07_scheduled.t     (Wstat: 1792 Tests: 11 Failed: 7)
  Failed tests:  4-10
  Non-zero exit status: 7
t/08_procedures.t    (Wstat: 3072 Tests: 21 Failed: 12)
  Failed tests:  5-6, 10-13, 15-20
  Non-zero exit status: 12
t/09_run.t           (Wstat: 1792 Tests: 12 Failed: 7)
  Failed tests:  5-11
  Non-zero exit status: 7
Files=9, Tests=87, 122 wallclock secs ( 0.04 usr  0.01 sys +  0.82 cusr  0.41 csys =  1.28 CPU)
Result: FAIL```

pg_dbms_job does not work when restarting PG without killing the process of pg_dbms_job

How to?

first
adding a new job,like

SELECT dbms_job.submit(
'INSERT INTO test1(id) SELECT GENERATE_SERIES(1,50);', -- what
LOCALTIMESTAMP + '3 seconds'::interval, -- start the job in 3 seconds
'LOCALTIMESTAMP + ''6 seconds''::interval' -- repeat the job every 6 seconds
);

Second
pg_ctl -D xxx stop or kill -9 xxx

Third
pg_ctl -D xxx start

Final
Neither current job nor newly added job is working

DEBUG log
2022-07-21 18:51:54 [13550]: [810] DEBUG: Found 0 asynchronous jobs to run
2022-07-21 18:51:54 [13550]: [705] DEBUG: Get scheduled jobs to run
2022-07-21 18:51:54 [13550]: [741] DEBUG: Found 1 scheduled jobs to run
2022-07-21 18:51:57 [13550]: [810] DEBUG: Found 0 asynchronous jobs to run
2022-07-21 18:51:57 [13550]: [705] DEBUG: Get scheduled jobs to run
2022-07-21 18:51:57 [13550]: [741] DEBUG: Found 0 scheduled jobs to run

no log of "executing job xxx"

Looking for problems

see function get_scheduled_jobs in the perl file of pg_dbms_job
...
if (!defined $sth)
{
dprint('ERROR', "can't prepare statement, $DBI::errstr");
$config_invalidated = 1 if (!$SINGLE); // <--- If error occurs, we set $config_invalidated = 1
return %alljobs;
}
...

see the call of get_scheduled_jobs
...
if ($scheduled_count || $startup)
{
%SCHEDULED_JOBS = get_scheduled_jobs();
$previous_scheduled_exec = time;
if ($config_invalidated) { // <--- do loop next time
sleep(3);
$startup = 1;
next;
}
}
$config_invalidated = 0; // <---- here is reset flag
...

so...if we stop PG without kill pg_dbms_job, $config_invalidated will be set to 1
in get_scheduled_jobs. and we have no chance to modify $config_invalidated

Change the code

I add $config_invalidated = 0 before "return %alljobs;" in get_scheduled_jobs
I don't know if this is the right thing to do?

Password Logging

Logging Passwords is considered as a huge security and need to address this at the earliest to make use of this extension in production. Please help fixing the issue.

Thank you

Will pg_dbms_job be available as a rpm package

Hi,

Just a quick question.
Will pg_dbms_job also be available as a rpm package?
I have some servers where I don't have the oppertunity to connect with git.

Thanks in advance

Error not logging upon termination

When scheduled/asynchronous jobs are terminated using kill -9 on the daemon host, the daemon should ensure all_jobs/all_scheduled_jobs is also getting updated with an error message in the database tables to identify stale jobs. Ideally all terminated sessions should log errors in the table such that it can help identify the failed jobs during such an unexpected events.
I see the similar behavior in the scenarios when the daemon server is terminated or a daemon pid is terminated as well.

currently, "failure" column in all_jobs/all_scheduled_jobs is empty after being terminated. Is it by design or a bug?

One question regarding quote_literal

I have one question about the use of quote_literal.
In source file pg_dbms_job--1.3.0.sql

CREATE PROCEDURE dbms_job.interval(
		jobid           IN  bigint,
		job_interval  IN  text)
    LANGUAGE PLPGSQL 
    AS $$
(omit)
        UPDATE dbms_job.all_scheduled_jobs SET interval = quote_literal(job_interval) WHERE job = jobid;
(omit)
END;
$$;


CREATE FUNCTION dbms_job.submit(
		jobid         OUT   bigint,
		what          IN    text,
		next_date     IN    timestamp(0) with time zone DEFAULT current_timestamp,
		job_interval  IN    text DEFAULT NULL,
		no_parse      IN    boolean DEFAULT false)
    RETURNS bigint
    LANGUAGE PLPGSQL
    AS $$
(omit)
    IF job_interval IS NOT NULL THEN
        INSERT INTO dbms_job.all_scheduled_jobs (what,next_date,interval) VALUES ($2,$3,$4) RETURNING job INTO jobid;
    ELSE
(omit)
END;
$$;

Why use quote_literal(job_interval) in dbms_job.interval, and not use quote_literal($4) in dbms_job.submit.

On Pg restart, pg_dbms_job fails to establish connection again

On Pg restart, pg_dbms_job fails to establish connection again. its stuck in "can't execute statement: no connection to server" error state.

Is it possible to add pg_dbms_job to shared_preload_libraries

Hi There,

Instead of run the pg_dbms_job process manually, is it possible to add pg_dbms_job to shared_preload_libraries, and postgres can start and manage the process. Just like pg_cron?

Thank you.

[Bug] different channels in notify and listen

When a scheduled job is submitted, there will be a trigger to notify the daemon using the channel "dbms_job_scheduled_notify" (

pg_dbms_job/sql/pg_dbms_job--1.5.0.sql

Line 350 in 4d6884c

PERFORM pg_notify('dbms_job_scheduled_notify', TG_OP || ':' || NEW.job);

). However, the daemon listens to a topic named "dbms_job_cache_invalidate" (

pg_dbms_job/bin/pg_dbms_job

Line 697 in 4d6884c

$ldbh->do("LISTEN dbms_job_cache_invalidate");

), and can thus not receive the notification on time.

how to install pg_dbms_job on windows 10 ?

Hello Gilles
Is there any chance to install pg_dbms_job on a windows 10 platform ?
Any guideline/procedure will be highly appreciated.
Thanks in advanced.
Edouard