PostgreSQL includes three functions that allow you to delay the execution of the server process. the execution of a statement.
In other words, you can run a statement and have it pause half way through, before continuing on its merry way.
The three functions are:
These are all very similar, but they work in slightly different ways.
Below are examples of each one.
Example – pg_sleep()
The pg_sleep() function makes the current session’s process sleep until a specified number of seconds have passed.
You specify the seconds as an argument. The argument is a value of double precision, so you can provide fractional seconds if required.
\x
SELECT
clock_timestamp(),
pg_sleep(1),
clock_timestamp(),
pg_sleep(1),
clock_timestamp();Result (using vertical output):
clock_timestamp | 2020-06-28 16:18:25.645814+10 pg_sleep | clock_timestamp | 2020-06-28 16:18:26.706998+10 pg_sleep | clock_timestamp | 2020-06-28 16:18:27.768604+10
Example – pg_sleep_for()
The pg_sleep_for() function is a convenience function for larger sleep times specified as an interval.
SELECT
clock_timestamp(),
pg_sleep_for('2 minutes 10 seconds'),
clock_timestamp(),
pg_sleep_for('1 minute 5 seconds'),
clock_timestamp();Result (using vertical output):
clock_timestamp | 2020-06-28 16:20:04.218295+10 pg_sleep_for | clock_timestamp | 2020-06-28 16:22:14.222907+10 pg_sleep_for | clock_timestamp | 2020-06-28 16:23:19.294483+10
Example – pg_sleep_until()
The pg_sleep_until() function is a convenience function convenience function for when a specific wake-up time is desired.
SELECT
clock_timestamp(),
pg_sleep_until('today 16:25:15.1234'),
clock_timestamp();Result (using vertical output):
clock_timestamp | 2020-06-28 16:24:28.900319+10 pg_sleep_until | clock_timestamp | 2020-06-28 16:25:15.184042+10
Precision of the Delay
When using these functions, the actual time delay may vary from the time specified depending on the activity level of the server. However, the sleep delay will be at least as long as specified.
In particular, pg_sleep_until is not guaranteed to wake up exactly at the specified time, but it will not wake up any earlier.