On Mon, Jan 09, 2006 at 08:50:36PM -0500, Tom Lane wrote:
> pg_sleep seems like a better idea to me too.
ok, renamed again.
> Why is the function defined to take numeric rather than float8?
> float8 is a whole lot easier to work with internally.
ok, changed.
> The proposed regression test seems unacceptably fragile, as well as
> rather pointless.
Why is it fragile? Which other regression test do you suggest? Or should it
go without one?
On Mon, Jan 09, 2006 at 08:54:49PM -0500, Tom Lane wrote:
> BTW, a serious problem with just passing it off to pg_usleep like that
> is that the sleep can't be aborted by a cancel request
No, cancelling the sleep works (at least for Unix). Isn't cancelling
implemented via a signal that interrupts select() ?
Anyway, I've changed it, removing the ~2000s limit is a good point.
I append a new version with the regression test ripped out.
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 10:54:46.000000000
+0100
***************
*** 6170,6175 ****
--- 6170,6226 ----
</para>
</tip>
</sect2>
+
+ <sect2 id="functions-datetime-delay">
+ <title>Delaying the execution with <function>pg_sleep</function></title>
+
+ <indexterm>
+ <primary>pg_sleep</primary>
+ </indexterm>
+ <indexterm>
+ <primary>sleep</primary>
+ </indexterm>
+ <indexterm>
+ <primary>delay</primary>
+ </indexterm>
+
+ <para>
+ The following function is available to delay the execution of the
+ backend:
+ <synopsis>
+ pg_sleep (<replaceable>seconds</replaceable>)
+ </synopsis>
+
+ <function>pg_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 pg_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>pg_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
10:22:10.000000000 +0100
***************
*** 259,261 ****
--- 259,301 ----
FreeDir(fctx->dirdesc);
SRF_RETURN_DONE(funcctx);
}
+
+ /*
+ * pg_sleep - delay for N seconds
+ *
+ * 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)
+ {
+ float8 secs;
+ float8 usecs;
+ int32 to_sleep;
+
+ if (PG_ARGISNULL(0))
+ /* return NULL here to comply with the strictness property */
+ PG_RETURN_NULL();
+
+ secs = PG_GETARG_FLOAT8(0);
+
+ if (secs < 0.0)
+ PG_RETURN_VOID();
+
+ while (secs > 1.0)
+ {
+ pg_usleep(1000000);
+ CHECK_FOR_INTERRUPTS();
+ secs -= 1.0;
+ }
+
+ /* sleep for the remaining time, secs is between 0.0 and 1.0 now,
+ * ceil() guarantees that we sleep at least as long as requested */
+ usecs = ceil(secs * 1000000);
+ to_sleep = DatumGetInt32(DirectFunctionCall1(dtoi4,
+
Float8GetDatumFast(usecs)));
+ pg_usleep(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
10:22:10.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 ( pg_sleep PGNSP PGUID 12
f f t f v 1 2278 "701" _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
10:22:10.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/port/pgsleep.c cvs.build/pgsql/src/port/pgsleep.c
*** cvs/pgsql/src/port/pgsleep.c 2004-12-31 23:03:53.000000000 +0100
--- cvs.build/pgsql/src/port/pgsleep.c 2006-01-10 10:35:30.000000000 +0100
***************
*** 23,28 ****
--- 23,29 ----
* the requested delay to be rounded up to the next resolution boundary.
*
* On machines where "long" is 32 bits, the maximum delay is ~2000 seconds.
+ * (there is also the function pg_sleep() that can sleep for a longer time)
*/
#ifdef pg_usleep
#undef pg_usleep
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
10:22:10.000000000 +0100
***************
*** 36,43 ****
(1 row)
-- let stats collector catch up
! SELECT do_sleep(2);
! do_sleep
----------
(1 row)
--- 36,43 ----
(1 row)
-- let stats collector catch up
! SELECT pg_sleep(2.0);
! pg_sleep
----------
(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 10:22:10.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 10:22:10.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 10:22:10.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
10:22:10.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 pg_sleep(2.0);
-- check effects
SELECT st.seq_scan >= pr.seq_scan + 1,
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
