I propose the appended patch for the todo item:
* Add sleep() function, remove from regress.c
The patch also removes do_sleep() from the regression and substitues this
with the call to the new function.
I'd personally prefer to call the function pg_sleep(), but since it is
called sleep() on the TODO list and in previous discussions, I kept the
name. The internal function is called pg_sleep() however.
There was a discussion about this item in August of last year
http://archives.postgresql.org/pgsql-hackers/2005-08/msg00633.php
A few people liked the idea, Tom Lane pointed out that it's bad to have a
sleeping backend that can hold locks for a long time. Other developers
answered that this should just be documented since the user can do that
anyways very easily if he is allowed to create functions in one of the
traditional scripting languages or with busy waiting also in plpgsql.
Finally the item got added to the TODO list and nobody objected.
In the mysqlcompat project there is a sleep function that does busy waiting,
this function could profit from a sleep() function in the backend.
To the docs I introduced a new paragraph such that it is easy to find this
function and labeled the note about the locks with the "warning" tag.
Tom, can you live with that?
Other Comments?
Joachim
diff -cr cvs/pgsql/doc/src/sgml/func.sgml cvs.build/pgsql/doc/src/sgml/func.sgml
*** cvs/pgsql/doc/src/sgml/func.sgml 2005-12-28 02:29:58.000000000 +0100
--- cvs.build/pgsql/doc/src/sgml/func.sgml 2006-01-10 00:40:05.000000000
+0100
***************
*** 6170,6175 ****
--- 6170,6223 ----
</para>
</tip>
</sect2>
+
+ <sect2 id="functions-datetime-delay">
+ <title>Delaying the execution with <function>sleep</function></title>
+
+ <indexterm>
+ <primary>sleep</primary>
+ </indexterm>
+ <indexterm>
+ <primary>delay</primary>
+ </indexterm>
+
+ <para>
+ The following function is available to delay the execution of the
+ backend:
+ <synopsis>
+ sleep (<replaceable>seconds</replaceable>)
+ </synopsis>
+
+ <function>sleep(<replaceable>seconds</replaceable>)</function> makes the
+ current backend sleep until <replaceable>seconds</replaceable> seconds
have
+ elapsed. Note that the argument <replaceable>seconds</replaceable> is
given
+ in the <type>numeric</type> type. Thus you can also sleep for fractions of
+ seconds.
+
+ <programlisting>
+ SELECT sleep(0.5);
+ </programlisting>
+ </para>
+
+ <note>
+ <para>
+ The exact time that the process sleeps depends on the operating system.
+ On a busy system the backend process might get suspended for a longer
+ time than specified, especially if the priority of the backend process
is
+ low compared to other processes.
+ </para>
+ </note>
+ <warning>
+ <para>
+ Make sure that your backend does not hold more locks than necessary
+ before calling
+ <function>sleep(<replaceable>seconds</replaceable>)</function>, since
+ this could slow down your whole system. Other backends might have to
wait
+ for locks your sleeping backend still holds.
+ </para>
+ </warning>
+ </sect2>
+
</sect1>
diff -cr cvs/pgsql/src/backend/utils/adt/misc.c
cvs.build/pgsql/src/backend/utils/adt/misc.c
*** cvs/pgsql/src/backend/utils/adt/misc.c 2005-10-15 04:49:29.000000000
+0200
--- cvs.build/pgsql/src/backend/utils/adt/misc.c 2006-01-10
00:34:36.000000000 +0100
***************
*** 28,33 ****
--- 28,34 ----
#include "storage/pmsignal.h"
#include "storage/procarray.h"
#include "utils/builtins.h"
+ #include "utils/numeric.h"
#define atooid(x) ((Oid) strtoul((x), NULL, 10))
***************
*** 259,261 ****
--- 260,315 ----
FreeDir(fctx->dirdesc);
SRF_RETURN_DONE(funcctx);
}
+
+ /*
+ * pg_sleep - delay for N seconds (N is numeric)
+ *
+ * This function gets exposed to the user as "sleep()"
+ *
+ * Note that pg_usleep() will abort when receiving a SIGHUP for example since
+ * it is implemented by means of select().
+ */
+ Datum
+ pg_sleep(PG_FUNCTION_ARGS)
+ {
+ Numeric secs;
+ Numeric usecs;
+ int64 to_sleep;
+
+ if (PG_ARGISNULL(0))
+ /* have to return NULL here to comply with the strictness
property */
+ PG_RETURN_NULL();
+
+ secs = PG_GETARG_NUMERIC(0);
+
+ /* if we should sleep for example 2.5 seconds, we first calculate:
+ * 2.5 * 1,000,000 = 2,500,000
+ * Then we calculate the ceiling of this and transform it to an int64
for
+ * easier calculations.
+ */
+ usecs = DatumGetNumeric(DirectFunctionCall1(int8_numeric,
+
Int64GetDatum(INT64CONST(1000000))));
+ usecs = DatumGetNumeric(DirectFunctionCall2(numeric_mul,
+
NumericGetDatum(secs),
+
NumericGetDatum(usecs)));
+ usecs = DatumGetNumeric(DirectFunctionCall1(numeric_ceil,
+
NumericGetDatum(usecs)));
+ to_sleep = DatumGetInt64(DirectFunctionCall1(numeric_int8,
+
NumericGetDatum(usecs)));
+
+ /* some sanity checks */
+ if (to_sleep < (int64) 0)
+ PG_RETURN_VOID();
+
+ if (to_sleep > (int64) LONG_MAX)
+ {
+ elog(WARNING, "Maximal delay for sleep() are %ld seconds on
this machine. Will sleep for this time",
+ (LONG_MAX / 1000000L) - 1);
+ to_sleep = (int64) LONG_MAX;
+ }
+
+ pg_usleep((long) to_sleep);
+
+ PG_RETURN_VOID();
+ }
+
diff -cr cvs/pgsql/src/include/catalog/pg_proc.h
cvs.build/pgsql/src/include/catalog/pg_proc.h
*** cvs/pgsql/src/include/catalog/pg_proc.h 2006-01-08 08:00:25.000000000
+0100
--- cvs.build/pgsql/src/include/catalog/pg_proc.h 2006-01-10
00:34:36.000000000 +0100
***************
*** 3025,3030 ****
--- 3025,3033 ----
DESCR("Read text from a file");
DATA(insert OID = 2625 ( pg_ls_dir PGNSP PGUID 12 f f t t
v 1 25 "25" _null_ _null_ _null_ pg_ls_dir - _null_ ));
DESCR("List all files in a directory");
+ DATA(insert OID = 2626 ( sleep PGNSP PGUID 12
f f t f v 1 2278 "1700" _null_ _null_ _null_ pg_sleep - _null_ ));
+ DESCR("Sleeps for the specified time of seconds");
+
/* Aggregates (moved here from pg_aggregate for 7.3) */
diff -cr cvs/pgsql/src/include/utils/builtins.h
cvs.build/pgsql/src/include/utils/builtins.h
*** cvs/pgsql/src/include/utils/builtins.h 2006-01-08 08:00:26.000000000
+0100
--- cvs.build/pgsql/src/include/utils/builtins.h 2006-01-10
00:34:36.000000000 +0100
***************
*** 387,392 ****
--- 387,393 ----
extern Datum pg_reload_conf(PG_FUNCTION_ARGS);
extern Datum pg_tablespace_databases(PG_FUNCTION_ARGS);
extern Datum pg_rotate_logfile(PG_FUNCTION_ARGS);
+ extern Datum pg_sleep(PG_FUNCTION_ARGS);
/* not_in.c */
extern Datum int4notin(PG_FUNCTION_ARGS);
diff -cr cvs/pgsql/src/test/regress/expected/stats.out
cvs.build/pgsql/src/test/regress/expected/stats.out
*** cvs/pgsql/src/test/regress/expected/stats.out 2005-10-06
04:29:22.000000000 +0200
--- cvs.build/pgsql/src/test/regress/expected/stats.out 2006-01-10
00:34:36.000000000 +0100
***************
*** 36,44 ****
(1 row)
-- let stats collector catch up
! SELECT do_sleep(2);
! do_sleep
! ----------
(1 row)
--- 36,44 ----
(1 row)
-- let stats collector catch up
! SELECT sleep(2.0);
! sleep
! -------
(1 row)
diff -cr cvs/pgsql/src/test/regress/expected/time.out
cvs.build/pgsql/src/test/regress/expected/time.out
*** cvs/pgsql/src/test/regress/expected/time.out 2003-11-01
04:07:07.000000000 +0100
--- cvs.build/pgsql/src/test/regress/expected/time.out 2006-01-10
00:34:36.000000000 +0100
***************
*** 71,73 ****
--- 71,100 ----
SELECT f1 + time '00:01' AS "Illegal" FROM TIME_TBL;
ERROR: operator is not unique: time without time zone + time without time
zone
HINT: Could not choose a best candidate operator. You may need to add
explicit type casts.
+ -- Test sleep():
+ -- the problem with this function is that in theory the delay can be more than
+ -- 1 second if the system is busy and/or we run with a low priority... I think
+ -- that won't happen but to be sure just check if we have delayed for more
than
+ -- 1 s after calling sleep(1.00)
+ CREATE OR REPLACE FUNCTION tmp_sleep_test() RETURNS int AS $$
+ DECLARE
+ starttime timestamptz;
+ BEGIN
+ starttime = timeofday()::timestamptz;
+ PERFORM sleep(0);
+ RAISE NOTICE '% seconds delayed (sleep(0))',
+ EXTRACT(SECONDS FROM date_trunc('second',
timeofday()::timestamptz - starttime));
+ PERFORM sleep(1.00);
+ RAISE NOTICE 'seconds delayed (sleep(1.00)) more than 1: %',
+ EXTRACT(SECONDS FROM date_trunc('second',
timeofday()::timestamptz - starttime)) >= 1;
+ RETURN 1;
+ END;
+ $$ LANGUAGE plpgsql;
+ SELECT tmp_sleep_test();
+ NOTICE: 0 seconds delayed (sleep(0))
+ NOTICE: seconds delayed (sleep(1.00)) more than 1: t
+ tmp_sleep_test
+ ----------------
+ 1
+ (1 row)
+
diff -cr cvs/pgsql/src/test/regress/input/create_function_1.source
cvs.build/pgsql/src/test/regress/input/create_function_1.source
*** cvs/pgsql/src/test/regress/input/create_function_1.source 2005-07-23
16:18:56.000000000 +0200
--- cvs.build/pgsql/src/test/regress/input/create_function_1.source
2006-01-10 00:34:36.000000000 +0100
***************
*** 52,62 ****
AS '@abs_builddir@/[EMAIL PROTECTED]@'
LANGUAGE 'C' STRICT;
- CREATE FUNCTION do_sleep (int4)
- RETURNS void
- AS '@abs_builddir@/[EMAIL PROTECTED]@'
- LANGUAGE 'C' STRICT;
-
-- Things that shouldn't work:
CREATE FUNCTION test1 (int) RETURNS int LANGUAGE sql
--- 52,57 ----
diff -cr cvs/pgsql/src/test/regress/output/create_function_1.source
cvs.build/pgsql/src/test/regress/output/create_function_1.source
*** cvs/pgsql/src/test/regress/output/create_function_1.source 2005-07-23
16:18:56.000000000 +0200
--- cvs.build/pgsql/src/test/regress/output/create_function_1.source
2006-01-10 00:34:36.000000000 +0100
***************
*** 47,56 ****
RETURNS int4
AS '@abs_builddir@/[EMAIL PROTECTED]@'
LANGUAGE 'C' STRICT;
- CREATE FUNCTION do_sleep (int4)
- RETURNS void
- AS '@abs_builddir@/[EMAIL PROTECTED]@'
- LANGUAGE 'C' STRICT;
-- Things that shouldn't work:
CREATE FUNCTION test1 (int) RETURNS int LANGUAGE sql
AS 'SELECT ''not an integer'';';
--- 47,52 ----
diff -cr cvs/pgsql/src/test/regress/regress.c
cvs.build/pgsql/src/test/regress/regress.c
*** cvs/pgsql/src/test/regress/regress.c 2005-10-15 04:49:51.000000000
+0200
--- cvs.build/pgsql/src/test/regress/regress.c 2006-01-10 00:34:36.000000000
+0100
***************
*** 26,32 ****
extern int oldstyle_length(int n, text *t);
extern Datum int44in(PG_FUNCTION_ARGS);
extern Datum int44out(PG_FUNCTION_ARGS);
- extern Datum do_sleep(PG_FUNCTION_ARGS);
/*
--- 26,31 ----
***************
*** 736,752 ****
PG_RETURN_CSTRING(result);
}
- /*
- * do_sleep - delay for N seconds
- */
- PG_FUNCTION_INFO_V1(do_sleep);
-
- Datum
- do_sleep(PG_FUNCTION_ARGS)
- {
- int32 secs = PG_GETARG_INT32(0);
-
- pg_usleep(secs * 1000000L);
-
- PG_RETURN_VOID();
- }
--- 735,737 ----
diff -cr cvs/pgsql/src/test/regress/sql/stats.sql
cvs.build/pgsql/src/test/regress/sql/stats.sql
*** cvs/pgsql/src/test/regress/sql/stats.sql 2005-10-06 04:29:23.000000000
+0200
--- cvs.build/pgsql/src/test/regress/sql/stats.sql 2006-01-10
00:34:36.000000000 +0100
***************
*** 26,32 ****
SELECT count(*) FROM tenk2 WHERE unique1 = 1;
-- let stats collector catch up
! SELECT do_sleep(2);
-- check effects
SELECT st.seq_scan >= pr.seq_scan + 1,
--- 26,32 ----
SELECT count(*) FROM tenk2 WHERE unique1 = 1;
-- let stats collector catch up
! SELECT sleep(2.0);
-- check effects
SELECT st.seq_scan >= pr.seq_scan + 1,
diff -cr cvs/pgsql/src/test/regress/sql/time.sql
cvs.build/pgsql/src/test/regress/sql/time.sql
*** cvs/pgsql/src/test/regress/sql/time.sql 2003-01-31 02:08:08.000000000
+0100
--- cvs.build/pgsql/src/test/regress/sql/time.sql 2006-01-10
00:34:36.000000000 +0100
***************
*** 34,36 ****
--- 34,62 ----
-- where we do mixed-type arithmetic. - thomas 2000-12-02
SELECT f1 + time '00:01' AS "Illegal" FROM TIME_TBL;
+
+
+
+ -- Test sleep():
+ -- the problem with this function is that in theory the delay can be more than
+ -- 1 second if the system is busy and/or we run with a low priority... I think
+ -- that won't happen but to be sure just check if we have delayed for more
than
+ -- 1 s after calling sleep(1.00)
+
+ CREATE OR REPLACE FUNCTION tmp_sleep_test() RETURNS int AS $$
+ DECLARE
+ starttime timestamptz;
+ BEGIN
+ starttime = timeofday()::timestamptz;
+ PERFORM sleep(0);
+ RAISE NOTICE '% seconds delayed (sleep(0))',
+ EXTRACT(SECONDS FROM date_trunc('second',
timeofday()::timestamptz - starttime));
+ PERFORM sleep(1.00);
+ RAISE NOTICE 'seconds delayed (sleep(1.00)) more than 1: %',
+ EXTRACT(SECONDS FROM date_trunc('second',
timeofday()::timestamptz - starttime)) >= 1;
+ RETURN 1;
+ END;
+ $$ LANGUAGE plpgsql;
+
+ SELECT tmp_sleep_test();
+
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
