CREATE EXTENSION pg_cron VERSION '1.0';
SELECT extversion FROM pg_extension WHERE extname='pg_cron';
 extversion 
------------
 1.0
(1 row)

-- Test binary compatibility with v1.4 function signature.
ALTER EXTENSION pg_cron UPDATE TO '1.4';
SELECT cron.unschedule(job_name := 'no_such_job');
ERROR:  could not find valid entry for job 'no_such_job'
SELECT cron.schedule('testjob', '* * * * *', 'SELECT 1');
 schedule 
----------
        1
(1 row)

SELECT cron.unschedule('testjob');
 unschedule 
------------
 t
(1 row)

-- Test cache invalidation
DROP EXTENSION pg_cron;
CREATE EXTENSION pg_cron VERSION '1.4';
ALTER EXTENSION pg_cron UPDATE;
-- Vacuum every day at 10:00am (GMT)
SELECT cron.schedule('0 10 * * *', 'VACUUM');
 schedule 
----------
        1
(1 row)

-- Stop scheduling a job
SELECT cron.unschedule(1);
 unschedule 
------------
 t
(1 row)

-- Invalid input: input too long
SELECT cron.schedule(repeat('a', 1000), '');
ERROR:  invalid schedule: aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
HINT:  Use cron format (e.g. 5 4 * * *), or interval format '[1-59] seconds'
-- Invalid input: missing parts
SELECT cron.schedule('* * * *', 'SELECT 1'); 
ERROR:  invalid schedule: * * * *
HINT:  Use cron format (e.g. 5 4 * * *), or interval format '[1-59] seconds'
-- Invalid input: trailing characters
SELECT cron.schedule('5 secondc', 'SELECT 1'); 
ERROR:  invalid schedule: 5 secondc
HINT:  Use cron format (e.g. 5 4 * * *), or interval format '[1-59] seconds'
SELECT cron.schedule('50 seconds c', 'SELECT 1'); 
ERROR:  invalid schedule: 50 seconds c
HINT:  Use cron format (e.g. 5 4 * * *), or interval format '[1-59] seconds'
-- Invalid input: step out of range
SELECT cron.schedule('*/0 10 * * *', 'SELECT 1');
ERROR:  invalid schedule: */0 10 * * *
HINT:  Use cron format (e.g. 5 4 * * *), or interval format '[1-59] seconds'
SELECT cron.schedule('-1 * * * *', 'SELECT 1');
ERROR:  invalid schedule: -1 * * * *
HINT:  Use cron format (e.g. 5 4 * * *), or interval format '[1-59] seconds'
SELECT cron.schedule('*/-1 10 * * *', 'SELECT 1');
ERROR:  invalid schedule: */-1 10 * * *
HINT:  Use cron format (e.g. 5 4 * * *), or interval format '[1-59] seconds'
SELECT cron.schedule('*/60 10 * * *', 'SELECT 1');
ERROR:  invalid schedule: */60 10 * * *
HINT:  Use cron format (e.g. 5 4 * * *), or interval format '[1-59] seconds'
SELECT cron.schedule('* * * 13 *', 'SELECT 1');
ERROR:  invalid schedule: * * * 13 *
HINT:  Use cron format (e.g. 5 4 * * *), or interval format '[1-59] seconds'
SELECT cron.schedule('* * * 0 *', 'SELECT 1');
ERROR:  invalid schedule: * * * 0 *
HINT:  Use cron format (e.g. 5 4 * * *), or interval format '[1-59] seconds'
SELECT cron.schedule('*/5000000000 10 * * *', 'SELECT 1');
ERROR:  invalid schedule: */5000000000 10 * * *
HINT:  Use cron format (e.g. 5 4 * * *), or interval format '[1-59] seconds'
-- Invalid input: seconds out of range
SELECT cron.schedule('-1 seconds', 'SELECT 1'); 
ERROR:  invalid schedule: -1 seconds
HINT:  Use cron format (e.g. 5 4 * * *), or interval format '[1-59] seconds'
SELECT cron.schedule('0 seconds', 'SELECT 1'); 
ERROR:  invalid schedule: 0 seconds
HINT:  Use cron format (e.g. 5 4 * * *), or interval format '[1-59] seconds'
SELECT cron.schedule('60 seconds', 'SELECT 1'); 
ERROR:  invalid schedule: 60 seconds
HINT:  Use cron format (e.g. 5 4 * * *), or interval format '[1-59] seconds'
SELECT cron.schedule('10000000000 seconds', 'SELECT 1'); 
ERROR:  invalid schedule: 10000000000 seconds
HINT:  Use cron format (e.g. 5 4 * * *), or interval format '[1-59] seconds'
-- Try to update pg_cron on restart
SELECT cron.schedule('@restar', 'ALTER EXTENSION pg_cron UPDATE');
ERROR:  invalid schedule: @restar
HINT:  Use cron format (e.g. 5 4 * * *), or interval format '[1-59] seconds'
SELECT cron.schedule('@restart', 'ALTER EXTENSION pg_cron UPDATE');
 schedule 
