Expand documentation for sequence functions (nextval and friends).

Place it in the expected place in the User's Guide, rather than
hiding it in the command reference page for CREATE SEQUENCE.

1 files changed, 36 insertions, 60 deletions

diff --git a/doc/src/sgml/ref/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml
index 953ca916849..334d45dd8b9 100644
--- a/doc/src/sgml/ref/create_sequence.sgml
+++ b/doc/src/sgml/ref/create_sequence.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_sequence.sgml,v 1.20 2001/09/13 15:55:24 petere Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_sequence.sgml,v 1.21 2001/11/17 22:20:34 tgl Exp $
 Postgres documentation
 -->
 
@@ -15,7 +15,7 @@ Postgres documentation
    CREATE SEQUENCE
   </refname>
   <refpurpose>
-   define a new sequence
+   define a new sequence generator
   </refpurpose>
  </refnamediv> 
  <refsynopsisdiv>
@@ -42,8 +42,8 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">seqname</rep
       <term>TEMPORARY or TEMP</term>
       <listitem>
        <para>
-	If specified, the sequence is created only for this session, and is
-	automatically dropped on session exit.
+	If specified, the sequence object is created only for this session,
+	and is automatically dropped on session exit.
 	Existing permanent sequences with the same name are not visible
 	(in this session) while the temporary sequence exists.
        </para>
@@ -141,6 +141,8 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">seqname</rep
 	<replaceable class="parameter">minvalue</replaceable> or
 	<replaceable class="parameter">maxvalue</replaceable>,
 	respectively.
+	Without CYCLE, after the limit is reached <function>nextval</> calls
+	will return an error.
        </para>
       </listitem>
      </varlistentry>
