File: pg_cron.sql

package info (click to toggle)
pg-cron 1.3.0-2
  • links: PTS, VCS
  • area: main
  • in suites: bullseye
  • size: 296 kB
  • sloc: ansic: 3,145; sql: 104; sh: 27; makefile: 26
file content (55 lines) | stat: -rw-r--r-- 2,034 bytes parent folder | download | duplicates (4)
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 current_database() <> current_setting('cron.database_name') AND current_database() <> 'contrib_regression' THEN
      RAISE EXCEPTION 'can only create extension in database %',
                      current_setting('cron.database_name')
      USING DETAIL = 'Jobs must be scheduled from the database configured in '||
                     'cron.database_name, since the pg_cron background worker '||
                     'reads job descriptions from this database.',
            HINT = format('Add cron.database_name = ''%s'' in postgresql.conf '||
                          'to use the current database.', current_database());
   END IF;
END;
$$;

CREATE SCHEMA cron;
CREATE SEQUENCE cron.jobid_seq;

CREATE TABLE cron.job (
	jobid bigint primary key default nextval('cron.jobid_seq'),
	schedule text not null,
	command text not null,
	nodename text not null default 'localhost',
	nodeport int not null default inet_server_port(),
	database text not null default 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 = 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();