----------
        2
(1 row)

-- Vacuum every day at 10:00am (GMT)
SELECT cron.schedule('myvacuum', '0 10 * * *', 'VACUUM');
 schedule 
----------
        3
(1 row)

SELECT jobid, jobname, schedule, command FROM cron.job ORDER BY jobid;
 jobid | jobname  |  schedule  |            command             
-------+----------+------------+--------------------------------
     2 |          | @restart   | ALTER EXTENSION pg_cron UPDATE
     3 | myvacuum | 0 10 * * * | VACUUM
(2 rows)

-- Make that 11:00am (GMT)
SELECT cron.schedule('myvacuum', '0 11 * * *', 'VACUUM');
 schedule 
----------
        3
(1 row)

SELECT jobid, jobname, schedule, command FROM cron.job ORDER BY jobid;
 jobid | jobname  |  schedule  |            command             
-------+----------+------------+--------------------------------
     2 |          | @restart   | ALTER EXTENSION pg_cron UPDATE
     3 | myvacuum | 0 11 * * * | VACUUM
(2 rows)

-- Make that VACUUM FULL
SELECT cron.schedule('myvacuum', '0 11 * * *', 'VACUUM FULL');
 schedule 
----------
        3
(1 row)

SELECT jobid, jobname, schedule, command FROM cron.job ORDER BY jobid;
 jobid | jobname  |  schedule  |            command             
-------+----------+------------+--------------------------------
     2 |          | @restart   | ALTER EXTENSION pg_cron UPDATE
     3 | myvacuum | 0 11 * * * | VACUUM FULL
(2 rows)

-- Stop scheduling a job
SELECT cron.unschedule('myvacuum');
 unschedule 
------------
 t
(1 row)

SELECT jobid, jobname, schedule, command FROM cron.job ORDER BY jobid;
 jobid | jobname | schedule |            command             
-------+---------+----------+--------------------------------
     2 |         | @restart | ALTER EXTENSION pg_cron UPDATE
(1 row)

-- Testing version >= 1.4 new APIs
-- First as superuser
-- Update a job without one job attribute to change
SELECT cron.alter_job(2);
ERROR:  no updates specified
HINT:  You must specify at least one job attribute to change when calling alter_job
-- Update to a non existing database
select cron.alter_job(job_id:=2,database:='hopedoesnotexist');
ERROR:  database "hopedoesnotexist" does not exist
-- Create a database that does not allow connection
create database pgcron_dbno;
revoke connect on database pgcron_dbno from public;
-- create a test user
create user pgcron_cront with password 'pwd';
GRANT USAGE ON SCHEMA cron TO pgcron_cront;
-- Schedule a job for this user on the database that does not accept connections
SELECT cron.schedule_in_database(job_name:='can not connect', schedule:='0 11 * * *', command:='VACUUM',database:='pgcron_dbno',username:='pgcron_cront');
ERROR:  User pgcron_cront does not have CONNECT privilege on pgcron_dbno
-- Create a database that does allow connections
create database pgcron_dbyes;
-- Schedule a job on the database that does accept connections for a non existing user
SELECT cron.schedule_in_database(job_name:='user does not exist', schedule:='0 11 * * *', command:='VACUUM',database:='pgcron_dbyes',username:='pgcron_useraqwxszedc');
ERROR:  role "pgcron_useraqwxszedc" does not exist
-- Alter an existing job on a database that does not accept connections
SELECT cron.alter_job(job_id:=2,database:='pgcron_dbno',username:='pgcron_cront');
ERROR:  User pgcron_cront does not have CONNECT privilege on pgcron_dbno
-- Make sure pgcron_cront can execute alter_job
GRANT EXECUTE ON FUNCTION cron.alter_job(bigint,text,text,text,text,boolean) TO public;
-- Second as non superuser
SET SESSION AUTHORIZATION pgcron_cront;
-- Create a job
SELECT cron.schedule('My vacuum', '0 11 * * *', 'VACUUM');
 schedule 
----------
        6
(1 row)

