One day, I was looking for PostgreSQL database function, which could delay the execution of queries. At first I thought that I will do one myself, because I do not remember ever seen this kind function. But then I found pg_sleep Postgres function which directly delay execution of the server process.

pg_sleep makes the current session’s process sleep until seconds seconds have elapsed. Seconds is a value of type double precision, so fractional-second delays can be specified.

pg_sleep function usage is as easy as it should be:


SELECT pg_sleep(seconds);

A real example, which adds 60 seconds delay between two queries:


SELECT CURRENT_TIMESTAMP; SELECT pg_sleep(60); SELECT CURRENT_TIMESTAMP; now ------------------------------- 2010-04-12 11:07:47.844122+03
(1 row) Time: 87.179 ms pg_sleep ---------- (1 row) Time: 60049.804 ms now ------------------------------- 2010-04-12 11:08:47.894901+03
(1 row) Time: 0.812 ms

pg_sleep function usage on PL/pgSQL function

First create simple function:


CREATE OR REPLACE FUNCTION test_pg_sleep(INTEGER, INTEGER) RETURNS VOID AS $$
DECLARE loops ALIAS FOR $1; delay ALIAS FOR $2;
BEGIN FOR i IN 1..loops LOOP RAISE INFO 'Current timestamp: %', timeofday()::TIMESTAMP; RAISE INFO 'Sleep % seconds', delay; PERFORM pg_sleep(delay); END LOOP;
END;
$$ LANGUAGE 'plpgsql' STRICT;

Use just created function:


[testdb]> SELECT test_pg_sleep(10,10);
INFO: Current timestamp: 2010-04-13 08:22:07.570522
INFO: Sleep 10 seconds
INFO: Current timestamp: 2010-04-13 08:22:17.573849
INFO: Sleep 10 seconds
INFO: Current timestamp: 2010-04-13 08:22:27.593824
INFO: Sleep 10 seconds
INFO: Current timestamp: 2010-04-13 08:22:37.603829
INFO: Sleep 10 seconds
INFO: Current timestamp: 2010-04-13 08:22:47.613843
INFO: Sleep 10 seconds
INFO: Current timestamp: 2010-04-13 08:22:57.623824
INFO: Sleep 10 seconds
INFO: Current timestamp: 2010-04-13 08:23:07.633823
INFO: Sleep 10 seconds
INFO: Current timestamp: 2010-04-13 08:23:17.643825
INFO: Sleep 10 seconds
INFO: Current timestamp: 2010-04-13 08:23:27.653836
INFO: Sleep 10 seconds
INFO: Current timestamp: 2010-04-13 08:23:37.67383
INFO: Sleep 10 seconds test_pg_sleep --------------- (1 row) Time: 100126.203 ms

Note: The effective resolution of the sleep interval is platform-specific; 0.01 seconds is a common value. The sleep delay will be at least as long as specified. It may be longer depending on factors such as server load.

Warning: Make sure that your session does not hold more locks than necessary when calling pg_sleep. Otherwise other sessions might have to wait for your sleeping process, slowing down the entire system.

Similar Posts