PgQue/tests/test_pgcron_lifecycle.sql at main · NikolayS/PgQue · GitHub

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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
-- test_pgcron_lifecycle.sql -- Verify pgque.start() and pgque.stop() with pg_cron
-- Copyright 2026 Nikolay Samokhvalov. Apache-2.0 license.
--
-- These tests exercise the pg_cron lifecycle integration.
-- Tests auto-skip when pg_cron is not available.

-- Test 1: start() sets job IDs in config and schedules all four jobs
do $$
declare
  v_ticker_id bigint;
  v_maint_id bigint;
  v_job_count int;
begin
  if not exists (select 1 from pg_extension where extname = 'pg_cron') then
    raise notice 'SKIP: pg_cron not installed';
    return;
  end if;

  perform pgque.start();

  select ticker_job_id, maint_job_id into v_ticker_id, v_maint_id
  from pgque.config;

  assert v_ticker_id is not null, 'ticker_job_id should be set after start()';
  assert v_maint_id is not null, 'maint_job_id should be set after start()';

  -- All four named jobs should exist in cron.job after start().
  select count(*) into v_job_count
  from cron.job
  where jobname in ('pgque_ticker', 'pgque_retry_events', 'pgque_maint', 'pgque_rotate_step2');
  assert v_job_count = 4,
    'expected 4 pgque_* jobs in cron.job, found ' || v_job_count;

  -- Clean up
  perform pgque.stop();

  raise notice 'PASS: start() schedules four jobs (ticker, retry_events, maint, rotate_step2)';
end $$;

-- Test 1b: stop() unschedules every pgque_* job (ticker, retry_events, maint, rotate_step2)
do $$
declare
  v_job_count int;
begin
  if not exists (select from pg_extension where extname = 'pg_cron') then
    raise notice 'SKIP: pg_cron not installed';
    return;
  end if;

  perform pgque.start();
  perform pgque.stop();

  select count(*) into v_job_count
  from cron.job
  where jobname in ('pgque_ticker', 'pgque_retry_events', 'pgque_maint', 'pgque_rotate_step2');
  assert v_job_count = 0,
    'expected 0 pgque_* jobs in cron.job after stop(), found ' || v_job_count;

  raise notice 'PASS: stop() unschedules all four pgque_* jobs';
end $$;

-- Test 2: start() is idempotent
do $$
declare
  v_ticker_id1 bigint;
  v_ticker_id2 bigint;
begin
  if not exists (select 1 from pg_extension where extname = 'pg_cron') then
    raise notice 'SKIP: pg_cron not installed';
    return;
  end if;

  perform pgque.start();
  select ticker_job_id into v_ticker_id1 from pgque.config;

  perform pgque.start();
  select ticker_job_id into v_ticker_id2 from pgque.config;

  assert v_ticker_id2 is not null, 'should still have ticker job after second start()';

  -- Clean up
  perform pgque.stop();

  raise notice 'PASS: start() is idempotent';
end $$;

-- Test 3: stop() clears job IDs
do $$
begin
  if not exists (select 1 from pg_extension where extname = 'pg_cron') then
    raise notice 'SKIP: pg_cron not installed';
    return;
  end if;

  perform pgque.start();
  perform pgque.stop();

  assert (select ticker_job_id from pgque.config) is null,
    'ticker_job_id should be NULL after stop()';
  assert (select maint_job_id from pgque.config) is null,
    'maint_job_id should be NULL after stop()';

  raise notice 'PASS: stop() clears job IDs';
end $$;

-- Test 4: stop() is idempotent (calling twice does not error)
do $$
begin
  if not exists (select 1 from pg_extension where extname = 'pg_cron') then
    raise notice 'SKIP: pg_cron not installed';
    return;
  end if;

  perform pgque.start();
  perform pgque.stop();
  perform pgque.stop();

  raise notice 'PASS: stop() is idempotent';
end $$;

-- Test 5: Without pg_cron, start() raises informative error
do $$
begin
  if exists (select 1 from pg_extension where extname = 'pg_cron') then
    raise notice 'SKIP: pg_cron IS installed (cannot test without-pgcron path)';
    return;
  end if;

  begin
    perform pgque.start();
    assert false, 'start() should raise error without pg_cron';
  exception when raise_exception then
    assert sqlerrm like '%pg_cron%',
      'error should mention pg_cron, got: ' || sqlerrm;
    raise notice 'PASS: start() raises informative error without pg_cron';
  end;
end $$;

-- Test 6: Without pg_cron, stop() does not error (graceful no-op)
do $$
begin
  if exists (select 1 from pg_extension where extname = 'pg_cron') then
    raise notice 'SKIP: pg_cron IS installed (cannot test without-pgcron path)';
    return;
  end if;

  -- Should not raise: just clears any stale job IDs
  perform pgque.stop();

  assert (select ticker_job_id from pgque.config) is null,
    'ticker_job_id should be NULL after stop() without pg_cron';
  assert (select maint_job_id from pgque.config) is null,
    'maint_job_id should be NULL after stop() without pg_cron';

  raise notice 'PASS: stop() is graceful no-op without pg_cron';
end $$;