SELECT pg_sleep_until('#800Monies');

Pavlo recently pointed out that the pg_sleep() function in PostgreSQL has two alternatives: pg_sleep_for() and pg_sleep_until().

What do they do?

pg_sleep() itself is used to stop the execution (as example of a file with SQL commands) for the specified amount of seconds. The number of seconds can be specified as fraction.

1
2
3
4
5
6
7
8
9

postgres=# \timing
Timing is on.
postgres=# SELECT pg_sleep(2.5);
 pg_sleep
----------

(1 row)

Time: 2508,399 ms (00:02,508)

Works quite well, the execution time is almost exactly 2.5 seconds.

That is useful for short delays. But what when the waiting time is supposed to be longer? Or the execution shall wait until a specific time? Using pg_sleep() only, one has to calculate the number of seconds. pg_sleep_for() uses an interval, and the time can be specified in a more human readable form.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

postgres=# SELECT pg_sleep_for('2 minutes 17 seconds');
 pg_sleep_for
--------------

(1 row)

Time: 137112,465 ms (02:17,112)
postgres=# SELECT 2*60 + 17;
 ?column?
----------
      137
(1 row)

Time: 8,184 ms

And then there is pg_sleep_until(), which uses a timestamp and waits until that time.

1
2
3
4
5
6
7

postgres=# SELECT pg_sleep_until('2020-01-17 00:58:00');
 pg_sleep_until
----------------

(1 row)

Time: 27683,361 ms (00:27,683)

In this example I just picked the next full minute at the time when I was writing this blog post.

All in all very useful.

But Vik has to fix one last remaining bug, which he quoted here:

1
2
3
4
5

postgres=# SELECT pg_sleep_until('#800Monies');
ERROR:  invalid input syntax for type timestamp with time zone: "#800Monies"
LINE 1: SELECT pg_sleep_until('#800Monies');
                              ^
Time: 8,499 ms