From 760c770ff66b5c2f77f2e17750a2e263a74d82b3 Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Thu, 30 Jan 2014 15:45:14 -0500 Subject: [PATCH] Add convenience functions pg_sleep_for and pg_sleep_until. Vik Fearing, reviewed by Pavel Stehule and myself --- doc/src/sgml/func.sgml | 18 ++++++++++++++---- src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_proc.h | 4 ++++ src/test/regress/expected/stats.out | 6 +++--- src/test/regress/sql/stats.sql | 2 +- 5 files changed, 23 insertions(+), 9 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 9816163354..3802fbcbb0 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -7664,20 +7664,28 @@ SELECT TIMESTAMP 'now'; -- incorrect for use with DEFAULT - The following function is available to delay execution of the server + The following functions are available to delay execution of the server process: pg_sleep(seconds) +pg_sleep_for(interval) +pg_sleep_until(timestamp with time zone) 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_for is a convenience function for larger + sleep times specified as an interval. + pg_sleep_until is a convenience function for when + a specific wake-up time is desired. For example: SELECT pg_sleep(1.5); +SELECT pg_sleep_for('5 minutes'); +SELECT pg_sleep_until('tomorrow 03:00'); @@ -7686,15 +7694,17 @@ SELECT pg_sleep(1.5); 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 might be longer depending on factors such as server load. + In particular, pg_sleep_until is not guaranteed to + wake up exactly at the specified time, but it will not wake up any earlier. 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. + when calling pg_sleep or its variants. Otherwise + other sessions might have to wait for your sleeping process, slowing down + the entire system. diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 404787c90c..c4661a8a66 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201401291 +#define CATALOG_VERSION_NO 201401301 #endif diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 9fc61ebed6..1682fa9f99 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -3034,6 +3034,10 @@ DATA(insert OID = 2625 ( pg_ls_dir PGNSP PGUID 12 1 1000 0 0 f f f f t t v 1 0 DESCR("list all files in a directory"); DATA(insert OID = 2626 ( pg_sleep PGNSP PGUID 12 1 0 0 0 f f f f t f v 1 0 2278 "701" _null_ _null_ _null_ _null_ pg_sleep _null_ _null_ _null_ )); DESCR("sleep for the specified time in seconds"); +DATA(insert OID = 3935 ( pg_sleep_for PGNSP PGUID 14 1 0 0 0 f f f f t f v 1 0 2278 "1186" _null_ _null_ _null_ _null_ "select pg_catalog.pg_sleep(extract(epoch from pg_catalog.now() operator(pg_catalog.+) $1) operator(pg_catalog.-) extract(epoch from pg_catalog.now()))" _null_ _null_ _null_ )); +DESCR("sleep for the specified interval"); +DATA(insert OID = 3936 ( pg_sleep_until PGNSP PGUID 14 1 0 0 0 f f f f t f v 1 0 2278 "1184" _null_ _null_ _null_ _null_ "select pg_catalog.pg_sleep(extract(epoch from $1) operator(pg_catalog.-) extract(epoch from pg_catalog.now()))" _null_ _null_ _null_ )); +DESCR("sleep until the specified time"); DATA(insert OID = 2971 ( text PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 25 "16" _null_ _null_ _null_ _null_ booltext _null_ _null_ _null_ )); DESCR("convert boolean to text"); diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index 56bace1187..ec0ff6589b 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -18,9 +18,9 @@ SET enable_indexscan TO on; SET enable_indexonlyscan TO off; -- wait to let any prior tests finish dumping out stats; -- else our messages might get lost due to contention -SELECT pg_sleep(2.0); - pg_sleep ----------- +SELECT pg_sleep_for('2 seconds'); + pg_sleep_for +-------------- (1 row) diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index bb349b2dfa..646b9ac686 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -16,7 +16,7 @@ SET enable_indexonlyscan TO off; -- wait to let any prior tests finish dumping out stats; -- else our messages might get lost due to contention -SELECT pg_sleep(2.0); +SELECT pg_sleep_for('2 seconds'); -- save counters CREATE TEMP TABLE prevstats AS -- 2.40.0