5.9ms drift per second on job with interal of '1 second'

[kairoswater-jason]

I'm not sure if this is expected behavior or not.

Using pg_cron 1.6.4 (the version is installed by default in PostgreSQL 16.6 databases in AWS RDS). I'm not sure if it would apply to 1.6.5

When I schedule a job to occur at an interval of '1 second' the number of times it executes per second is slightly slower than one second. In the testcase below the actual interval averages out to 1.0059 seconds over a time span of 1000 seconds.

Information for reproduction

CREATE SCHEMA cron_test AUTHORIZATION jason;
CREATE SEQUENCE cron_test.table_seconds_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	START 1
	CACHE 1
	NO CYCLE;-- cron_test.table_seconds definition

CREATE TABLE cron_test.table_seconds (
	id bigserial NOT NULL,
	"time" timestamptz NOT NULL,
	CONSTRAINT table_seconds_pk PRIMARY KEY (id)
);

INSERT INTO cron.job
(jobid, schedule, command, nodename, nodeport, "database", username, active, jobname)
VALUES(1, '1 second', '
	INSERT INTO cron_test.table_seconds
	("time")
	VALUES(now());
', 'localhost', 5432, 'jason_test', 'jason', false, 'test-every-1-second');

--after letting run for a short while
SELECT EXTRACT(EPOCH FROM (t2.time - t1.time) ) AS time_delta 
FROM jason_test.cron_test.table_seconds AS t1, jason_test.cron_test.table_seconds AS t2 
WHERE t1.id = 2000 AND t2.id = 3000

-- Result: time_delta = 1005.902082

[marcoslot]

The current scheduling is not designed to be very precise, since we're dependent on things like system calls to sleep. Is the main concern the number of executions over a time period?

[kairoswater-jason]

Yes, I'd expect a "1 second" interval to execute 86400 +/- 1 times per day and maintain an average interval that is almost exactly 1 second over long periods of time.

This current implementation would execute 85893 times per day on average (a little more than 500 executions short of what might be expected)

It's not a terribly big concern. I just was surprised. I had wrongly expected it to be similar to normal linux cron scheduling where it periodically checks/schedules against an absolute time/deadline rather than just inserting a relative delay.