The Debian package includes a regression test for pgagent which might
be useful for others as well. It installs a simple job, fires up
pgagent for 80 s (using `timeout` from coreutils), and checks if the
job was run at least once.
Christoph
--- /dev/null
+++ b/test/Makefile
@@ -0,0 +1,4 @@
+PG_CONFIG = pg_config
+REGRESS = init job
+PGXS = $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
--- /dev/null
+++ b/test/expected/init.out
@@ -0,0 +1 @@
+CREATE EXTENSION pgagent;
--- /dev/null
+++ b/test/sql/init.sql
@@ -0,0 +1 @@
+CREATE EXTENSION pgagent;
--- /dev/null
+++ b/test/expected/job.out
@@ -0,0 +1,23 @@
+CREATE TABLE t (
+ t timestamptz
+);
+WITH
+ id AS
+ (INSERT INTO pgagent.pga_job (jobjclid, jobname, jobdesc, jobenabled,
jobhostagent)
+ SELECT jcl.jclid, 'job1', '', true, ''
+ FROM pgagent.pga_jobclass jcl WHERE jclname='Routine Maintenance'
+ RETURNING jobid),
+ insertstep AS
+ (INSERT INTO pgagent.pga_jobstep (jstjobid, jstname, jstdesc, jstenabled,
jstkind, jstonerror, jstcode, jstdbname, jstconnstr)
+ SELECT id.jobid, 'step1', '', true, 's', 'f', 'INSERT INTO t VALUES
(now())', 'contrib_regression', ''
+ FROM id)
+INSERT INTO pgagent.pga_schedule (jscjobid, jscname, jscdesc, jscminutes,
jschours, jscweekdays, jscmonthdays, jscmonths, jscenabled, jscstart, jscend)
+ SELECT id.jobid, 'schedule1', '',
'{t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t}',
'{t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t}', '{t,t,t,t,t,t,t}',
'{t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t}',
'{t,t,t,t,t,t,t,t,t,t,t,t}', true, '2000-01-01 00:00:00', '2099-12-31 00:00:00'
+ FROM id;
+\! timeout 80 pgagent -f -l 2 -s pgagent.out "user=$PGUSER
dbname=contrib_regression" 2>/dev/null
+SELECT count(*) > 0 AS job_run FROM t;
+ job_run
+---------
+ t
+(1 row)
+
--- /dev/null
+++ b/test/sql/job.sql
@@ -0,0 +1,23 @@
+CREATE TABLE t (
+ t timestamptz
+);
+
+WITH
+ id AS
+ (INSERT INTO pgagent.pga_job (jobjclid, jobname, jobdesc, jobenabled,
jobhostagent)
+ SELECT jcl.jclid, 'job1', '', true, ''
+ FROM pgagent.pga_jobclass jcl WHERE jclname='Routine Maintenance'
+ RETURNING jobid),
+
+ insertstep AS
+ (INSERT INTO pgagent.pga_jobstep (jstjobid, jstname, jstdesc, jstenabled,
jstkind, jstonerror, jstcode, jstdbname, jstconnstr)
+ SELECT id.jobid, 'step1', '', true, 's', 'f', 'INSERT INTO t VALUES
(now())', 'contrib_regression', ''
+ FROM id)
+
+INSERT INTO pgagent.pga_schedule (jscjobid, jscname, jscdesc, jscminutes,
jschours, jscweekdays, jscmonthdays, jscmonths, jscenabled, jscstart, jscend)
+ SELECT id.jobid, 'schedule1', '',
'{t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t}',
'{t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t}', '{t,t,t,t,t,t,t}',
'{t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t,t}',
'{t,t,t,t,t,t,t,t,t,t,t,t}', true, '2000-01-01 00:00:00', '2099-12-31 00:00:00'
+ FROM id;
+
+\! timeout 80 pgagent -f -l 2 -s pgagent.out "user=$PGUSER
dbname=contrib_regression" 2>/dev/null
+
+SELECT count(*) > 0 AS job_run FROM t;
