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

Reply via email to