@@ -222,81 +224,57 @@ ERROR:  DefineSequence: MINVALUE (<replaceable class="parameter">min</replaceabl
   </title>
   <para>
    <command>CREATE SEQUENCE</command> will enter a new sequence number generator
-   into the current data base. This involves creating and initializing a
+   into the current database. This involves creating and initializing a
    new single-row
    table with the name <replaceable class="parameter">seqname</replaceable>.
    The generator will be owned by the user issuing the command.
   </para>
 
   <para>
-   After a sequence is created, you may use the function
-   <function>nextval('<replaceable class="parameter">seqname</replaceable>')</function>
-   to get a new number from the sequence.
-   The function
-   <function>currval('<replaceable class="parameter">seqname</replaceable>')</function>
-   may be used to determine the number returned by the last call to
-   <function>nextval('<replaceable class="parameter">seqname</replaceable>')</function>
-   for the specified sequence in the current session.
-   The function
-   <function>setval('<replaceable class="parameter">seqname</replaceable>',
-    <replaceable class="parameter">newvalue</replaceable>)</function>
-   may be used to set the current value of the specified sequence.
-   The next call to 
-   <function>nextval('<replaceable class="parameter">seqname</replaceable>')</function>
-   will return the given value plus the sequence increment.
+   After a sequence is created, you use the functions
+   <function>nextval</function>,
+   <function>currval</function> and
+   <function>setval</function>
+   to operate on the sequence.  These functions are documented in
+   the <citetitle>User's Guide</citetitle>.
   </para>
 
   <para>
-   Use a query like
+   Although you cannot update a sequence directly, you can use a query like
 
    <programlisting>
 SELECT * FROM <replaceable>seqname</replaceable>;
    </programlisting>
 
-   to examine the parameters of a sequence.
-
-   As an alternative to fetching the
-   parameters from the original definition as above, you can use
-
-   <programlisting>
-SELECT last_value FROM <replaceable>seqname</replaceable>;
-   </programlisting>
-
-   to obtain the last value allocated by any backend.
-  </para>
-
-  <para>
-   To avoid blocking of concurrent transactions
-   that obtain numbers from the same sequence, a nextval operation
-   is never rolled back; that is, once a value has been fetched it is
-   considered used, even if the transaction that did the nextval later
-   aborts.  This means that aborted transactions may leave unused <quote>holes</quote>
-   in the sequence of assigned values.  setval operations are never
-   rolled back, either.
+   to examine the parameters and current state of a sequence.  In particular,
+   the <literal>last_value</> field of the sequence shows the last value
+   allocated by any backend process.  (Of course, this value may be obsolete
+   by the time it's printed, if other processes are actively doing
+   <function>nextval</> calls.)
   </para>
 
   <caution>
    <para>
-    Unexpected results may be obtained if a cache setting greater than one
+    Unexpected results may be obtained if a <replaceable class="parameter">cache</replaceable> setting greater than one
     is used for a sequence object that will be used concurrently by multiple
     backends.  Each backend will allocate and cache successive sequence values
     during one access to the sequence object and increase the sequence
-    object's last_value accordingly.  Then, the next cache-1 uses of nextval
+    object's <literal>last_value</> accordingly.  Then, the next <replaceable class="parameter">cache</replaceable>-1 uses of <function>nextval</>
     within that backend simply return the preallocated values without touching
-    the shared object.  So, numbers allocated but not used in the current session
-    will be lost.  Furthermore, although multiple backends are guaranteed to
+    the shared object.  So, any numbers allocated but not used within a session
+    will be lost when that session ends.  Furthermore, although multiple backends are guaranteed to
     allocate distinct sequence values, the values may be generated out of
-    sequence when all the backends are considered.  (For example, with a cache
+    sequence when all the backends are considered.  (For example, with a <replaceable class="parameter">cache</replaceable>
     setting of 10, backend A might reserve values 1..10 and return nextval=1, 
     then
     backend B might reserve values 11..20 and return nextval=11 before backend
-    A has generated nextval=2.)  Thus, with a cache setting of one it is safe
-    to assume that nextval values are generated sequentially; with a cache
-    setting greater than one you should only assume that the nextval values
+    A has generated nextval=2.)  Thus, with a <replaceable class="parameter">cache</replaceable> setting of one it is safe
+    to assume that <function>nextval</> values are generated sequentially; with a <replaceable class="parameter">cache</replaceable>
+    setting greater than one you should only assume that the <function>nextval</> values
     are all distinct, not that they are generated purely sequentially.
-    Also, last_value will reflect the latest value reserved by any backend,
-    whether or not it has yet been returned by nextval.
-    Another consideration is that a setval executed on such a sequence
+    Also, <literal>last_value</> will reflect the latest value reserved by any backend,
+    whether or not it has yet been returned by <function>nextval</>.
+    Another consideration is that a <function>setval</> executed on such a sequence
     will not be noticed by other backends until they have used up any
     preallocated values they have cached.
    </para>
@@ -313,7 +291,8 @@ SELECT last_value FROM <replaceable>seqname</replaceable>;
     Use <command>DROP SEQUENCE</command> to remove a sequence.
    </para>
    <para>
-    Each backend uses its own cache to store preallocated numbers.
+    When <replaceable class="parameter">cache</replaceable> is greater than
+    one, each backend uses its own cache to store preallocated numbers.
     Numbers that are cached but not used in the current session will be
     lost, resulting in <quote>holes</quote> in the sequence.
    </para>
@@ -333,7 +312,7 @@ CREATE SEQUENCE serial START 101;
   <para>
    Select the next number from this sequence:
    <programlisting>
-SELECT NEXTVAL ('serial');
+SELECT nextval('serial');
     
 nextval
 -------
@@ -343,19 +322,16 @@ nextval
   <para>
    Use this sequence in an INSERT:
    <programlisting>
-INSERT INTO distributors VALUES (NEXTVAL('serial'),'nothing');
+INSERT INTO distributors VALUES (nextval('serial'), 'nothing');
    </programlisting>
   </para>
 
   <para>
-   Set the sequence value after a COPY FROM:
+   Update the sequence value after a COPY FROM:
    <programlisting>
-CREATE FUNCTION distributors_id_max() RETURNS INT4
-    AS 'SELECT max(id) FROM distributors' 
-    LANGUAGE 'sql';
 BEGIN;
     COPY distributors FROM 'input_file';
-    SELECT setval('serial', distributors_id_max());
+    SELECT setval('serial', max(id)) FROM distributors;
 END;
    </programlisting>
   </para>