PostgreSQL Sleep Function pg_sleep – Postgres Delay Execution
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.
Related posts:
- PostgreSQL: Select a Random Number in a Range (Between Two Numbers)
- PHP mb_ucfirst Make a String’s First Character Uppercase-Multibyte (UTF-8) Function
- PHP: Calculate Real Differences Between Two Dates or Timestamps
- Postgresql (psql) .psqlrc tips and tricks
- Create PostgreSQL Table Partitioning (Part 1)
