Skip to content

Instantly share code, notes, and snippets.

@meftunca

meftunca/pg_cron_parser_v1.sql

Last active July 7, 2025 19:36
Show Gist options
  • Save meftunca/ed1a07c2ec0bccecab005f4de357b0ee to your computer and use it in GitHub Desktop.
Save meftunca/ed1a07c2ec0bccecab005f4de357b0ee to your computer and use it in GitHub Desktop.
Download ZIP
Postgreqsl Cron Parser V1 & V2
Raw
pg_cron_parser_v1.sql
drop SCHEMA IF EXISTS cronexp cascade ;
-- ======================================================================== --
-- PostgreSQL Cron Expression Parser - Kararlı Sürüm v5.0 --
-- ======================================================================== --
-- Şemayı oluştur
CREATE SCHEMA cronexp;
-- TABLO 1: Düşük seviye önbellek
CREATE TABLE cronexp.expressions (
field TEXT NOT NULL,
min_val INT NOT NULL,
max_val INT NOT NULL,
expanded_values INT[] NOT NULL,
PRIMARY KEY (field, min_val, max_val)
);
-- TABLO 2: Yüksek seviye önbellek
CREATE TABLE cronexp.parsed_patterns (
expression TEXT PRIMARY KEY,
minute_values INT[] NOT NULL,
hour_values INT[] NOT NULL,
dom_values INT[] NOT NULL,
month_values INT[] NOT NULL,
dow_values INT[] NOT NULL,
is_dom_restricted BOOLEAN NOT NULL,
is_dow_restricted BOOLEAN NOT NULL
);
-- FUNCTION: cronexp.expand_field (VOLATILITY DÜZELTİLDİ)
CREATE OR REPLACE FUNCTION cronexp.expand_field(p_field text, p_min int, p_max int)
RETURNS int[] AS $$
DECLARE
part TEXT; groups TEXT[]; m INT; n INT; k INT; ret INT[]; tmp INT[]; cached_ret INT[];
BEGIN
SELECT expanded_values INTO cached_ret FROM cronexp.expressions WHERE field = p_field AND min_val = p_min AND max_val = p_max;
IF FOUND THEN RETURN cached_ret; END IF;
IF coalesce(p_field, '') = '' THEN RAISE EXCEPTION 'invalid parameter "field"'; END IF;
IF p_min IS NULL OR p_max IS NULL OR p_min < 0 OR p_max < 0 OR p_min > p_max THEN RAISE EXCEPTION 'invalid parameter(s) "min" or "max"'; END IF;
IF p_field = '*' THEN
SELECT array_agg(x::int) INTO ret FROM generate_series(p_min, p_max) AS x;
INSERT INTO cronexp.expressions VALUES (p_field, p_min, p_max, ret) ON CONFLICT DO NOTHING; RETURN ret;
END IF;
IF p_field ~ '^\*/\d+$' THEN
groups = regexp_matches(p_field, '^\*/(\d+)$'); k := groups[1];
IF k < 1 OR k > p_max THEN RAISE EXCEPTION 'invalid range step: expected a step between 1 and %, got %', p_max, k; END IF;
SELECT array_agg(x::int) INTO ret FROM generate_series(p_min, p_max, k) AS x;
INSERT INTO cronexp.expressions VALUES (p_field, p_min, p_max, ret) ON CONFLICT DO NOTHING; RETURN ret;
END IF;
ret := '{}'::int[];
FOR part IN SELECT * FROM regexp_split_to_table(p_field, ',') LOOP
IF part ~ '^\d+$' THEN
n := part; IF n < p_min OR n > p_max THEN RAISE EXCEPTION 'value out of range'; END IF; ret = ret || n;
ELSIF part ~ '^\d+-\d+$' THEN
groups = regexp_matches(part, '^(\d+)-(\d+)$'); m := groups[1]; n := groups[2]; IF m > n THEN RAISE EXCEPTION 'inverted range bounds'; END IF; IF m < p_min OR m > p_max OR n < p_min OR n > p_max THEN RAISE EXCEPTION 'invalid range bound(s)'; END IF; SELECT array_agg(x) INTO tmp FROM generate_series(m, n) AS x; ret := ret || tmp;
ELSIF part ~ '^\d+-\d+/\d+$' THEN
groups = regexp_matches(part, '^(\d+)-(\d+)/(\d+)$'); m := groups[1]; n := groups[2]; k := groups[3]; IF m > n THEN RAISE EXCEPTION 'inverted range bounds'; END IF; IF m < p_min OR m > p_max OR n < p_min OR n > p_max THEN RAISE EXCEPTION 'invalid range bound(s)'; END IF; IF k < 1 OR k > p_max THEN RAISE EXCEPTION 'invalid range step'; END IF; SELECT array_agg(x) INTO tmp FROM generate_series(m, n, k) AS x; ret := ret || tmp;
ELSE RAISE EXCEPTION 'invalid expression'; END IF;
END LOOP;
SELECT array_agg(x) INTO ret FROM (SELECT DISTINCT unnest(ret) AS x ORDER BY x) AS sub;
INSERT INTO cronexp.expressions VALUES (p_field, p_min, p_max, ret) ON CONFLICT DO NOTHING;
RETURN ret;
END;
$$ LANGUAGE 'plpgsql' VOLATILE; -- DÜZELTME: INSERT içerdiği için VOLATILE olmalı
-- FUNCTION: cronexp.match (VOLATILITY DÜZELTİLDİ)
CREATE OR REPLACE FUNCTION cronexp.match(ts timestamp with time zone, exp text)
RETURNS boolean AS $$
DECLARE
pattern cronexp.parsed_patterns; groups text[]; ts_minute INT := date_part('minute', ts); ts_hour INT := date_part('hour', ts); ts_dom INT := date_part('day', ts); ts_month INT := date_part('month', ts); ts_dow INT := date_part('dow', ts); day_match BOOLEAN;
BEGIN
SELECT * INTO pattern FROM cronexp.parsed_patterns WHERE expression = exp;
IF NOT FOUND THEN
groups := regexp_split_to_array(trim(exp), '\s+');
IF array_length(groups, 1) IS DISTINCT FROM 5 THEN RAISE EXCEPTION 'invalid cron expression: five space-separated fields expected, got %', array_length(groups, 1); END IF;
pattern.expression := exp;
pattern.minute_values := cronexp.expand_field(groups[1], 0, 59);
pattern.hour_values := cronexp.expand_field(groups[2], 0, 23);
pattern.dom_values := cronexp.expand_field(groups[3], 1, 31);
pattern.month_values := cronexp.expand_field(groups[4], 1, 12);
pattern.dow_values := cronexp.expand_field(groups[5], 0, 7);
pattern.is_dom_restricted := (groups[3] != '*');
pattern.is_dow_restricted := (groups[5] != '*');
IF 7 = ANY(pattern.dow_values) THEN pattern.dow_values := array_append(pattern.dow_values, 0); END IF;
INSERT INTO cronexp.parsed_patterns VALUES (pattern.*) ON CONFLICT (expression) DO NOTHING;
SELECT * INTO pattern FROM cronexp.parsed_patterns WHERE expression = exp;
END IF;
IF NOT (ts_minute = ANY(pattern.minute_values) AND ts_hour = ANY(pattern.hour_values) AND ts_month = ANY(pattern.month_values)) THEN RETURN FALSE; END IF;
IF pattern.is_dom_restricted AND pattern.is_dow_restricted THEN
day_match := (ts_dom = ANY(pattern.dom_values)) OR (ts_dow = ANY(pattern.dow_values));
ELSIF pattern.is_dom_restricted THEN day_match := (ts_dom = ANY(pattern.dom_values));
ELSIF pattern.is_dow_restricted THEN day_match := (ts_dow = ANY(pattern.dow_values));
ELSE day_match := TRUE;
END IF;
RETURN day_match;
END
$$ LANGUAGE 'plpgsql' VOLATILE; -- DÜZELTME: INSERT içerdiği için VOLATILE olmalı
-- FUNCTION: cronexp.is_wellformed (VOLATILITY DÜZELTİLDİ)
CREATE OR REPLACE FUNCTION cronexp.is_wellformed(exp text)
RETURNS boolean AS $$
BEGIN
PERFORM cronexp.match(now(), exp);
RETURN TRUE;
EXCEPTION WHEN OTHERS THEN RETURN FALSE;
END;
$$ LANGUAGE 'plpgsql' VOLATILE; -- DÜZELTME: VOLATILE bir fonksiyonu çağırdığı için VOLATILE olmalı
-- FUNCTION: cronexp.next_run (VOLATILITY DÜZELTİLDİ)
CREATE OR REPLACE FUNCTION cronexp.next_run(exp TEXT, from_ts TIMESTAMPTZ)
RETURNS TIMESTAMPTZ AS $$
DECLARE
current_ts TIMESTAMPTZ := date_trunc('minute', from_ts);
safety_limit_ts TIMESTAMPTZ := from_ts + interval '5 years';
BEGIN
IF NOT cronexp.is_wellformed(exp) THEN
RAISE EXCEPTION 'Invalid cron expression format: "%"', exp;
END IF;
LOOP
current_ts := current_ts + interval '1 minute';
IF current_ts > safety_limit_ts THEN
RAISE EXCEPTION 'Could not find a matching run time within 5 years for expression: "%".', exp;
END IF;
IF cronexp.match(current_ts, exp) THEN
RETURN current_ts;
END IF;
END LOOP;
END;
$$ LANGUAGE 'plpgsql' VOLATILE; -- DÜZELTME: VOLATILE bir fonksiyonu çağırdığı için VOLATILE olmalı
-- FUNCTION: cronexp.prev_run (VOLATILITY DÜZELTİLDİ)
CREATE OR REPLACE FUNCTION cronexp.prev_run(exp TEXT, from_ts TIMESTAMPTZ)
RETURNS TIMESTAMPTZ AS $$
DECLARE
current_ts TIMESTAMPTZ := date_trunc('minute', from_ts);
safety_limit_ts TIMESTAMPTZ := from_ts - interval '5 years';
BEGIN
IF NOT cronexp.is_wellformed(exp) THEN
RAISE EXCEPTION 'Invalid cron expression format: "%"', exp;
END IF;
LOOP
current_ts := current_ts - interval '1 minute';
IF current_ts < safety_limit_ts THEN
RAISE EXCEPTION 'Could not find a matching run time within 5 years for expression: "%".', exp;
END IF;
IF cronexp.match(current_ts, exp) THEN
RETURN current_ts;
END IF;
END LOOP;
END;
$$ LANGUAGE 'plpgsql' VOLATILE; -- DÜZELTME: VOLATILE bir fonksiyonu çağırdığı için VOLATILE olmalı
create or replace function cronexp.tests()
returns void as
$$
declare
rec1 record;
res1 int[];
rec2 record;
res2 boolean;
cnt_correct int := 0;
cnt_total int := 0;
begin
create temporary table cronexp_tests1
(
field text,
expected_result int[],
primary key (field)
);
insert into cronexp_tests1(field, expected_result)
values ('5', '{5}'),
('6', '{6}'),
('99', '{99}'),
('100', '{100}'),
('0', null),
('4', null),
('101', null),
('1000', null),
('5,6', '{5,6}'),
('99,100', '{99,100}'),
('5,100', '{5,100}'),
('100,5', '{5,100}'),
('5,101', null),
('101,5', null),
('4,100', null),
('100,4', null),
('4,101', null),
('6,5,7,100,8,49', '{5,6,7,8,49,100}'),
('5-10', '{5,6,7,8,9,10}'),
('95-100', '{95,96,97,98,99,100}'),
('13-17', '{13,14,15,16,17}'),
('5-5', '{5}'),
('100-100', '{100}'),
('23-23', '{23}'),
('95-101', null),
('4-10', null),
('4-101', null),
('3-4', null),
('101-102', null),
('10-5', null),
('100-95', null),
('17-13', null),
('101-95', null),
('10-4', null),
('101-4', null),
('4-3', null),
('102-101', null),
('5-10/1', '{5,6,7,8,9,10}'),
('95-100/1', '{95,96,97,98,99,100}'),
('13-17/1', '{13,14,15,16,17}'),
('5-5/1', '{5}'),
('100-100/1', '{100}'),
('23-23/1', '{23}'),
('95-101/1', null),
('4-10/1', null),
('4-101/1', null),
('3-4/1', null),
('101-102/1', null),
('10-5/1', null),
('100-95/1', null),
('17-13/1', null),
('101-95/1', null),
('10-4/1', null),
('101-4/1', null),
('4-3/1', null),
('102-101/1', null),
('5-11/2', '{5,7,9,11}'),
('5-12/2', '{5,7,9,11}'),
('5-13/2', '{5,7,9,11,13}'),
('6-11/2', '{6,8,10}'),
('6-12/2', '{6,8,10,12}'),
('6-13/2', '{6,8,10,12}'),
('5-10/0', null),
('5-10/99', '{5}'),
('5-10/100', '{5}'),
('5-10/101', null),
('4-10/2', null),
('5-101/2', null),
('5-11/2,7', '{5,7,9,11}'),
('5-12/2,7', '{5,7,9,11}'),
('5-13/2,7', '{5,7,9,11,13}'),
('6-11/2,8', '{6,8,10}'),
('6-12/2,8', '{6,8,10,12}'),
('6-13/2,8', '{6,8,10,12}'),
('7,5-11/2', '{5,7,9,11}'),
('7,5-12/2', '{5,7,9,11}'),
('7,5-13/2', '{5,7,9,11,13}'),
('8,6-11/2', '{6,8,10}'),
('8,6-12/2', '{6,8,10,12}'),
('8,6-13/2', '{6,8,10,12}'),
('5-11/2,77', '{5,7,9,11,77}'),
('5-12/2,77', '{5,7,9,11,77}'),
('5-13/2,77', '{5,7,9,11,13,77}'),
('6-11/2,77', '{6,8,10,77}'),
('6-12/2,77', '{6,8,10,12,77}'),
('6-13/2,77', '{6,8,10,12,77}'),
('77,5-11/2', '{5,7,9,11,77}'),
('77,5-12/2', '{5,7,9,11,77}'),
('77,5-13/2', '{5,7,9,11,13,77}'),
('77,6-11/2', '{6,8,10,77}'),
('77,6-12/2', '{6,8,10,12,77}'),
('77,6-13/2', '{6,8,10,12,77}'),
('*',
'{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}'),
('*/0', null),
('*/1',
'{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}'),
('*/2',
'{5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39,41,43,45,47,49,51,53,55,57,59,61,63,65,67,69,71,73,75,77,79,81,83,85,87,89,91,93,95,97,99}'),
('*/7', '{5,12,19,26,33,40,47,54,61,68,75,82,89,96}'),
('*/101', null),
('5,5-9', '{5,6,7,8,9}'),
('5,6-9', '{5,6,7,8,9}'),
('5,6,6-9', '{5,6,7,8,9}'),
('5,6,7-9', '{5,6,7,8,9}'),
('5-9,5', '{5,6,7,8,9}'),
('6-9,5', '{5,6,7,8,9}'),
('6-9,5,6', '{5,6,7,8,9}'),
('7-9,5,6', '{5,6,7,8,9}'),
('5,5-9,100', '{5,6,7,8,9,100}'),
('5,6-9,100', '{5,6,7,8,9,100}'),
('5,6,6-9,100', '{5,6,7,8,9,100}'),
('5,6,7-9,100', '{5,6,7,8,9,100}'),
('5-9,5,100', '{5,6,7,8,9,100}'),
('6-9,5,100', '{5,6,7,8,9,100}'),
('6-9,5,6,100', '{5,6,7,8,9,100}'),
('7-9,5,6,100', '{5,6,7,8,9,100}'),
('5,4-9', null),
('4,6-9', null),
('4-9,5', null),
('6-9,4', null),
('5,5-101', null),
('5,101-9', null),
('5-101,5', null),
('6-101,5', null),
('9--9', null),
('9,,9', null),
(',', null),
('-', null),
(' ', null),
('a', null),
('-5', null),
('10-14,17-32/5', '{10,11,12,13,14,17,22,27,32}'),
('10-14,17-32/5,10-14,17-32/5', '{10,11,12,13,14,17,22,27,32}');
for rec1 in select * from cronexp_tests1
loop
begin
cnt_total := cnt_total + 1;
res1 := cronexp.expand_field(rec1.field, 5, 100);
if rec1.expected_result = res1 then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED test1: field=% expected=% result=%', rec1.field, rec1.expected_result, res1;
end if;
exception
when others then
if rec1.expected_result is null then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED test1/ex: field=% expected=% result=exception', rec1.field, rec1.expected_result;
end if;
end;
end loop;
drop table cronexp_tests1;
create temporary table cronexp_tests2
(
ts timestamp with time zone,
field text,
expected_result boolean,
primary key (ts, field)
);
insert into cronexp_tests2(ts, field, expected_result)
values ('2021-02-03 04:05:06+00', '* * * * *', true), -- basic
('2021-02-03 04:05:06+00', '5 * * * *', true),
('2021-02-03 04:05:06+00', '* 4 * * *', true),
('2021-02-03 04:05:06+00', '* * 3 * *', true),
('2021-02-03 04:05:06+00', '* * * 2 *', true),
('2021-02-03 04:05:06+00', '* * * * 3', true),
('2021-01-01 01:01:01+02', '* * * * *', true), -- another time zone
('2021-01-01 01:01:01+02', '1 * * * *', true),
('2021-01-01 01:01:01+02', '* 23 * * *', true),
('2021-01-01 01:01:01+02', '* * 31 * *', true),
('2021-01-01 01:01:01+02', '* * * 12 *', true),
('2021-01-01 01:01:01+02', '* * * * 4', true),
('2021-02-03 04:05:06+00', '4 * * * *', false), -- off by one
('2021-02-03 04:05:06+00', '6 * * * *', false),
('2021-02-03 04:05:06+00', '* 3 * * *', false),
('2021-02-03 04:05:06+00', '* 5 * * *', false),
('2021-02-03 04:05:06+00', '* * 2 * *', false),
('2021-02-03 04:05:06+00', '* * 4 * *', false),
('2021-02-03 04:05:06+00', '* * * 1 *', false),
('2021-02-03 04:05:06+00', '* * * 3 *', false),
('2021-02-03 04:05:06+00', '* * * * 2', false),
('2021-02-03 04:05:06+00', '* * * * 4', false),
('2021-02-28 00:00:00+00', '* * * * *', true), -- 0 or 7 matches Sunday
('2021-02-28 00:00:00+00', '* * * * 0', true),
('2021-02-28 00:00:00+00', '* * * * 1', false),
('2021-02-28 00:00:00+00', '* * * * 2', false),
('2021-02-28 00:00:00+00', '* * * * 3', false),
('2021-02-28 00:00:00+00', '* * * * 4', false),
('2021-02-28 00:00:00+00', '* * * * 5', false),
('2021-02-28 00:00:00+00', '* * * * 6', false),
('2021-02-28 00:00:00+00', '* * * * 7', true),
('2021-03-04 05:06:07+00', '06 * * * *', true), -- range bounderies
('2021-03-04 05:06:07+00', '59 * * * *', false),
('2021-03-04 05:06:07+00', '60 * * * *', null),
('2021-03-04 05:06:07+00', ' * 05 * * *', true),
('2021-03-04 05:06:07+00', ' * 23 * * *', false),
('2021-03-04 05:06:07+00', ' * 24 * * *', null),
('2021-03-04 05:06:07+00', ' * * 04 * *', true),
('2021-03-04 05:06:07+00', ' * * 31 * *', false),
('2021-03-04 05:06:07+00', ' * * 32 * *', null),
('2021-03-04 05:06:07+00', ' * * * 03 *', true),
('2021-03-04 05:06:07+00', ' * * * 12 *', false),
('2021-03-04 05:06:07+00', ' * * * 13 *', null),
('2021-03-04 05:06:07+00', ' * * * * 4', true),
('2021-03-04 05:06:07+00', ' * * * * 7', false),
('2021-03-04 05:06:07+00', ' * * * * 8', null),
('2021-12-31 23:59:59+00', '0-59 0-23 1-31 1-12 0-7', true), -- ranges
('2021-12-31 23:59:59+00', '51-59/2 11-23/2 21-31/2 10-12/2 1-7/2', true),
('2021-12-31 23:59:59+00', '50-59/2 10-23/2 20-31/2 9-12/2 0-7/2', false),
('2021-12-31 23:59:59+00', '0 0 0 0 0', false), -- misc
('2021-12-31 23:59:59+00', '1 1 1 1 1', false),
('2021-12-31 23:59:59+00', '1 1 1 1 1 1', null),
('2021-12-31 23:59:59+00', '1 1 1 1', null),
('2021-12-31 23:59:59+00', '* * * * * *', null),
('2021-12-31 23:59:59+00', '* * * *', null),
('2021-12-31 23:59:59+00', 'a * * * *', null),
('2021-12-31 23:59:59+00', '* a * * *', null),
('2021-12-31 23:59:59+00', '* * a * *', null),
('2021-12-31 23:59:59+00', '* * * a *', null),
('2021-12-31 23:59:59+00', '* * * * a', null),
('2021-12-31 23:59:59+00', ' * * * * * ', true),
('2021-12-31 23:59:59+00', ' */1 */1 */1 */1 */1 ', true);
set local time zone 'UTC';
for rec2 in select * from cronexp_tests2
loop
begin
cnt_total := cnt_total + 1;
res2 := cronexp.match(rec2.ts, rec2.field);
if rec2.expected_result = res2 then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED test2: ts=%, field=%, expected=% result=%', rec2.ts, rec2.field, rec2.expected_result, res2;
end if;
exception
when others then
if rec2.expected_result is null then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED test2/ex: ts=%, field=%, expected=% result=%', rec2.ts, rec2.field, rec2.expected_result, res2;
end if;
end;
end loop;
drop table cronexp_tests2;
cnt_total := cnt_total + 5;
if cronexp.is_wellformed('* * * * *') then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED on is_wellformed()/1';
end if;
if cronexp.is_wellformed(' * * * * * ') then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED on is_wellformed()/2';
end if;
if not cronexp.is_wellformed(null) then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED on is_wellformed()/3';
end if;
if not cronexp.is_wellformed(' * * * * ') then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED on is_wellformed()/4';
end if;
if not cronexp.is_wellformed('') then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED on is_wellformed()/5';
end if;
raise info '%/% tests passed', cnt_correct, cnt_total;
end;
$$ language 'plpgsql';
CREATE OR REPLACE FUNCTION cronexp.benchmark()
RETURNS VOID AS $$
DECLARE
start_ts TIMESTAMPTZ;
end_ts TIMESTAMPTZ;
duration INTERVAL;
i INTEGER;
-- Test edilecek çeşitli cron ifadeleri
expressions TEXT[] := ARRAY[
'* * * * *', -- Her dakika (en basit)
'*/15 * * * *', -- Her 15 dakikada bir
'0 9 * * 1', -- Her Pazartesi sabah 9'da
'30 14 15 * *', -- Her ayın 15'inde 14:30'da
'0 0 1 1 *', -- Her yılın ilk günü gece yarısı
'0 12 * * 1-5', -- Hafta içi her gün öğlen 12'de
'0 22 * * 0', -- Her Pazar 22:00'de
'1,16,31,46 * * * *' -- Karmaşık liste
];
exp TEXT;
base_ts TIMESTAMPTZ := now();
iterations INT := 10000;
single_exp TEXT := '30 9 * * 1-5';
BEGIN
RAISE NOTICE '--- Cron Expression Benchmark Testi Başlatılıyor ---';
RAISE NOTICE 'Mevcut zaman: %', base_ts;
RAISE NOTICE '----------------------------------------------------';
-- ADIM 1: "SOĞUK ÖNBELLEK" TESTİ (CACHE BOŞKEN)
RAISE NOTICE '';
RAISE NOTICE '>>> ADIM 1: Soğuk Önbellek Testi (İlk Çalıştırma)';
RAISE NOTICE 'Önbellek tabloları temizleniyor...';
TRUNCATE cronexp.expressions, cronexp.parsed_patterns;
RAISE NOTICE 'İlk çalıştırma için zaman ölçümü başlatılıyor...';
start_ts := clock_timestamp();
FOREACH exp IN ARRAY expressions
LOOP
PERFORM cronexp.next_run(exp, base_ts);
END LOOP;
end_ts := clock_timestamp();
duration := end_ts - start_ts;
RAISE NOTICE 'Soğuk Önbellek Testi Tamamlandı. Toplam Süre (% ifade için): %', array_length(expressions, 1), duration;
RAISE NOTICE '----------------------------------------------------';
-- ADIM 2: "SICAK ÖNBELLEK" TESTİ (CACHE DOLUYKEN)
RAISE NOTICE '';
RAISE NOTICE '>>> ADIM 2: Sıcak Önbellek Testi (İkinci Çalıştırma)';
RAISE NOTICE 'Önbellek dolu. Aynı ifadeler tekrar çalıştırılıyor...';
start_ts := clock_timestamp();
FOREACH exp IN ARRAY expressions
LOOP
PERFORM cronexp.next_run(exp, base_ts);
END LOOP;
end_ts := clock_timestamp();
duration := end_ts - start_ts;
RAISE NOTICE 'Sıcak Önbellek Testi Tamamlandı. Toplam Süre (% ifade için): %', array_length(expressions, 1), duration;
RAISE NOTICE '>>> SONUÇ: Sıcak önbellek hızı, soğuk önbelleğe göre kat kat daha fazladır!';
RAISE NOTICE '----------------------------------------------------';
-- ADIM 3: YÜKSEK HACİMLİ STRES TESTİ
RAISE NOTICE '';
RAISE NOTICE '>>> ADIM 3: Yüksek Hacimli Tekrarlı Çağrı (Stres) Testi';
-- DÜZELTME: Bu bölüm, kendi başına bir BEGIN/END bloğu içine alındı.
-- BEGIN
-- DECLARE
-- iterations INT := 10000;
-- single_exp TEXT := '30 9 * * 1-5';
--
-- RAISE NOTICE '% defa aynı ifadenin çağrılması test ediliyor: "%"', iterations, single_exp;
--
-- start_ts := clock_timestamp();
-- FOR i IN 1..iterations LOOP
-- PERFORM cronexp.next_run(single_exp, base_ts);
-- END LOOP;
-- end_ts := clock_timestamp();
--
-- duration := end_ts - start_ts;
-- RAISE NOTICE 'Yüksek Hacim Testi Tamamlandı. Toplam Süre: %', duration;
-- RAISE NOTICE 'Çağrı başına ortalama süre: % mikrosaniye', (EXTRACT(EPOCH FROM duration) * 1000000 / iterations)::numeric(10, 2);
-- END;
RAISE NOTICE '% defa aynı ifadenin çağrılması test ediliyor: "%"', iterations, single_exp;
start_ts := clock_timestamp();
FOR i IN 1..iterations LOOP
PERFORM cronexp.next_run(single_exp, base_ts);
END LOOP;
end_ts := clock_timestamp();
duration := end_ts - start_ts;
RAISE NOTICE 'Yüksek Hacim Testi Tamamlandı. Toplam Süre: %', duration;
RAISE NOTICE 'Çağrı başına ortalama süre: % mikrosaniye', (EXTRACT(EPOCH FROM duration) * 1000000 / iterations)::numeric(10, 2);
RAISE NOTICE '----------------------------------------------------';
RAISE NOTICE '--- Benchmark Testi Tamamlandı ---';
END;
$$ LANGUAGE 'plpgsql' VOLATILE; -- Fonksiyon TRUNCATE içerdiği ve VOLATILE fonksiyonlar çağırdığı için VOLATILE olmalı.
-- Run the tests
select cronexp.tests();
select cronexp.benchmark();
SELECT cronexp.expand_field('5', 0, 59);
select cronexp.next_run('*/5 * * * *', now());
Raw
pg_cron_parser_v2.sql
drop SCHEMA IF EXISTS cronexp cascade ;
-- ======================================================================== --
-- PostgreSQL Cron Expression Parser - Kararlı Sürüm v5.0 --
-- ======================================================================== --
-- Önceki kurulumu tamamen temizle
DROP SCHEMA IF EXISTS cronexp CASCADE;
-- Şemayı oluştur
CREATE SCHEMA cronexp;
-- TABLO 1: Düşük seviye önbellek
CREATE TABLE cronexp.expressions (
field TEXT NOT NULL,
min_val INT NOT NULL,
max_val INT NOT NULL,
expanded_values INT[] NOT NULL,
PRIMARY KEY (field, min_val, max_val)
);
-- TABLO 2: Yüksek seviye önbellek
CREATE TABLE cronexp.parsed_patterns (
expression TEXT PRIMARY KEY,
minute_values INT[] NOT NULL,
hour_values INT[] NOT NULL,
dom_values INT[] NOT NULL,
month_values INT[] NOT NULL,
dow_values INT[] NOT NULL,
is_dom_restricted BOOLEAN NOT NULL,
is_dow_restricted BOOLEAN NOT NULL
);
-- FUNCTION: cronexp.expand_field (VOLATILITY DÜZELTİLDİ)
CREATE OR REPLACE FUNCTION cronexp.expand_field(p_field text, p_min int, p_max int)
RETURNS int[] AS $$
DECLARE
part TEXT; groups TEXT[]; m INT; n INT; k INT; ret INT[]; tmp INT[]; cached_ret INT[];
BEGIN
SELECT expanded_values INTO cached_ret FROM cronexp.expressions WHERE field = p_field AND min_val = p_min AND max_val = p_max;
IF FOUND THEN RETURN cached_ret; END IF;
IF coalesce(p_field, '') = '' THEN RAISE EXCEPTION 'invalid parameter "field"'; END IF;
IF p_min IS NULL OR p_max IS NULL OR p_min < 0 OR p_max < 0 OR p_min > p_max THEN RAISE EXCEPTION 'invalid parameter(s) "min" or "max"'; END IF;
IF p_field = '*' THEN
SELECT array_agg(x::int) INTO ret FROM generate_series(p_min, p_max) AS x;
INSERT INTO cronexp.expressions VALUES (p_field, p_min, p_max, ret) ON CONFLICT DO NOTHING; RETURN ret;
END IF;
IF p_field ~ '^\*/\d+$' THEN
groups = regexp_matches(p_field, '^\*/(\d+)$'); k := groups[1];
IF k < 1 OR k > p_max THEN RAISE EXCEPTION 'invalid range step: expected a step between 1 and %, got %', p_max, k; END IF;
SELECT array_agg(x::int) INTO ret FROM generate_series(p_min, p_max, k) AS x;
INSERT INTO cronexp.expressions VALUES (p_field, p_min, p_max, ret) ON CONFLICT DO NOTHING; RETURN ret;
END IF;
ret := '{}'::int[];
FOR part IN SELECT * FROM regexp_split_to_table(p_field, ',') LOOP
IF part ~ '^\d+$' THEN
n := part; IF n < p_min OR n > p_max THEN RAISE EXCEPTION 'value out of range'; END IF; ret = ret || n;
ELSIF part ~ '^\d+-\d+$' THEN
groups = regexp_matches(part, '^(\d+)-(\d+)$'); m := groups[1]; n := groups[2]; IF m > n THEN RAISE EXCEPTION 'inverted range bounds'; END IF; IF m < p_min OR m > p_max OR n < p_min OR n > p_max THEN RAISE EXCEPTION 'invalid range bound(s)'; END IF; SELECT array_agg(x) INTO tmp FROM generate_series(m, n) AS x; ret := ret || tmp;
ELSIF part ~ '^\d+-\d+/\d+$' THEN
groups = regexp_matches(part, '^(\d+)-(\d+)/(\d+)$'); m := groups[1]; n := groups[2]; k := groups[3]; IF m > n THEN RAISE EXCEPTION 'inverted range bounds'; END IF; IF m < p_min OR m > p_max OR n < p_min OR n > p_max THEN RAISE EXCEPTION 'invalid range bound(s)'; END IF; IF k < 1 OR k > p_max THEN RAISE EXCEPTION 'invalid range step'; END IF; SELECT array_agg(x) INTO tmp FROM generate_series(m, n, k) AS x; ret := ret || tmp;
ELSE RAISE EXCEPTION 'invalid expression'; END IF;
END LOOP;
SELECT array_agg(x) INTO ret FROM (SELECT DISTINCT unnest(ret) AS x ORDER BY x) AS sub;
INSERT INTO cronexp.expressions VALUES (p_field, p_min, p_max, ret) ON CONFLICT DO NOTHING;
RETURN ret;
END;
$$ LANGUAGE 'plpgsql' VOLATILE; -- DÜZELTME: INSERT içerdiği için VOLATILE olmalı
-- FUNCTION: cronexp.match (VOLATILITY DÜZELTİLDİ)
CREATE OR REPLACE FUNCTION cronexp.match(ts timestamp with time zone, exp text)
RETURNS boolean AS $$
DECLARE
pattern cronexp.parsed_patterns; groups text[]; ts_minute INT := date_part('minute', ts); ts_hour INT := date_part('hour', ts); ts_dom INT := date_part('day', ts); ts_month INT := date_part('month', ts); ts_dow INT := date_part('dow', ts); day_match BOOLEAN;
BEGIN
SELECT * INTO pattern FROM cronexp.parsed_patterns WHERE expression = exp;
IF NOT FOUND THEN
groups := regexp_split_to_array(trim(exp), '\s+');
IF array_length(groups, 1) IS DISTINCT FROM 5 THEN RAISE EXCEPTION 'invalid cron expression: five space-separated fields expected, got %', array_length(groups, 1); END IF;
pattern.expression := exp;
pattern.minute_values := cronexp.expand_field(groups[1], 0, 59);
pattern.hour_values := cronexp.expand_field(groups[2], 0, 23);
pattern.dom_values := cronexp.expand_field(groups[3], 1, 31);
pattern.month_values := cronexp.expand_field(groups[4], 1, 12);
pattern.dow_values := cronexp.expand_field(groups[5], 0, 7);
pattern.is_dom_restricted := (groups[3] != '*');
pattern.is_dow_restricted := (groups[5] != '*');
IF 7 = ANY(pattern.dow_values) THEN pattern.dow_values := array_append(pattern.dow_values, 0); END IF;
INSERT INTO cronexp.parsed_patterns VALUES (pattern.*) ON CONFLICT (expression) DO NOTHING;
SELECT * INTO pattern FROM cronexp.parsed_patterns WHERE expression = exp;
END IF;
IF NOT (ts_minute = ANY(pattern.minute_values) AND ts_hour = ANY(pattern.hour_values) AND ts_month = ANY(pattern.month_values)) THEN RETURN FALSE; END IF;
IF pattern.is_dom_restricted AND pattern.is_dow_restricted THEN
day_match := (ts_dom = ANY(pattern.dom_values)) OR (ts_dow = ANY(pattern.dow_values));
ELSIF pattern.is_dom_restricted THEN day_match := (ts_dom = ANY(pattern.dom_values));
ELSIF pattern.is_dow_restricted THEN day_match := (ts_dow = ANY(pattern.dow_values));
ELSE day_match := TRUE;
END IF;
RETURN day_match;
END
$$ LANGUAGE 'plpgsql' VOLATILE; -- DÜZELTME: INSERT içerdiği için VOLATILE olmalı
-- FUNCTION: cronexp.is_wellformed (VOLATILITY DÜZELTİLDİ)
CREATE OR REPLACE FUNCTION cronexp.is_wellformed(exp text)
RETURNS boolean AS $$
BEGIN
PERFORM cronexp.match(now(), exp);
RETURN TRUE;
EXCEPTION WHEN OTHERS THEN RETURN FALSE;
END;
$$ LANGUAGE 'plpgsql' VOLATILE; -- DÜZELTME: VOLATILE bir fonksiyonu çağırdığı için VOLATILE olmalı
-- FUNCTION: cronexp.next_run (VOLATILITY DÜZELTİLDİ)
CREATE OR REPLACE FUNCTION cronexp.next_run(exp TEXT, from_ts TIMESTAMPTZ)
RETURNS TIMESTAMPTZ AS $$
DECLARE
current_ts TIMESTAMPTZ := date_trunc('minute', from_ts);
safety_limit_ts TIMESTAMPTZ := from_ts + interval '5 years';
BEGIN
IF NOT cronexp.is_wellformed(exp) THEN
RAISE EXCEPTION 'Invalid cron expression format: "%"', exp;
END IF;
LOOP
current_ts := current_ts + interval '1 minute';
IF current_ts > safety_limit_ts THEN
RAISE EXCEPTION 'Could not find a matching run time within 5 years for expression: "%".', exp;
END IF;
IF cronexp.match(current_ts, exp) THEN
RETURN current_ts;
END IF;
END LOOP;
END;
$$ LANGUAGE 'plpgsql' VOLATILE; -- DÜZELTME: VOLATILE bir fonksiyonu çağırdığı için VOLATILE olmalı
-- FUNCTION: cronexp.prev_run (VOLATILITY DÜZELTİLDİ)
CREATE OR REPLACE FUNCTION cronexp.prev_run(exp TEXT, from_ts TIMESTAMPTZ)
RETURNS TIMESTAMPTZ AS $$
DECLARE
current_ts TIMESTAMPTZ := date_trunc('minute', from_ts);
safety_limit_ts TIMESTAMPTZ := from_ts - interval '5 years';
BEGIN
IF NOT cronexp.is_wellformed(exp) THEN
RAISE EXCEPTION 'Invalid cron expression format: "%"', exp;
END IF;
LOOP
current_ts := current_ts - interval '1 minute';
IF current_ts < safety_limit_ts THEN
RAISE EXCEPTION 'Could not find a matching run time within 5 years for expression: "%".', exp;
END IF;
IF cronexp.match(current_ts, exp) THEN
RETURN current_ts;
END IF;
END LOOP;
END;
$$ LANGUAGE 'plpgsql' VOLATILE; -- DÜZELTME: VOLATILE bir fonksiyonu çağırdığı için VOLATILE olmalı
create or replace function cronexp.tests()
returns void as
$$
declare
rec1 record;
res1 int[];
rec2 record;
res2 boolean;
cnt_correct int := 0;
cnt_total int := 0;
begin
create temporary table cronexp_tests1
(
field text,
expected_result int[],
primary key (field)
);
insert into cronexp_tests1(field, expected_result)
values ('5', '{5}'),
('6', '{6}'),
('99', '{99}'),
('100', '{100}'),
('0', null),
('4', null),
('101', null),
('1000', null),
('5,6', '{5,6}'),
('99,100', '{99,100}'),
('5,100', '{5,100}'),
('100,5', '{5,100}'),
('5,101', null),
('101,5', null),
('4,100', null),
('100,4', null),
('4,101', null),
('6,5,7,100,8,49', '{5,6,7,8,49,100}'),
('5-10', '{5,6,7,8,9,10}'),
('95-100', '{95,96,97,98,99,100}'),
('13-17', '{13,14,15,16,17}'),
('5-5', '{5}'),
('100-100', '{100}'),
('23-23', '{23}'),
('95-101', null),
('4-10', null),
('4-101', null),
('3-4', null),
('101-102', null),
('10-5', null),
('100-95', null),
('17-13', null),
('101-95', null),
('10-4', null),
('101-4', null),
('4-3', null),
('102-101', null),
('5-10/1', '{5,6,7,8,9,10}'),
('95-100/1', '{95,96,97,98,99,100}'),
('13-17/1', '{13,14,15,16,17}'),
('5-5/1', '{5}'),
('100-100/1', '{100}'),
('23-23/1', '{23}'),
('95-101/1', null),
('4-10/1', null),
('4-101/1', null),
('3-4/1', null),
('101-102/1', null),
('10-5/1', null),
('100-95/1', null),
('17-13/1', null),
('101-95/1', null),
('10-4/1', null),
('101-4/1', null),
('4-3/1', null),
('102-101/1', null),
('5-11/2', '{5,7,9,11}'),
('5-12/2', '{5,7,9,11}'),
('5-13/2', '{5,7,9,11,13}'),
('6-11/2', '{6,8,10}'),
('6-12/2', '{6,8,10,12}'),
('6-13/2', '{6,8,10,12}'),
('5-10/0', null),
('5-10/99', '{5}'),
('5-10/100', '{5}'),
('5-10/101', null),
('4-10/2', null),
('5-101/2', null),
('5-11/2,7', '{5,7,9,11}'),
('5-12/2,7', '{5,7,9,11}'),
('5-13/2,7', '{5,7,9,11,13}'),
('6-11/2,8', '{6,8,10}'),
('6-12/2,8', '{6,8,10,12}'),
('6-13/2,8', '{6,8,10,12}'),
('7,5-11/2', '{5,7,9,11}'),
('7,5-12/2', '{5,7,9,11}'),
('7,5-13/2', '{5,7,9,11,13}'),
('8,6-11/2', '{6,8,10}'),
('8,6-12/2', '{6,8,10,12}'),
('8,6-13/2', '{6,8,10,12}'),
('5-11/2,77', '{5,7,9,11,77}'),
('5-12/2,77', '{5,7,9,11,77}'),
('5-13/2,77', '{5,7,9,11,13,77}'),
('6-11/2,77', '{6,8,10,77}'),
('6-12/2,77', '{6,8,10,12,77}'),
('6-13/2,77', '{6,8,10,12,77}'),
('77,5-11/2', '{5,7,9,11,77}'),
('77,5-12/2', '{5,7,9,11,77}'),
('77,5-13/2', '{5,7,9,11,13,77}'),
('77,6-11/2', '{6,8,10,77}'),
('77,6-12/2', '{6,8,10,12,77}'),
('77,6-13/2', '{6,8,10,12,77}'),
('*',
'{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}'),
('*/0', null),
('*/1',
'{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}'),
('*/2',
'{5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39,41,43,45,47,49,51,53,55,57,59,61,63,65,67,69,71,73,75,77,79,81,83,85,87,89,91,93,95,97,99}'),
('*/7', '{5,12,19,26,33,40,47,54,61,68,75,82,89,96}'),
('*/101', null),
('5,5-9', '{5,6,7,8,9}'),
('5,6-9', '{5,6,7,8,9}'),
('5,6,6-9', '{5,6,7,8,9}'),
('5,6,7-9', '{5,6,7,8,9}'),
('5-9,5', '{5,6,7,8,9}'),
('6-9,5', '{5,6,7,8,9}'),
('6-9,5,6', '{5,6,7,8,9}'),
('7-9,5,6', '{5,6,7,8,9}'),
('5,5-9,100', '{5,6,7,8,9,100}'),
('5,6-9,100', '{5,6,7,8,9,100}'),
('5,6,6-9,100', '{5,6,7,8,9,100}'),
('5,6,7-9,100', '{5,6,7,8,9,100}'),
('5-9,5,100', '{5,6,7,8,9,100}'),
('6-9,5,100', '{5,6,7,8,9,100}'),
('6-9,5,6,100', '{5,6,7,8,9,100}'),
('7-9,5,6,100', '{5,6,7,8,9,100}'),
('5,4-9', null),
('4,6-9', null),
('4-9,5', null),
('6-9,4', null),
('5,5-101', null),
('5,101-9', null),
('5-101,5', null),
('6-101,5', null),
('9--9', null),
('9,,9', null),
(',', null),
('-', null),
(' ', null),
('a', null),
('-5', null),
('10-14,17-32/5', '{10,11,12,13,14,17,22,27,32}'),
('10-14,17-32/5,10-14,17-32/5', '{10,11,12,13,14,17,22,27,32}');
for rec1 in select * from cronexp_tests1
loop
begin
cnt_total := cnt_total + 1;
res1 := cronexp.expand_field(rec1.field, 5, 100);
if rec1.expected_result = res1 then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED test1: field=% expected=% result=%', rec1.field, rec1.expected_result, res1;
end if;
exception
when others then
if rec1.expected_result is null then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED test1/ex: field=% expected=% result=exception', rec1.field, rec1.expected_result;
end if;
end;
end loop;
drop table cronexp_tests1;
create temporary table cronexp_tests2
(
ts timestamp with time zone,
field text,
expected_result boolean,
primary key (ts, field)
);
insert into cronexp_tests2(ts, field, expected_result)
values ('2021-02-03 04:05:06+00', '* * * * *', true), -- basic
('2021-02-03 04:05:06+00', '5 * * * *', true),
('2021-02-03 04:05:06+00', '* 4 * * *', true),
('2021-02-03 04:05:06+00', '* * 3 * *', true),
('2021-02-03 04:05:06+00', '* * * 2 *', true),
('2021-02-03 04:05:06+00', '* * * * 3', true),
('2021-01-01 01:01:01+02', '* * * * *', true), -- another time zone
('2021-01-01 01:01:01+02', '1 * * * *', true),
('2021-01-01 01:01:01+02', '* 23 * * *', true),
('2021-01-01 01:01:01+02', '* * 31 * *', true),
('2021-01-01 01:01:01+02', '* * * 12 *', true),
('2021-01-01 01:01:01+02', '* * * * 4', true),
('2021-02-03 04:05:06+00', '4 * * * *', false), -- off by one
('2021-02-03 04:05:06+00', '6 * * * *', false),
('2021-02-03 04:05:06+00', '* 3 * * *', false),
('2021-02-03 04:05:06+00', '* 5 * * *', false),
('2021-02-03 04:05:06+00', '* * 2 * *', false),
('2021-02-03 04:05:06+00', '* * 4 * *', false),
('2021-02-03 04:05:06+00', '* * * 1 *', false),
('2021-02-03 04:05:06+00', '* * * 3 *', false),
('2021-02-03 04:05:06+00', '* * * * 2', false),
('2021-02-03 04:05:06+00', '* * * * 4', false),
('2021-02-28 00:00:00+00', '* * * * *', true), -- 0 or 7 matches Sunday
('2021-02-28 00:00:00+00', '* * * * 0', true),
('2021-02-28 00:00:00+00', '* * * * 1', false),
('2021-02-28 00:00:00+00', '* * * * 2', false),
('2021-02-28 00:00:00+00', '* * * * 3', false),
('2021-02-28 00:00:00+00', '* * * * 4', false),
('2021-02-28 00:00:00+00', '* * * * 5', false),
('2021-02-28 00:00:00+00', '* * * * 6', false),
('2021-02-28 00:00:00+00', '* * * * 7', true),
('2021-03-04 05:06:07+00', '06 * * * *', true), -- range bounderies
('2021-03-04 05:06:07+00', '59 * * * *', false),
('2021-03-04 05:06:07+00', '60 * * * *', null),
('2021-03-04 05:06:07+00', ' * 05 * * *', true),
('2021-03-04 05:06:07+00', ' * 23 * * *', false),
('2021-03-04 05:06:07+00', ' * 24 * * *', null),
('2021-03-04 05:06:07+00', ' * * 04 * *', true),
('2021-03-04 05:06:07+00', ' * * 31 * *', false),
('2021-03-04 05:06:07+00', ' * * 32 * *', null),
('2021-03-04 05:06:07+00', ' * * * 03 *', true),
('2021-03-04 05:06:07+00', ' * * * 12 *', false),
('2021-03-04 05:06:07+00', ' * * * 13 *', null),
('2021-03-04 05:06:07+00', ' * * * * 4', true),
('2021-03-04 05:06:07+00', ' * * * * 7', false),
('2021-03-04 05:06:07+00', ' * * * * 8', null),
('2021-12-31 23:59:59+00', '0-59 0-23 1-31 1-12 0-7', true), -- ranges
('2021-12-31 23:59:59+00', '51-59/2 11-23/2 21-31/2 10-12/2 1-7/2', true),
('2021-12-31 23:59:59+00', '50-59/2 10-23/2 20-31/2 9-12/2 0-7/2', false),
('2021-12-31 23:59:59+00', '0 0 0 0 0', false), -- misc
('2021-12-31 23:59:59+00', '1 1 1 1 1', false),
('2021-12-31 23:59:59+00', '1 1 1 1 1 1', null),
('2021-12-31 23:59:59+00', '1 1 1 1', null),
('2021-12-31 23:59:59+00', '* * * * * *', null),
('2021-12-31 23:59:59+00', '* * * *', null),
('2021-12-31 23:59:59+00', 'a * * * *', null),
('2021-12-31 23:59:59+00', '* a * * *', null),
('2021-12-31 23:59:59+00', '* * a * *', null),
('2021-12-31 23:59:59+00', '* * * a *', null),
('2021-12-31 23:59:59+00', '* * * * a', null),
('2021-12-31 23:59:59+00', ' * * * * * ', true),
('2021-12-31 23:59:59+00', ' */1 */1 */1 */1 */1 ', true);
set local time zone 'UTC';
for rec2 in select * from cronexp_tests2
loop
begin
cnt_total := cnt_total + 1;
res2 := cronexp.match(rec2.ts, rec2.field);
if rec2.expected_result = res2 then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED test2: ts=%, field=%, expected=% result=%', rec2.ts, rec2.field, rec2.expected_result, res2;
end if;
exception
when others then
if rec2.expected_result is null then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED test2/ex: ts=%, field=%, expected=% result=%', rec2.ts, rec2.field, rec2.expected_result, res2;
end if;
end;
end loop;
drop table cronexp_tests2;
cnt_total := cnt_total + 5;
if cronexp.is_wellformed('* * * * *') then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED on is_wellformed()/1';
end if;
if cronexp.is_wellformed(' * * * * * ') then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED on is_wellformed()/2';
end if;
if not cronexp.is_wellformed(null) then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED on is_wellformed()/3';
end if;
if not cronexp.is_wellformed(' * * * * ') then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED on is_wellformed()/4';
end if;
if not cronexp.is_wellformed('') then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED on is_wellformed()/5';
end if;
raise info '%/% tests passed', cnt_correct, cnt_total;
end;
$$ language 'plpgsql';
CREATE OR REPLACE FUNCTION cronexp.benchmark()
RETURNS VOID AS $$
DECLARE
start_ts TIMESTAMPTZ;
end_ts TIMESTAMPTZ;
duration INTERVAL;
i INTEGER;
-- Test edilecek çeşitli cron ifadeleri
expressions TEXT[] := ARRAY[
'* * * * *', -- Her dakika (en basit)
'*/15 * * * *', -- Her 15 dakikada bir
'0 9 * * 1', -- Her Pazartesi sabah 9'da
'30 14 15 * *', -- Her ayın 15'inde 14:30'da
'0 0 1 1 *', -- Her yılın ilk günü gece yarısı
'0 12 * * 1-5', -- Hafta içi her gün öğlen 12'de
'0 22 * * 0', -- Her Pazar 22:00'de
'1,16,31,46 * * * *' -- Karmaşık liste
];
exp TEXT;
base_ts TIMESTAMPTZ := now();
iterations INT := 10000;
single_exp TEXT := '30 9 * * 1-5';
BEGIN
RAISE NOTICE '--- Cron Expression Benchmark Testi Başlatılıyor ---';
RAISE NOTICE 'Mevcut zaman: %', base_ts;
RAISE NOTICE '----------------------------------------------------';
-- ADIM 1: "SOĞUK ÖNBELLEK" TESTİ (CACHE BOŞKEN)
RAISE NOTICE '';
RAISE NOTICE '>>> ADIM 1: Soğuk Önbellek Testi (İlk Çalıştırma)';
RAISE NOTICE 'Önbellek tabloları temizleniyor...';
TRUNCATE cronexp.expressions, cronexp.parsed_patterns;
RAISE NOTICE 'İlk çalıştırma için zaman ölçümü başlatılıyor...';
start_ts := clock_timestamp();
FOREACH exp IN ARRAY expressions
LOOP
PERFORM cronexp.next_run(exp, base_ts);
END LOOP;
end_ts := clock_timestamp();
duration := end_ts - start_ts;
RAISE NOTICE 'Soğuk Önbellek Testi Tamamlandı. Toplam Süre (% ifade için): %', array_length(expressions, 1), duration;
RAISE NOTICE '----------------------------------------------------';
-- ADIM 2: "SICAK ÖNBELLEK" TESTİ (CACHE DOLUYKEN)
RAISE NOTICE '';
RAISE NOTICE '>>> ADIM 2: Sıcak Önbellek Testi (İkinci Çalıştırma)';
RAISE NOTICE 'Önbellek dolu. Aynı ifadeler tekrar çalıştırılıyor...';
start_ts := clock_timestamp();
FOREACH exp IN ARRAY expressions
LOOP
PERFORM cronexp.next_run(exp, base_ts);
END LOOP;
end_ts := clock_timestamp();
duration := end_ts - start_ts;
RAISE NOTICE 'Sıcak Önbellek Testi Tamamlandı. Toplam Süre (% ifade için): %', array_length(expressions, 1), duration;
RAISE NOTICE '>>> SONUÇ: Sıcak önbellek hızı, soğuk önbelleğe göre kat kat daha fazladır!';
RAISE NOTICE '----------------------------------------------------';
-- ADIM 3: YÜKSEK HACİMLİ STRES TESTİ
RAISE NOTICE '';
RAISE NOTICE '>>> ADIM 3: Yüksek Hacimli Tekrarlı Çağrı (Stres) Testi';
-- DÜZELTME: Bu bölüm, kendi başına bir BEGIN/END bloğu içine alındı.
-- BEGIN
-- DECLARE
-- iterations INT := 10000;
-- single_exp TEXT := '30 9 * * 1-5';
--
-- RAISE NOTICE '% defa aynı ifadenin çağrılması test ediliyor: "%"', iterations, single_exp;
--
-- start_ts := clock_timestamp();
-- FOR i IN 1..iterations LOOP
-- PERFORM cronexp.next_run(single_exp, base_ts);
-- END LOOP;
-- end_ts := clock_timestamp();
--
-- duration := end_ts - start_ts;
-- RAISE NOTICE 'Yüksek Hacim Testi Tamamlandı. Toplam Süre: %', duration;
-- RAISE NOTICE 'Çağrı başına ortalama süre: % mikrosaniye', (EXTRACT(EPOCH FROM duration) * 1000000 / iterations)::numeric(10, 2);
-- END;
RAISE NOTICE '% defa aynı ifadenin çağrılması test ediliyor: "%"', iterations, single_exp;
start_ts := clock_timestamp();
FOR i IN 1..iterations LOOP
PERFORM cronexp.next_run(single_exp, base_ts);
END LOOP;
end_ts := clock_timestamp();
duration := end_ts - start_ts;
RAISE NOTICE 'Yüksek Hacim Testi Tamamlandı. Toplam Süre: %', duration;
RAISE NOTICE 'Çağrı başına ortalama süre: % mikrosaniye', (EXTRACT(EPOCH FROM duration) * 1000000 / iterations)::numeric(10, 2);
RAISE NOTICE '----------------------------------------------------';
RAISE NOTICE '--- Benchmark Testi Tamamlandı ---';
END;
$$ LANGUAGE 'plpgsql' VOLATILE; -- Fonksiyon TRUNCATE içerdiği ve VOLATILE fonksiyonlar çağırdığı için VOLATILE olmalı.
-- Run the tests
-- ======================================================================== --
-- YARDIMCI FONKSİYON: _find_next_val
-- AÇIKLAMA: Bir tamsayı dizisi içinde, verilen bir değerden büyük veya eşit
-- olan ilk değeri bulur. Bulamazsa, dizinin ilk elemanını döndürür.
-- `next_jump` tarafından kullanılır.
-- ======================================================================== --
CREATE OR REPLACE FUNCTION cronexp._find_next_val(vals int[], current_val int, OUT next_val int, OUT wrapped boolean)
AS $$
BEGIN
wrapped := false;
SELECT v INTO next_val FROM unnest(vals) v WHERE v >= current_val ORDER BY v LIMIT 1;
IF NOT FOUND THEN
wrapped := true;
next_val := vals[1];
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- ======================================================================== --
-- YARDIMCI FONKSİYON: _find_prev_val
-- AÇIKLAMA: Bir tamsayı dizisi içinde, verilen bir değerden küçük veya eşit
-- olan son değeri bulur. Bulamazsa, dizinin son elemanını döndürür.
-- `prev_jump` tarafından kullanılır.
-- ======================================================================== --
CREATE OR REPLACE FUNCTION cronexp._find_prev_val(vals int[], current_val int, OUT prev_val int, OUT wrapped boolean)
AS $$
BEGIN
wrapped := false;
SELECT v INTO prev_val FROM unnest(vals) v WHERE v <= current_val ORDER BY v DESC LIMIT 1;
IF NOT FOUND THEN
wrapped := true;
prev_val := vals[array_length(vals, 1)];
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- ======================================================================== --
-- FUNCTION: cronexp.next_jump(exp TEXT, from_ts TIMESTAMPTZ)
-- AÇIKLAMA: Dakika dakika aramak yerine, bir sonraki çalışma zamanına
-- doğrudan "zıplayan" ultra performanslı fonksiyon.
-- ======================================================================== --
-- ======================================================================== --
-- FUNCTION: cronexp.next_jump(exp TEXT, from_ts TIMESTAMPTZ)
-- DÜZELTME: date_part() sonuçları integer'a cast edildi.
-- ======================================================================== --
CREATE OR REPLACE FUNCTION cronexp.next_jump(exp TEXT, from_ts TIMESTAMPTZ)
RETURNS TIMESTAMPTZ AS $$
DECLARE
pattern cronexp.parsed_patterns;
ts TIMESTAMPTZ;
next_val INT;
wrapped BOOLEAN;
i INT := 0;
MAX_ITERATIONS CONSTANT INT := 1440;
BEGIN
SELECT * INTO pattern FROM cronexp.parsed_patterns WHERE expression = exp;
IF NOT FOUND THEN
PERFORM cronexp.match(from_ts, exp);
SELECT * INTO pattern FROM cronexp.parsed_patterns WHERE expression = exp;
END IF;
ts := date_trunc('minute', from_ts) + interval '1 minute';
LOOP
i := i + 1;
IF i > MAX_ITERATIONS THEN RAISE EXCEPTION 'Could not find next run time within reasonable iterations.'; END IF;
SELECT * INTO next_val, wrapped FROM cronexp._find_next_val(pattern.month_values, date_part('month', ts)::int);
IF next_val != date_part('month', ts)::int OR wrapped THEN
ts := date_trunc('year', ts) + (next_val - 1) * interval '1 month';
IF wrapped THEN ts := ts + interval '1 year'; END IF;
CONTINUE;
END IF;
LOOP
IF cronexp.match(date_trunc('day', ts), exp) THEN EXIT; END IF;
ts := date_trunc('day', ts) + interval '1 day';
IF date_part('month', ts)::int != next_val THEN CONTINUE ; END IF;
END LOOP;
SELECT * INTO next_val, wrapped FROM cronexp._find_next_val(pattern.hour_values, date_part('hour', ts)::int);
IF next_val != date_part('hour', ts)::int OR wrapped THEN
ts := date_trunc('day', ts) + next_val * interval '1 hour';
IF wrapped THEN ts := ts + interval '1 day'; END IF;
CONTINUE;
END IF;
SELECT * INTO next_val, wrapped FROM cronexp._find_next_val(pattern.minute_values, date_part('minute', ts)::int);
IF next_val != date_part('minute', ts)::int OR wrapped THEN
ts := date_trunc('hour', ts) + next_val * interval '1 minute';
IF wrapped THEN ts := ts + interval '1 hour'; END IF;
CONTINUE;
END IF;
RETURN ts;
END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;
-- ======================================================================== --
-- FUNCTION: cronexp.prev_jump(exp TEXT, from_ts TIMESTAMPTZ)
-- DÜZELTME: date_part() sonuçları integer'a cast edildi ve mantık iyileştirildi.
-- ======================================================================== --
CREATE OR REPLACE FUNCTION cronexp.prev_jump(exp TEXT, from_ts TIMESTAMPTZ)
RETURNS TIMESTAMPTZ AS $$
DECLARE
pattern cronexp.parsed_patterns;
ts TIMESTAMPTZ;
prev_val INT;
wrapped BOOLEAN;
i INT := 0;
MAX_ITERATIONS CONSTANT INT := 1440;
BEGIN
SELECT * INTO pattern FROM cronexp.parsed_patterns WHERE expression = exp;
IF NOT FOUND THEN
PERFORM cronexp.match(from_ts, exp);
SELECT * INTO pattern FROM cronexp.parsed_patterns WHERE expression = exp;
END IF;
ts := date_trunc('minute', from_ts) - interval '1 minute';
LOOP
i := i + 1;
IF i > MAX_ITERATIONS THEN RAISE EXCEPTION 'Could not find previous run time within reasonable iterations.'; END IF;
SELECT * INTO prev_val, wrapped FROM cronexp._find_prev_val(pattern.month_values, date_part('month', ts)::int);
IF prev_val != date_part('month', ts)::int OR wrapped THEN
ts := date_trunc('year', ts) + (prev_val - 1) * interval '1 month';
IF wrapped THEN ts := ts - interval '1 year'; END IF;
ts := ts + interval '1 month' - interval '1 day'; -- Ay sonuna git
ts := date_trunc('day', ts) + interval '23 hours 59 minutes';
CONTINUE;
END IF;
LOOP
IF cronexp.match(date_trunc('day', ts), exp) THEN EXIT; END IF;
ts := date_trunc('day', ts) - interval '1 minute';
IF date_part('month', ts)::int != prev_val THEN CONTINUE ; END IF;
END LOOP;
SELECT * INTO prev_val, wrapped FROM cronexp._find_prev_val(pattern.hour_values, date_part('hour', ts)::int);
IF prev_val != date_part('hour', ts)::int OR wrapped THEN
ts := date_trunc('day', ts) + prev_val * interval '1 hour';
IF wrapped THEN
ts := date_trunc('day', ts) - interval '1 minute';
ELSE
ts := ts + interval '59 minutes';
END IF;
CONTINUE;
END IF;
SELECT * INTO prev_val, wrapped FROM cronexp._find_prev_val(pattern.minute_values, date_part('minute', ts)::int);
IF prev_val != date_part('minute', ts)::int OR wrapped THEN
ts := date_trunc('hour', ts) + prev_val * interval '1 minute';
IF wrapped THEN ts := date_trunc('hour', ts) - interval '1 minute'; END IF;
CONTINUE;
END IF;
RETURN ts;
END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;
select cronexp.tests();
select cronexp.benchmark();
SELECT cronexp.expand_field('5', 0, 59);
explain analyze
select cronexp.next_jump('* * * * 0,2-3,5-6', now()-interval '23 years') from generate_series(0,1000) s;
explain analyze
select cronexp.next_run('* * * * 0,2-3,5-6', now())from generate_series(0,1000) s;
DO $$
DECLARE
start_ts TIMESTAMPTZ;
BEGIN
RAISE NOTICE '--- ALGORİTMA KARŞILAŞTIRMA TESTİ ---';
RAISE NOTICE 'Hedef: Gelecek yılın ilk günü (0 0 1 1 *)';
RAISE NOTICE 'Başlangıç Zamanı: %', now();
RAISE NOTICE '------------------------------------------------';
-- YAVAŞ YÖNTEM (Dakika Dakika Arama)
RAISE NOTICE '1. next_run (Yürüyerek) ile test ediliyor... (Bu işlem BİRKAÇ SANİYE sürebilir)';
start_ts := clock_timestamp();
PERFORM cronexp.next_run('0 0 1 1 *', now());
RAISE NOTICE ' -> next_run Süresi: %', clock_timestamp() - start_ts;
RAISE NOTICE '------------------------------------------------';
-- HIZLI YÖNTEM (Akıllı Zıplama)
RAISE NOTICE '2. next_jump (Uçakla) ile test ediliyor...';
start_ts := clock_timestamp();
PERFORM cronexp.next_jump('0 0 1 1 *', now());
RAISE NOTICE ' -> next_jump Süresi: %', clock_timestamp() - start_ts;
RAISE NOTICE '------------------------------------------------';
RAISE NOTICE 'SONUÇ: "jump" fonksiyonu, uzak hedefler için katlanarak artan bir hız avantajı sağlar!';
END;
$$;
Raw
pg_cron_parser_v3.sql
drop SCHEMA IF EXISTS cronexp cascade ;
-- ======================================================================== --
-- PostgreSQL Cron Expression Parser - Kararlı Sürüm v5.0 --
-- ======================================================================== --
-- Önceki kurulumu tamamen temizle
DROP SCHEMA IF EXISTS cronexp CASCADE;
-- Şemayı oluştur
CREATE SCHEMA cronexp;
-- TABLO 1: Düşük seviye önbellek
CREATE TABLE cronexp.expressions (
field TEXT NOT NULL,
min_val INT NOT NULL,
max_val INT NOT NULL,
expanded_values INT[] NOT NULL,
PRIMARY KEY (field, min_val, max_val)
);
-- TABLO 2: Yüksek seviye önbellek
CREATE TABLE cronexp.parsed_patterns (
expression TEXT PRIMARY KEY,
minute_values INT[] NOT NULL,
hour_values INT[] NOT NULL,
dom_values INT[] NOT NULL,
month_values INT[] NOT NULL,
dow_values INT[] NOT NULL,
is_dom_restricted BOOLEAN NOT NULL,
is_dow_restricted BOOLEAN NOT NULL
);
-- FUNCTION: cronexp.expand_field (VOLATILITY DÜZELTİLDİ)
CREATE OR REPLACE FUNCTION cronexp.expand_field(p_field text, p_min int, p_max int)
RETURNS int[] AS $$
DECLARE
part TEXT; groups TEXT[]; m INT; n INT; k INT; ret INT[]; tmp INT[]; cached_ret INT[];
BEGIN
SELECT expanded_values INTO cached_ret FROM cronexp.expressions WHERE field = p_field AND min_val = p_min AND max_val = p_max;
IF FOUND THEN RETURN cached_ret; END IF;
IF coalesce(p_field, '') = '' THEN RAISE EXCEPTION 'invalid parameter "field"'; END IF;
IF p_min IS NULL OR p_max IS NULL OR p_min < 0 OR p_max < 0 OR p_min > p_max THEN RAISE EXCEPTION 'invalid parameter(s) "min" or "max"'; END IF;
IF p_field = '*' THEN
SELECT array_agg(x::int) INTO ret FROM generate_series(p_min, p_max) AS x;
INSERT INTO cronexp.expressions VALUES (p_field, p_min, p_max, ret) ON CONFLICT DO NOTHING; RETURN ret;
END IF;
IF p_field ~ '^\*/\d+$' THEN
groups = regexp_matches(p_field, '^\*/(\d+)$'); k := groups[1];
IF k < 1 OR k > p_max THEN RAISE EXCEPTION 'invalid range step: expected a step between 1 and %, got %', p_max, k; END IF;
SELECT array_agg(x::int) INTO ret FROM generate_series(p_min, p_max, k) AS x;
INSERT INTO cronexp.expressions VALUES (p_field, p_min, p_max, ret) ON CONFLICT DO NOTHING; RETURN ret;
END IF;
ret := '{}'::int[];
FOR part IN SELECT * FROM regexp_split_to_table(p_field, ',') LOOP
IF part ~ '^\d+$' THEN
n := part; IF n < p_min OR n > p_max THEN RAISE EXCEPTION 'value out of range'; END IF; ret = ret || n;
ELSIF part ~ '^\d+-\d+$' THEN
groups = regexp_matches(part, '^(\d+)-(\d+)$'); m := groups[1]; n := groups[2]; IF m > n THEN RAISE EXCEPTION 'inverted range bounds'; END IF; IF m < p_min OR m > p_max OR n < p_min OR n > p_max THEN RAISE EXCEPTION 'invalid range bound(s)'; END IF; SELECT array_agg(x) INTO tmp FROM generate_series(m, n) AS x; ret := ret || tmp;
ELSIF part ~ '^\d+-\d+/\d+$' THEN
groups = regexp_matches(part, '^(\d+)-(\d+)/(\d+)$'); m := groups[1]; n := groups[2]; k := groups[3]; IF m > n THEN RAISE EXCEPTION 'inverted range bounds'; END IF; IF m < p_min OR m > p_max OR n < p_min OR n > p_max THEN RAISE EXCEPTION 'invalid range bound(s)'; END IF; IF k < 1 OR k > p_max THEN RAISE EXCEPTION 'invalid range step'; END IF; SELECT array_agg(x) INTO tmp FROM generate_series(m, n, k) AS x; ret := ret || tmp;
ELSE RAISE EXCEPTION 'invalid expression'; END IF;
END LOOP;
SELECT array_agg(x) INTO ret FROM (SELECT DISTINCT unnest(ret) AS x ORDER BY x) AS sub;
INSERT INTO cronexp.expressions VALUES (p_field, p_min, p_max, ret) ON CONFLICT DO NOTHING;
RETURN ret;
END;
$$ LANGUAGE 'plpgsql' VOLATILE; -- DÜZELTME: INSERT içerdiği için VOLATILE olmalı
-- FUNCTION: cronexp.match (VOLATILITY DÜZELTİLDİ)
CREATE OR REPLACE FUNCTION cronexp.match(ts timestamp with time zone, exp text)
RETURNS boolean AS $$
DECLARE
pattern cronexp.parsed_patterns; groups text[]; ts_minute INT := date_part('minute', ts); ts_hour INT := date_part('hour', ts); ts_dom INT := date_part('day', ts); ts_month INT := date_part('month', ts); ts_dow INT := date_part('dow', ts); day_match BOOLEAN;
BEGIN
SELECT * INTO pattern FROM cronexp.parsed_patterns WHERE expression = exp;
IF NOT FOUND THEN
groups := regexp_split_to_array(trim(exp), '\s+');
IF array_length(groups, 1) IS DISTINCT FROM 5 THEN RAISE EXCEPTION 'invalid cron expression: five space-separated fields expected, got %', array_length(groups, 1); END IF;
pattern.expression := exp;
pattern.minute_values := cronexp.expand_field(groups[1], 0, 59);
pattern.hour_values := cronexp.expand_field(groups[2], 0, 23);
pattern.dom_values := cronexp.expand_field(groups[3], 1, 31);
pattern.month_values := cronexp.expand_field(groups[4], 1, 12);
pattern.dow_values := cronexp.expand_field(groups[5], 0, 7);
pattern.is_dom_restricted := (groups[3] != '*');
pattern.is_dow_restricted := (groups[5] != '*');
IF 7 = ANY(pattern.dow_values) THEN pattern.dow_values := array_append(pattern.dow_values, 0); END IF;
INSERT INTO cronexp.parsed_patterns VALUES (pattern.*) ON CONFLICT (expression) DO NOTHING;
SELECT * INTO pattern FROM cronexp.parsed_patterns WHERE expression = exp;
END IF;
IF NOT (ts_minute = ANY(pattern.minute_values) AND ts_hour = ANY(pattern.hour_values) AND ts_month = ANY(pattern.month_values)) THEN RETURN FALSE; END IF;
IF pattern.is_dom_restricted AND pattern.is_dow_restricted THEN
day_match := (ts_dom = ANY(pattern.dom_values)) OR (ts_dow = ANY(pattern.dow_values));
ELSIF pattern.is_dom_restricted THEN day_match := (ts_dom = ANY(pattern.dom_values));
ELSIF pattern.is_dow_restricted THEN day_match := (ts_dow = ANY(pattern.dow_values));
ELSE day_match := TRUE;
END IF;
RETURN day_match;
END
$$ LANGUAGE 'plpgsql' VOLATILE; -- DÜZELTME: INSERT içerdiği için VOLATILE olmalı
-- FUNCTION: cronexp.is_wellformed (VOLATILITY DÜZELTİLDİ)
CREATE OR REPLACE FUNCTION cronexp.is_wellformed(exp text)
RETURNS boolean AS $$
BEGIN
PERFORM cronexp.match(now(), exp);
RETURN TRUE;
EXCEPTION WHEN OTHERS THEN RETURN FALSE;
END;
$$ LANGUAGE 'plpgsql' VOLATILE; -- DÜZELTME: VOLATILE bir fonksiyonu çağırdığı için VOLATILE olmalı
-- FUNCTION: cronexp.next_run (VOLATILITY DÜZELTİLDİ)
CREATE OR REPLACE FUNCTION cronexp.next_run(exp TEXT, from_ts TIMESTAMPTZ)
RETURNS TIMESTAMPTZ AS $$
DECLARE
current_ts TIMESTAMPTZ := date_trunc('minute', from_ts);
safety_limit_ts TIMESTAMPTZ := from_ts + interval '5 years';
BEGIN
IF NOT cronexp.is_wellformed(exp) THEN
RAISE EXCEPTION 'Invalid cron expression format: "%"', exp;
END IF;
LOOP
current_ts := current_ts + interval '1 minute';
IF current_ts > safety_limit_ts THEN
RAISE EXCEPTION 'Could not find a matching run time within 5 years for expression: "%".', exp;
END IF;
IF cronexp.match(current_ts, exp) THEN
RETURN current_ts;
END IF;
END LOOP;
END;
$$ LANGUAGE 'plpgsql' VOLATILE; -- DÜZELTME: VOLATILE bir fonksiyonu çağırdığı için VOLATILE olmalı
-- FUNCTION: cronexp.prev_run (VOLATILITY DÜZELTİLDİ)
CREATE OR REPLACE FUNCTION cronexp.prev_run(exp TEXT, from_ts TIMESTAMPTZ)
RETURNS TIMESTAMPTZ AS $$
DECLARE
current_ts TIMESTAMPTZ := date_trunc('minute', from_ts);
safety_limit_ts TIMESTAMPTZ := from_ts - interval '5 years';
BEGIN
IF NOT cronexp.is_wellformed(exp) THEN
RAISE EXCEPTION 'Invalid cron expression format: "%"', exp;
END IF;
LOOP
current_ts := current_ts - interval '1 minute';
IF current_ts < safety_limit_ts THEN
RAISE EXCEPTION 'Could not find a matching run time within 5 years for expression: "%".', exp;
END IF;
IF cronexp.match(current_ts, exp) THEN
RETURN current_ts;
END IF;
END LOOP;
END;
$$ LANGUAGE 'plpgsql' VOLATILE; -- DÜZELTME: VOLATILE bir fonksiyonu çağırdığı için VOLATILE olmalı
create or replace function cronexp.tests()
returns void as
$$
declare
rec1 record;
res1 int[];
rec2 record;
res2 boolean;
cnt_correct int := 0;
cnt_total int := 0;
begin
create temporary table cronexp_tests1
(
field text,
expected_result int[],
primary key (field)
);
insert into cronexp_tests1(field, expected_result)
values ('5', '{5}'),
('6', '{6}'),
('99', '{99}'),
('100', '{100}'),
('0', null),
('4', null),
('101', null),
('1000', null),
('5,6', '{5,6}'),
('99,100', '{99,100}'),
('5,100', '{5,100}'),
('100,5', '{5,100}'),
('5,101', null),
('101,5', null),
('4,100', null),
('100,4', null),
('4,101', null),
('6,5,7,100,8,49', '{5,6,7,8,49,100}'),
('5-10', '{5,6,7,8,9,10}'),
('95-100', '{95,96,97,98,99,100}'),
('13-17', '{13,14,15,16,17}'),
('5-5', '{5}'),
('100-100', '{100}'),
('23-23', '{23}'),
('95-101', null),
('4-10', null),
('4-101', null),
('3-4', null),
('101-102', null),
('10-5', null),
('100-95', null),
('17-13', null),
('101-95', null),
('10-4', null),
('101-4', null),
('4-3', null),
('102-101', null),
('5-10/1', '{5,6,7,8,9,10}'),
('95-100/1', '{95,96,97,98,99,100}'),
('13-17/1', '{13,14,15,16,17}'),
('5-5/1', '{5}'),
('100-100/1', '{100}'),
('23-23/1', '{23}'),
('95-101/1', null),
('4-10/1', null),
('4-101/1', null),
('3-4/1', null),
('101-102/1', null),
('10-5/1', null),
('100-95/1', null),
('17-13/1', null),
('101-95/1', null),
('10-4/1', null),
('101-4/1', null),
('4-3/1', null),
('102-101/1', null),
('5-11/2', '{5,7,9,11}'),
('5-12/2', '{5,7,9,11}'),
('5-13/2', '{5,7,9,11,13}'),
('6-11/2', '{6,8,10}'),
('6-12/2', '{6,8,10,12}'),
('6-13/2', '{6,8,10,12}'),
('5-10/0', null),
('5-10/99', '{5}'),
('5-10/100', '{5}'),
('5-10/101', null),
('4-10/2', null),
('5-101/2', null),
('5-11/2,7', '{5,7,9,11}'),
('5-12/2,7', '{5,7,9,11}'),
('5-13/2,7', '{5,7,9,11,13}'),
('6-11/2,8', '{6,8,10}'),
('6-12/2,8', '{6,8,10,12}'),
('6-13/2,8', '{6,8,10,12}'),
('7,5-11/2', '{5,7,9,11}'),
('7,5-12/2', '{5,7,9,11}'),
('7,5-13/2', '{5,7,9,11,13}'),
('8,6-11/2', '{6,8,10}'),
('8,6-12/2', '{6,8,10,12}'),
('8,6-13/2', '{6,8,10,12}'),
('5-11/2,77', '{5,7,9,11,77}'),
('5-12/2,77', '{5,7,9,11,77}'),
('5-13/2,77', '{5,7,9,11,13,77}'),
('6-11/2,77', '{6,8,10,77}'),
('6-12/2,77', '{6,8,10,12,77}'),
('6-13/2,77', '{6,8,10,12,77}'),
('77,5-11/2', '{5,7,9,11,77}'),
('77,5-12/2', '{5,7,9,11,77}'),
('77,5-13/2', '{5,7,9,11,13,77}'),
('77,6-11/2', '{6,8,10,77}'),
('77,6-12/2', '{6,8,10,12,77}'),
('77,6-13/2', '{6,8,10,12,77}'),
('*',
'{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}'),
('*/0', null),
('*/1',
'{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}'),
('*/2',
'{5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39,41,43,45,47,49,51,53,55,57,59,61,63,65,67,69,71,73,75,77,79,81,83,85,87,89,91,93,95,97,99}'),
('*/7', '{5,12,19,26,33,40,47,54,61,68,75,82,89,96}'),
('*/101', null),
('5,5-9', '{5,6,7,8,9}'),
('5,6-9', '{5,6,7,8,9}'),
('5,6,6-9', '{5,6,7,8,9}'),
('5,6,7-9', '{5,6,7,8,9}'),
('5-9,5', '{5,6,7,8,9}'),
('6-9,5', '{5,6,7,8,9}'),
('6-9,5,6', '{5,6,7,8,9}'),
('7-9,5,6', '{5,6,7,8,9}'),
('5,5-9,100', '{5,6,7,8,9,100}'),
('5,6-9,100', '{5,6,7,8,9,100}'),
('5,6,6-9,100', '{5,6,7,8,9,100}'),
('5,6,7-9,100', '{5,6,7,8,9,100}'),
('5-9,5,100', '{5,6,7,8,9,100}'),
('6-9,5,100', '{5,6,7,8,9,100}'),
('6-9,5,6,100', '{5,6,7,8,9,100}'),
('7-9,5,6,100', '{5,6,7,8,9,100}'),
('5,4-9', null),
('4,6-9', null),
('4-9,5', null),
('6-9,4', null),
('5,5-101', null),
('5,101-9', null),
('5-101,5', null),
('6-101,5', null),
('9--9', null),
('9,,9', null),
(',', null),
('-', null),
(' ', null),
('a', null),
('-5', null),
('10-14,17-32/5', '{10,11,12,13,14,17,22,27,32}'),
('10-14,17-32/5,10-14,17-32/5', '{10,11,12,13,14,17,22,27,32}');
for rec1 in select * from cronexp_tests1
loop
begin
cnt_total := cnt_total + 1;
res1 := cronexp.expand_field(rec1.field, 5, 100);
if rec1.expected_result = res1 then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED test1: field=% expected=% result=%', rec1.field, rec1.expected_result, res1;
end if;
exception
when others then
if rec1.expected_result is null then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED test1/ex: field=% expected=% result=exception', rec1.field, rec1.expected_result;
end if;
end;
end loop;
drop table cronexp_tests1;
create temporary table cronexp_tests2
(
ts timestamp with time zone,
field text,
expected_result boolean,
primary key (ts, field)
);
insert into cronexp_tests2(ts, field, expected_result)
values ('2021-02-03 04:05:06+00', '* * * * *', true), -- basic
('2021-02-03 04:05:06+00', '5 * * * *', true),
('2021-02-03 04:05:06+00', '* 4 * * *', true),
('2021-02-03 04:05:06+00', '* * 3 * *', true),
('2021-02-03 04:05:06+00', '* * * 2 *', true),
('2021-02-03 04:05:06+00', '* * * * 3', true),
('2021-01-01 01:01:01+02', '* * * * *', true), -- another time zone
('2021-01-01 01:01:01+02', '1 * * * *', true),
('2021-01-01 01:01:01+02', '* 23 * * *', true),
('2021-01-01 01:01:01+02', '* * 31 * *', true),
('2021-01-01 01:01:01+02', '* * * 12 *', true),
('2021-01-01 01:01:01+02', '* * * * 4', true),
('2021-02-03 04:05:06+00', '4 * * * *', false), -- off by one
('2021-02-03 04:05:06+00', '6 * * * *', false),
('2021-02-03 04:05:06+00', '* 3 * * *', false),
('2021-02-03 04:05:06+00', '* 5 * * *', false),
('2021-02-03 04:05:06+00', '* * 2 * *', false),
('2021-02-03 04:05:06+00', '* * 4 * *', false),
('2021-02-03 04:05:06+00', '* * * 1 *', false),
('2021-02-03 04:05:06+00', '* * * 3 *', false),
('2021-02-03 04:05:06+00', '* * * * 2', false),
('2021-02-03 04:05:06+00', '* * * * 4', false),
('2021-02-28 00:00:00+00', '* * * * *', true), -- 0 or 7 matches Sunday
('2021-02-28 00:00:00+00', '* * * * 0', true),
('2021-02-28 00:00:00+00', '* * * * 1', false),
('2021-02-28 00:00:00+00', '* * * * 2', false),
('2021-02-28 00:00:00+00', '* * * * 3', false),
('2021-02-28 00:00:00+00', '* * * * 4', false),
('2021-02-28 00:00:00+00', '* * * * 5', false),
('2021-02-28 00:00:00+00', '* * * * 6', false),
('2021-02-28 00:00:00+00', '* * * * 7', true),
('2021-03-04 05:06:07+00', '06 * * * *', true), -- range bounderies
('2021-03-04 05:06:07+00', '59 * * * *', false),
('2021-03-04 05:06:07+00', '60 * * * *', null),
('2021-03-04 05:06:07+00', ' * 05 * * *', true),
('2021-03-04 05:06:07+00', ' * 23 * * *', false),
('2021-03-04 05:06:07+00', ' * 24 * * *', null),
('2021-03-04 05:06:07+00', ' * * 04 * *', true),
('2021-03-04 05:06:07+00', ' * * 31 * *', false),
('2021-03-04 05:06:07+00', ' * * 32 * *', null),
('2021-03-04 05:06:07+00', ' * * * 03 *', true),
('2021-03-04 05:06:07+00', ' * * * 12 *', false),
('2021-03-04 05:06:07+00', ' * * * 13 *', null),
('2021-03-04 05:06:07+00', ' * * * * 4', true),
('2021-03-04 05:06:07+00', ' * * * * 7', false),
('2021-03-04 05:06:07+00', ' * * * * 8', null),
('2021-12-31 23:59:59+00', '0-59 0-23 1-31 1-12 0-7', true), -- ranges
('2021-12-31 23:59:59+00', '51-59/2 11-23/2 21-31/2 10-12/2 1-7/2', true),
('2021-12-31 23:59:59+00', '50-59/2 10-23/2 20-31/2 9-12/2 0-7/2', false),
('2021-12-31 23:59:59+00', '0 0 0 0 0', false), -- misc
('2021-12-31 23:59:59+00', '1 1 1 1 1', false),
('2021-12-31 23:59:59+00', '1 1 1 1 1 1', null),
('2021-12-31 23:59:59+00', '1 1 1 1', null),
('2021-12-31 23:59:59+00', '* * * * * *', null),
('2021-12-31 23:59:59+00', '* * * *', null),
('2021-12-31 23:59:59+00', 'a * * * *', null),
('2021-12-31 23:59:59+00', '* a * * *', null),
('2021-12-31 23:59:59+00', '* * a * *', null),
('2021-12-31 23:59:59+00', '* * * a *', null),
('2021-12-31 23:59:59+00', '* * * * a', null),
('2021-12-31 23:59:59+00', ' * * * * * ', true),
('2021-12-31 23:59:59+00', ' */1 */1 */1 */1 */1 ', true);
set local time zone 'UTC';
for rec2 in select * from cronexp_tests2
loop
begin
cnt_total := cnt_total + 1;
res2 := cronexp.match(rec2.ts, rec2.field);
if rec2.expected_result = res2 then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED test2: ts=%, field=%, expected=% result=%', rec2.ts, rec2.field, rec2.expected_result, res2;
end if;
exception
when others then
if rec2.expected_result is null then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED test2/ex: ts=%, field=%, expected=% result=%', rec2.ts, rec2.field, rec2.expected_result, res2;
end if;
end;
end loop;
drop table cronexp_tests2;
cnt_total := cnt_total + 5;
if cronexp.is_wellformed('* * * * *') then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED on is_wellformed()/1';
end if;
if cronexp.is_wellformed(' * * * * * ') then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED on is_wellformed()/2';
end if;
if not cronexp.is_wellformed(null) then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED on is_wellformed()/3';
end if;
if not cronexp.is_wellformed(' * * * * ') then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED on is_wellformed()/4';
end if;
if not cronexp.is_wellformed('') then
cnt_correct := cnt_correct + 1;
else
raise info 'FAILED on is_wellformed()/5';
end if;
raise info '%/% tests passed', cnt_correct, cnt_total;
end;
$$ language 'plpgsql';
CREATE OR REPLACE FUNCTION cronexp.benchmark()
RETURNS VOID AS $$
DECLARE
start_ts TIMESTAMPTZ;
end_ts TIMESTAMPTZ;
duration INTERVAL;
i INTEGER;
-- Test edilecek çeşitli cron ifadeleri
expressions TEXT[] := ARRAY[
'* * * * *', -- Her dakika (en basit)
'*/15 * * * *', -- Her 15 dakikada bir
'0 9 * * 1', -- Her Pazartesi sabah 9'da
'30 14 15 * *', -- Her ayın 15'inde 14:30'da
'0 0 1 1 *', -- Her yılın ilk günü gece yarısı
'0 12 * * 1-5', -- Hafta içi her gün öğlen 12'de
'0 22 * * 0', -- Her Pazar 22:00'de
'1,16,31,46 * * * *' -- Karmaşık liste
];
exp TEXT;
base_ts TIMESTAMPTZ := now();
iterations INT := 1000;
single_exp TEXT := '30 9 * * 1-5';
BEGIN
RAISE NOTICE '--- Cron Expression Benchmark Testi Başlatılıyor ---';
RAISE NOTICE 'Mevcut zaman: %', base_ts;
RAISE NOTICE '----------------------------------------------------';
-- ADIM 1: "SOĞUK ÖNBELLEK" TESTİ (CACHE BOŞKEN)
RAISE NOTICE '';
RAISE NOTICE '>>> ADIM 1: Soğuk Önbellek Testi (İlk Çalıştırma)';
RAISE NOTICE 'Önbellek tabloları temizleniyor...';
TRUNCATE cronexp.expressions, cronexp.parsed_patterns;
RAISE NOTICE 'İlk çalıştırma için zaman ölçümü başlatılıyor...';
start_ts := clock_timestamp();
FOREACH exp IN ARRAY expressions
LOOP
PERFORM cronexp.next_jump(exp, base_ts);
END LOOP;
end_ts := clock_timestamp();
duration := end_ts - start_ts;
RAISE NOTICE 'Soğuk Önbellek Testi Tamamlandı. Toplam Süre (% ifade için): %', array_length(expressions, 1), duration;
RAISE NOTICE '----------------------------------------------------';
-- ADIM 2: "SICAK ÖNBELLEK" TESTİ (CACHE DOLUYKEN)
RAISE NOTICE '';
RAISE NOTICE '>>> ADIM 2: Sıcak Önbellek Testi (İkinci Çalıştırma)';
RAISE NOTICE 'Önbellek dolu. Aynı ifadeler tekrar çalıştırılıyor...';
start_ts := clock_timestamp();
FOREACH exp IN ARRAY expressions
LOOP
PERFORM cronexp.next_jump(exp, base_ts);
END LOOP;
end_ts := clock_timestamp();
duration := end_ts - start_ts;
RAISE NOTICE 'Sıcak Önbellek Testi Tamamlandı. Toplam Süre (% ifade için): %', array_length(expressions, 1), duration;
RAISE NOTICE '>>> SONUÇ: Sıcak önbellek hızı, soğuk önbelleğe göre kat kat daha fazladır!';
RAISE NOTICE '----------------------------------------------------';
-- ADIM 3: YÜKSEK HACİMLİ STRES TESTİ
RAISE NOTICE '';
RAISE NOTICE '>>> ADIM 3: Yüksek Hacimli Tekrarlı Çağrı (Stres) Testi';
-- DÜZELTME: Bu bölüm, kendi başına bir BEGIN/END bloğu içine alındı.
-- BEGIN
-- DECLARE
-- iterations INT := 10000;
-- single_exp TEXT := '30 9 * * 1-5';
--
-- RAISE NOTICE '% defa aynı ifadenin çağrılması test ediliyor: "%"', iterations, single_exp;
--
-- start_ts := clock_timestamp();
-- FOR i IN 1..iterations LOOP
-- PERFORM cronexp.next_run(single_exp, base_ts);
-- END LOOP;
-- end_ts := clock_timestamp();
--
-- duration := end_ts - start_ts;
-- RAISE NOTICE 'Yüksek Hacim Testi Tamamlandı. Toplam Süre: %', duration;
-- RAISE NOTICE 'Çağrı başına ortalama süre: % mikrosaniye', (EXTRACT(EPOCH FROM duration) * 1000000 / iterations)::numeric(10, 2);
-- END;
RAISE NOTICE '% defa aynı ifadenin çağrılması test ediliyor: "%"', iterations, single_exp;
start_ts := clock_timestamp();
FOR i IN 1..iterations LOOP
PERFORM cronexp.next_jump(single_exp, base_ts);
END LOOP;
end_ts := clock_timestamp();
duration := end_ts - start_ts;
RAISE NOTICE 'Yüksek Hacim Testi Tamamlandı. Toplam Süre: %', duration;
RAISE NOTICE 'Çağrı başına ortalama süre: % mikrosaniye', (EXTRACT(EPOCH FROM duration) * 1000000 / iterations)::numeric(10, 2);
RAISE NOTICE '----------------------------------------------------';
RAISE NOTICE '--- Benchmark Testi Tamamlandı ---';
END;
$$ LANGUAGE 'plpgsql' VOLATILE; -- Fonksiyon TRUNCATE içerdiği ve VOLATILE fonksiyonlar çağırdığı için VOLATILE olmalı.
-- Run the tests
-- ======================================================================== --
-- YARDIMCI FONKSİYON: _find_next_val
-- AÇIKLAMA: Bir tamsayı dizisi içinde, verilen bir değerden büyük veya eşit
-- olan ilk değeri bulur. Bulamazsa, dizinin ilk elemanını döndürür.
-- `next_jump` tarafından kullanılır.
-- ======================================================================== --
CREATE OR REPLACE FUNCTION cronexp._find_next_val(vals int[], current_val int, OUT next_val int, OUT wrapped boolean)
AS $$
BEGIN
wrapped := false;
SELECT v INTO next_val FROM unnest(vals) v WHERE v >= current_val ORDER BY v LIMIT 1;
IF NOT FOUND THEN
wrapped := true;
next_val := vals[1];
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- ======================================================================== --
-- YARDIMCI FONKSİYON: _find_prev_val
-- AÇIKLAMA: Bir tamsayı dizisi içinde, verilen bir değerden küçük veya eşit
-- olan son değeri bulur. Bulamazsa, dizinin son elemanını döndürür.
-- `prev_jump` tarafından kullanılır.
-- ======================================================================== --
CREATE OR REPLACE FUNCTION cronexp._find_prev_val(vals int[], current_val int, OUT prev_val int, OUT wrapped boolean)
AS $$
BEGIN
wrapped := false;
SELECT v INTO prev_val FROM unnest(vals) v WHERE v <= current_val ORDER BY v DESC LIMIT 1;
IF NOT FOUND THEN
wrapped := true;
prev_val := vals[array_length(vals, 1)];
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- ======================================================================== --
-- FUNCTION: cronexp.next_jump(exp TEXT, from_ts TIMESTAMPTZ)
-- AÇIKLAMA: Dakika dakika aramak yerine, bir sonraki çalışma zamanına
-- doğrudan "zıplayan" ultra performanslı fonksiyon.
-- ======================================================================== --
-- ======================================================================== --
-- FUNCTION: cronexp.next_jump(exp TEXT, from_ts TIMESTAMPTZ)
-- DÜZELTME: date_part() sonuçları integer'a cast edildi.
-- ======================================================================== --
CREATE OR REPLACE FUNCTION cronexp.next_jump(exp TEXT, from_ts TIMESTAMPTZ)
RETURNS TIMESTAMPTZ AS $$
DECLARE
pattern cronexp.parsed_patterns;
ts TIMESTAMPTZ;
next_val INT;
wrapped BOOLEAN;
i INT := 0;
MAX_ITERATIONS CONSTANT INT := 1440;
BEGIN
SELECT * INTO pattern FROM cronexp.parsed_patterns WHERE expression = exp;
IF NOT FOUND THEN
PERFORM cronexp.match(from_ts, exp);
SELECT * INTO pattern FROM cronexp.parsed_patterns WHERE expression = exp;
END IF;
ts := date_trunc('minute', from_ts) + interval '1 minute';
LOOP
i := i + 1;
IF i > MAX_ITERATIONS THEN RAISE EXCEPTION 'Could not find next run time within reasonable iterations.'; END IF;
SELECT * INTO next_val, wrapped FROM cronexp._find_next_val(pattern.month_values, date_part('month', ts)::int);
IF next_val != date_part('month', ts)::int OR wrapped THEN
ts := date_trunc('year', ts) + (next_val - 1) * interval '1 month';
IF wrapped THEN ts := ts + interval '1 year'; END IF;
CONTINUE;
END IF;
LOOP
IF cronexp.match(date_trunc('day', ts), exp) THEN EXIT; END IF;
ts := date_trunc('day', ts) + interval '1 day';
IF date_part('month', ts)::int != next_val THEN CONTINUE ; END IF;
END LOOP;
SELECT * INTO next_val, wrapped FROM cronexp._find_next_val(pattern.hour_values, date_part('hour', ts)::int);
IF next_val != date_part('hour', ts)::int OR wrapped THEN
ts := date_trunc('day', ts) + next_val * interval '1 hour';
IF wrapped THEN ts := ts + interval '1 day'; END IF;
CONTINUE;
END IF;
SELECT * INTO next_val, wrapped FROM cronexp._find_next_val(pattern.minute_values, date_part('minute', ts)::int);
IF next_val != date_part('minute', ts)::int OR wrapped THEN
ts := date_trunc('hour', ts) + next_val * interval '1 minute';
IF wrapped THEN ts := ts + interval '1 hour'; END IF;
CONTINUE;
END IF;
RETURN ts;
END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;
-- ======================================================================== --
-- FUNCTION: cronexp.prev_jump(exp TEXT, from_ts TIMESTAMPTZ)
-- DÜZELTME: date_part() sonuçları integer'a cast edildi ve mantık iyileştirildi.
-- ======================================================================== --
CREATE OR REPLACE FUNCTION cronexp.prev_jump(exp TEXT, from_ts TIMESTAMPTZ)
RETURNS TIMESTAMPTZ AS $$
DECLARE
pattern cronexp.parsed_patterns;
ts TIMESTAMPTZ;
prev_val INT;
wrapped BOOLEAN;
i INT := 0;
MAX_ITERATIONS CONSTANT INT := 1440;
BEGIN
SELECT * INTO pattern FROM cronexp.parsed_patterns WHERE expression = exp;
IF NOT FOUND THEN
PERFORM cronexp.match(from_ts, exp);
SELECT * INTO pattern FROM cronexp.parsed_patterns WHERE expression = exp;
END IF;
ts := date_trunc('minute', from_ts) - interval '1 minute';
LOOP
i := i + 1;
IF i > MAX_ITERATIONS THEN RAISE EXCEPTION 'Could not find previous run time within reasonable iterations.'; END IF;
SELECT * INTO prev_val, wrapped FROM cronexp._find_prev_val(pattern.month_values, date_part('month', ts)::int);
IF prev_val != date_part('month', ts)::int OR wrapped THEN
ts := date_trunc('year', ts) + (prev_val - 1) * interval '1 month';
IF wrapped THEN ts := ts - interval '1 year'; END IF;
ts := ts + interval '1 month' - interval '1 day'; -- Ay sonuna git
ts := date_trunc('day', ts) + interval '23 hours 59 minutes';
CONTINUE;
END IF;
LOOP
IF cronexp.match(date_trunc('day', ts), exp) THEN EXIT; END IF;
ts := date_trunc('day', ts) - interval '1 minute';
IF date_part('month', ts)::int != prev_val THEN CONTINUE ; END IF;
END LOOP;
SELECT * INTO prev_val, wrapped FROM cronexp._find_prev_val(pattern.hour_values, date_part('hour', ts)::int);
IF prev_val != date_part('hour', ts)::int OR wrapped THEN
ts := date_trunc('day', ts) + prev_val * interval '1 hour';
IF wrapped THEN
ts := date_trunc('day', ts) - interval '1 minute';
ELSE
ts := ts + interval '59 minutes';
END IF;
CONTINUE;
END IF;
SELECT * INTO prev_val, wrapped FROM cronexp._find_prev_val(pattern.minute_values, date_part('minute', ts)::int);
IF prev_val != date_part('minute', ts)::int OR wrapped THEN
ts := date_trunc('hour', ts) + prev_val * interval '1 minute';
IF wrapped THEN ts := date_trunc('hour', ts) - interval '1 minute'; END IF;
CONTINUE;
END IF;
RETURN ts;
END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;
-- ======================================================================== --
-- KOMUTA MERKEZİ: JOBS TABLOSU --
-- ======================================================================== --
CREATE TABLE cronexp.jobs (
job_id BIGSERIAL PRIMARY KEY,
job_name TEXT UNIQUE NOT NULL,
cron_expression TEXT NOT NULL,
command TEXT NOT NULL, -- Çalıştırılacak SQL komutu
is_active BOOLEAN NOT NULL DEFAULT TRUE,
last_run_started_at TIMESTAMPTZ,
last_run_finished_at TIMESTAMPTZ,
next_run_at TIMESTAMPTZ NOT NULL -- Bu işin bir sonraki çalışma zamanı (çok önemli!)
);
COMMENT ON COLUMN cronexp.jobs.next_run_at IS 'Bu sütuna index eklemek, motor fonksiyonunun performansını kritik derecede artırır.';
CREATE INDEX ON cronexp.jobs (is_active, next_run_at);
-- ======================================================================== --
-- KARA KUTU: JOB LOGS TABLOSU --
-- ======================================================================== --
CREATE TABLE cronexp.job_logs (
log_id BIGSERIAL PRIMARY KEY,
job_id BIGINT NOT NULL REFERENCES cronexp.jobs(job_id) ON DELETE CASCADE,
run_started_at TIMESTAMPTZ NOT NULL,
run_finished_at TIMESTAMPTZ,
status TEXT NOT NULL, -- Örn: 'success', 'failure'
output_message TEXT -- Hata mesajları veya diğer çıktılar için
);
-- ======================================================================== --
-- MOTOR ODASI: EXECUTOR FONKSİYONU --
-- ======================================================================== --
CREATE OR REPLACE FUNCTION cronexp.run_due_jobs()
RETURNS VOID AS $$
DECLARE
due_job RECORD;
new_next_run_at TIMESTAMPTZ;
BEGIN
-- Yüksek eşzamanlılıkta bile güvenli çalışmak için "SKIP LOCKED" kullanıyoruz.
-- Bu sayede, aynı anda çalışan iki executor fonksiyonu, aynı işi kapmaya çalışmaz.
FOR due_job IN
SELECT * FROM cronexp.jobs
WHERE is_active = TRUE AND next_run_at <= now()
FOR UPDATE SKIP LOCKED
LOOP
-- İşi çalışıyor olarak işaretle ve bir sonraki çalıştırmayı geçici olarak null yap
UPDATE cronexp.jobs SET last_run_started_at = now(), next_run_at = NULL WHERE job_id = due_job.job_id;
BEGIN
-- Komutu çalıştır
EXECUTE due_job.command;
-- Başarı günlüğü
INSERT INTO cronexp.job_logs (job_id, run_started_at, run_finished_at, status, output_message)
VALUES (due_job.job_id, due_job.last_run_started_at, now(), 'success', 'Job completed successfully.');
EXCEPTION
WHEN OTHERS THEN
-- Hata günlüğü
INSERT INTO cronexp.job_logs (job_id, run_started_at, run_finished_at, status, output_message)
VALUES (due_job.job_id, due_job.last_run_started_at, now(), 'failure', SQLSTATE || ': ' || SQLERRM);
END;
-- İşin bir sonraki çalışma zamanını hesapla
new_next_run_at := cronexp.next_jump(due_job.cron_expression, now());
-- İşi bitir ve bir sonraki çalıştırmayı ayarla
UPDATE cronexp.jobs SET last_run_finished_at = now(), next_run_at = new_next_run_at WHERE job_id = due_job.job_id;
END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;
INSERT INTO cronexp.jobs (job_name, cron_expression, command, next_run_at)
VALUES (
'log_inserter_10min',
'*/10 * * * *',
$$INSERT INTO cronexp.job_logs (job_id, run_started_at, status, output_message) VALUES (-1, now(), 'info', 'Periodic logger job ran.');$$,
cronexp.next_jump('*/10 * * * *', now())
);
SELECT cronexp.run_due_jobs();
select cronexp.tests();
select cronexp.benchmark();
SELECT cronexp.expand_field('5', 0, 59);
EXPLAIN (ANALYZE, VERBOSE, COSTS, BUFFERS, TIMING, SUMMARY)
select cronexp.next_jump('* * * * 0,2-3,5-6', now() ) from generate_series(0,100000) s;
explain analyze
select cronexp.next_run('* * * * 0,2-3,5-6', now())from generate_series(0,1000) s;
DO $$
DECLARE
start_ts TIMESTAMPTZ;
BEGIN
RAISE NOTICE '--- ALGORİTMA KARŞILAŞTIRMA TESTİ ---';
RAISE NOTICE 'Hedef: Gelecek yılın ilk günü (0 0 1 1 *)';
RAISE NOTICE 'Başlangıç Zamanı: %', now();
RAISE NOTICE '------------------------------------------------';
-- YAVAŞ YÖNTEM (Dakika Dakika Arama)
RAISE NOTICE '1. next_run (Yürüyerek) ile test ediliyor... (Bu işlem BİRKAÇ SANİYE sürebilir)';
start_ts := clock_timestamp();
PERFORM cronexp.next_run('0 0 1 1 *', now());
RAISE NOTICE ' -> next_run Süresi: %', clock_timestamp() - start_ts;
RAISE NOTICE '------------------------------------------------';
-- HIZLI YÖNTEM (Akıllı Zıplama)
RAISE NOTICE '2. next_jump (Uçakla) ile test ediliyor...';
start_ts := clock_timestamp();
PERFORM cronexp.next_jump('0 0 1 1 *', now());
RAISE NOTICE ' -> next_jump Süresi: %', clock_timestamp() - start_ts;
RAISE NOTICE '------------------------------------------------';
RAISE NOTICE 'SONUÇ: "jump" fonksiyonu, uzak hedefler için katlanarak artan bir hız avantajı sağlar!';
END;
$$;
CREATE INDEX ON cronexp.jobs (is_active, next_run_at);
-- Belirli bir işin loglarını en yeniden eskiye doğru hızla getirmek için.
CREATE INDEX ON cronexp.job_logs (job_id, run_started_at DESC);
-- Sadece 'failure' durumundaki az sayıdaki kaydı çok hızlı bulmak için (Partial Index).
CREATE INDEX ON cronexp.job_logs (status) WHERE status = 'failure';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment