1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
DO $$
BEGIN
IF pg_catalog.current_database() OPERATOR(pg_catalog.<>) pg_catalog.current_setting('cron.database_name') AND pg_catalog.current_database() OPERATOR(pg_catalog.<>) 'contrib_regression' THEN
RAISE EXCEPTION 'can only create extension in database %',
pg_catalog.current_setting('cron.database_name')
USING DETAIL = 'Jobs must be scheduled from the database configured in 'OPERATOR(pg_catalog.||)
'cron.database_name, since the pg_cron background worker 'OPERATOR(pg_catalog.||)
'reads job descriptions from this database.',
HINT = pg_catalog.format('Add cron.database_name = ''%s'' in postgresql.conf 'OPERATOR(pg_catalog.||)
'to use the current database.', pg_catalog.current_database());
END IF;
END;
$$;
CREATE SCHEMA cron;
CREATE SEQUENCE cron.jobid_seq;
CREATE TABLE cron.job (
jobid bigint primary key default pg_catalog.nextval('cron.jobid_seq'),
schedule text not null,
command text not null,
nodename text not null default 'localhost',
nodeport int not null default pg_catalog.inet_server_port(),
database text not null default pg_catalog.current_database(),
username text not null default current_user
);
GRANT SELECT ON cron.job TO public;
ALTER TABLE cron.job ENABLE ROW LEVEL SECURITY;
CREATE POLICY cron_job_policy ON cron.job USING (username OPERATOR(pg_catalog.=) current_user);
CREATE FUNCTION cron.schedule(schedule text, command text)
RETURNS bigint
LANGUAGE C STRICT
AS 'MODULE_PATHNAME', $$cron_schedule$$;
COMMENT ON FUNCTION cron.schedule(text,text)
IS 'schedule a pg_cron job';
CREATE FUNCTION cron.unschedule(job_id bigint)
RETURNS bool
LANGUAGE C STRICT
AS 'MODULE_PATHNAME', $$cron_unschedule$$;
COMMENT ON FUNCTION cron.unschedule(bigint)
IS 'unschedule a pg_cron job';
CREATE FUNCTION cron.job_cache_invalidate()
RETURNS trigger
LANGUAGE C
AS 'MODULE_PATHNAME', $$cron_job_cache_invalidate$$;
COMMENT ON FUNCTION cron.job_cache_invalidate()
IS 'invalidate job cache';
CREATE TRIGGER cron_job_cache_invalidate
AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
ON cron.job
FOR STATEMENT EXECUTE PROCEDURE cron.job_cache_invalidate();
|