-- Create a job for another user
SELECT cron.schedule_in_database(job_name:='his vacuum', schedule:='0 11 * * *', command:='VACUUM',database:=current_database(),username:='anotheruser');
ERROR:  permission denied for function schedule_in_database
-- Change the username of an existing job that the user own
select cron.alter_job(job_id:=6,username:='anotheruser');
ERROR:  must be superuser to alter username
-- Update a job that the user does not own
select cron.alter_job(job_id:=2,database:='pgcron_dbyes');
ERROR:  Job 2 does not exist or you don't own it
-- change the database for a job that the user own and can connect to
select cron.alter_job(job_id:=6,database:='pgcron_dbyes');
 alter_job 
-----------
 
(1 row)

SELECT database FROM cron.job;
   database   
--------------
 pgcron_dbyes
(1 row)

-- change the database for a job that the user own but can not connect to
select cron.alter_job(job_id:=6,database:='pgcron_dbno');
ERROR:  User pgcron_cront does not have CONNECT privilege on pgcron_dbno
SELECT database FROM cron.job;
   database   
--------------
 pgcron_dbyes
(1 row)

-- back to superuser
RESET SESSION AUTHORIZATION;
-- Change the username of an existing job
select cron.alter_job(job_id:=2,username:='pgcron_cront');
 alter_job 
-----------
 
(1 row)

SELECT username FROM cron.job where jobid=2;
   username   
--------------
 pgcron_cront
(1 row)

-- Create a job for another user
SELECT cron.schedule_in_database(job_name:='his vacuum', schedule:='0 11 * * *', command:='VACUUM',database:=current_database(), username:='pgcron_cront');
 schedule_in_database 
----------------------
                    7
(1 row)

SELECT username FROM cron.job where jobid=7;
   username   
--------------
 pgcron_cront
(1 row)

-- Override function
DROP EXTENSION IF EXISTS pg_cron cascade;
CREATE TABLE test (data text);
DROP TYPE IF EXISTS current_setting cascade;
NOTICE:  type "current_setting" does not exist, skipping
CREATE TYPE current_setting AS ENUM ('cron.database_name');
CREATE OR REPLACE FUNCTION public.func1(text, current_setting) RETURNS text
    LANGUAGE sql volatile AS 'INSERT INTO test(data) VALUES (current_user); SELECT current_database()::text;';
CREATE OR REPLACE FUNCTION public.func1(current_setting) RETURNS text
    LANGUAGE sql volatile AS 'INSERT INTO test(data) VALUES (current_user); SELECT current_database()::text;';
CREATE CAST (current_setting AS text) WITH FUNCTION public.func1(current_setting) AS IMPLICIT;
CREATE EXTENSION pg_cron;
select * from public.test;
 data 
------
(0 rows)

-- valid interval jobs
SELECT cron.schedule('1 second', 'SELECT 1'); 
 schedule 
----------
        1
(1 row)

SELECT cron.schedule(' 30 sEcOnDs ', 'SELECT 1'); 
 schedule 
----------
        2
(1 row)

SELECT cron.schedule('59 seconds', 'SELECT 1'); 
 schedule 
----------
        3
(1 row)

SELECT cron.schedule('17  seconds ', 'SELECT 1'); 
 schedule 
----------
        4
(1 row)

SELECT jobid, jobname, schedule, command FROM cron.job ORDER BY jobid;
 jobid | jobname |   schedule   | command  
-------+---------+--------------+----------
     1 |         | 1 second     | SELECT 1
     2 |         |  30 sEcOnDs  | SELECT 1
     3 |         | 59 seconds   | SELECT 1
     4 |         | 17  seconds  | SELECT 1
(4 rows)

-- valid last of day job
SELECT cron.schedule('last-day-of-month-job1', '0 11 $ * *', 'SELECT 1');
 schedule 
----------
        5
(1 row)

SELECT jobid, jobname, schedule, command FROM cron.job ORDER BY jobid;
 jobid |        jobname         |   schedule   | command  
-------+------------------------+--------------+----------
     1 |                        | 1 second     | SELECT 1
     2 |                        |  30 sEcOnDs  | SELECT 1
     3 |                        | 59 seconds   | SELECT 1
     4 |                        | 17  seconds  | SELECT 1
     5 | last-day-of-month-job1 | 0 11 $ * *   | SELECT 1
(5 rows)

-- invalid last of day job
SELECT cron.schedule('bad-last-dom-job1', '0 11 $foo * *', 'VACUUM FULL');
ERROR:  invalid schedule: 0 11 $foo * *
HINT:  Use cron format (e.g. 5 4 * * *), or interval format '[1-59] seconds'
-- cleaning
DROP EXTENSION pg_cron;
drop user pgcron_cront;
drop database pgcron_dbno;
drop database pgcron_dbyes;