Sleeping with Postgres
Do you want to sleep with PostgreSQL or maybe just have your session sleep for a bit? Well, we have an answer for you, the pg_sleep() function. It’s a pretty simple function and does what you would expect and sleeps for a specified period of time.
SELECT pg_sleep(2);
+----------+
| pg_sleep |
+----------+
| |
+----------+
(1 row)
Time: 2009.332 ms (00:02.009)
As you can see the SQL takes approximately 2 seconds to return. You can also you fractional seconds if needed. The pg_sleep() function also has a few variants including pg_sleep_for() and pg_sleep_until().
pg_sleep_for() is simply a function that utilizes a plain text timeframe. For example, if you wanted to sleep for 2 seconds like the example about you could execute something like this.
SELECT pg_sleep_for('2 seconds');
+--------------+
| pg_sleep_for |
+--------------+
| |
+--------------+
(1 row)
Time: 2004.185 ms (00:02.004)
You can pass any string that works are an interval. For example, if you wanted to sleep for 1 minute this would work.
SELECT pg_sleep_for('1 minute');
+--------------+
| pg_sleep_for |
+--------------+
| |
+--------------+
(1 row)
Time: 60061.970 ms (01:00.062)
pg_sleep_until() works by sleeping until a specific time. For example at the time of this post, I want PostgreSQL to sleep until 4:08PM today so this statement will accomplish just that.
SELECT pg_sleep_until('2020-01-17 16:08');
+----------------+
| pg_sleep_until |
+----------------+
| |
+----------------+
(1 row)
Time: 51018.980 ms (00:51.019)
Keep in mind while you might have a good use case for sleeping in the database it’s generally not a good idea to sleep for too long. While your session is sleeping you are keeping a transaction open. Too many of these transactions or long-running transactions will upset your local DBA. Use the PostgreSQL sleep command sparingly.