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.