Todo items:

Add ALTER SEQUENCE to modify min/max/increment/cache/cycle values

Also updated create sequence docs to mention NO MINVALUE, & NO MAXVALUE.

New Files:
doc/src/sgml/ref/alter_sequence.sgml
src/test/regress/expected/sequence.out
src/test/regress/sql/sequence.sql


ALTER SEQUENCE is NOT transactional.  It behaves similarly to setval().
It matches the proposed SQL200N spec, as well as Oracle in most ways --
Oracle lacks RESTART WITH for some strange reason.

--
Rod Taylor <rbt@rbt.ca>

4 files changed, 295 insertions, 9 deletions

diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index 53cb7d4b184..a922e933e16 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.52 2002/12/06 04:40:36 momjian Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.53 2003/03/20 07:02:07 momjian Exp $
 PostgreSQL documentation
 Complete list of usable sgml source files in this directory.
 -->
@@ -40,6 +40,7 @@ Complete list of usable sgml source files in this directory.
 <!entity alterDatabase      system "alter_database.sgml">
 <!entity alterDomain        system "alter_domain.sgml">
 <!entity alterGroup         system "alter_group.sgml">
+<!entity alterSequence      system "alter_sequence.sgml">
 <!entity alterTable         system "alter_table.sgml">
 <!entity alterTrigger       system "alter_trigger.sgml">
 <!entity alterUser          system "alter_user.sgml">
diff --git a/doc/src/sgml/ref/alter_sequence.sgml b/doc/src/sgml/ref/alter_sequence.sgml
new file mode 100644
index 00000000000..c6c3a7e33c0
--- /dev/null
+++ b/doc/src/sgml/ref/alter_sequence.sgml
@@ -0,0 +1,280 @@
+<!--
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_sequence.sgml,v 1.1 2003/03/20 07:02:07 momjian Exp $
+PostgreSQL documentation
+-->
+
+<refentry id="SQL-ALTERSEQUENCE">
+ <refmeta>
+  <refentrytitle id="SQL-ALTERSEQUENCE-TITLE">ALTER SEQUENCE</refentrytitle>
+  <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+ <refnamediv>
+  <refname>
+   ALTER SEQUENCE
+  </refname>
+  <refpurpose>
+   alter the definition of a sequence generator
+  </refpurpose>
+ </refnamediv> 
+ <refsynopsisdiv>
+  <refsynopsisdivinfo>
+   <date>1999-07-20</date>
+  </refsynopsisdivinfo>
+  <synopsis>
+ALTER SEQUENCE <replaceable class="parameter">seqname</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
+    [ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
+    [ RESTART [ WITH ] <replaceable class="parameter">start</replaceable> ] [ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
+  </synopsis>
+  
+  <refsect2 id="R2-SQL-ALTERSEQUENCE-1">
+   <refsect2info>
+    <date>1998-09-11</date>
+   </refsect2info>
+   <title>
+    Inputs
+   </title>
+   <para>
+
+    <variablelist>
+     <varlistentry>
+      <term><replaceable class="parameter">seqname</replaceable></term>
+      <listitem>
+       <para>
+	The name (optionally schema-qualified) of a sequence to be altered.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><replaceable class="parameter">increment</replaceable></term>
+      <listitem>
+       <para>
+	The
+	<option>INCREMENT BY <replaceable class="parameter">increment</replaceable></option>
+	clause is optional. A positive value will make an
+	ascending sequence, a negative one a descending sequence.
+	If unspecified, the old increment value will be maintained.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><replaceable class="parameter">minvalue</replaceable></term>
+      <term>NO MINVALUE</term>
+      <listitem>
+       <para>
+	The optional clause <option>MINVALUE
+	 <replaceable class="parameter">minvalue</replaceable></option>
+	determines the minimum value
+	a sequence can generate. If <option>NO MINVALUE</option> is specified,
+    the defaults of 1 and -2^63-1 for ascending and descending sequences, respectively, will be used.  If neither option is specified, the current minimum
+    value will be maintained.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><replaceable class="parameter">maxvalue</replaceable></term>
+      <term>NO MAXVALUE</term>
+      <listitem>
+       <para>
+	The optional clause <option>MAXVALUE
+	 <replaceable class="parameter">maxvalue</replaceable></option>
+	determines the maximum value for the sequence. If 
+    <option>NO MAXVALUE</option> is specified, the defaults are 2^63-1 and -1 for
+	ascending and descending sequences, respectively, will be used.  If
+    neither option is specified, the current maximum value will be
+    maintained.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><replaceable class="parameter">start</replaceable></term>
+      <listitem>
+       <para>
+	The optional <option>RESTART WITH
+	 <replaceable class="parameter">start</replaceable>
+	 clause</option> enables the sequence to re-begin anywhere.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><replaceable class="parameter">cache</replaceable></term>
+      <listitem>
+       <para>
+	The <option>CACHE <replaceable class="parameter">cache</replaceable></option> option
+	enables sequence numbers to be preallocated
+	and stored in memory for faster access. The  minimum
+	value is 1 (only one value can be generated at a time, i.e., no cache).
+	If unspecified, the old cache value will be maintained.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term>CYCLE</term>
+      <listitem>
+       <para>
+	The optional <option>CYCLE</option> keyword may be used to enable
+	the sequence to wrap around when the
+	<replaceable class="parameter">maxvalue</replaceable> or
+	<replaceable class="parameter">minvalue</replaceable> has been
+	reached by
+	an ascending or descending sequence respectively. If the limit is
+	reached, the next number generated will be the
+	<replaceable class="parameter">minvalue</replaceable> or
+	<replaceable class="parameter">maxvalue</replaceable>,
+	respectively.
+       </para>
+      </listitem>
+     </varlistentry>
+
+	 <varlistentry>
+	  <term>NO CYCLE</term>
+	  <listitem>
+	   <para>
+	If the optional <option>NO CYCLE</option> keyword is specified, any
+	calls to <function>nextval</function> after the sequence has reached
+	its maximum value will return an error.  If neither
+	<option>CYCLE</option> or <option>NO CYCLE</option> are specified,
+	the old cycle behaviour will be maintained.
+	   </para>
+	  </listitem>
+	</varlistentry>
+    </variablelist>
+   </para>
+  </refsect2>
+
+  <refsect2 id="R2-SQL-ALTERSEQUENCE-2">
+   <refsect2info>
+    <date>1998-09-11</date>
+   </refsect2info>
+   <title>
+    Outputs
+   </title>
+   <para>
+
+    <variablelist>
+     <varlistentry>
+      <term><computeroutput>
+ALTER SEQUENCE
+       </computeroutput></term>
+      <listitem>
+       <para>
+	Message returned if the command is successful.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry>
+      <term><computeroutput>
+ERROR:  AlterSequence: MINVALUE (<replaceable class="parameter">start</replaceable>) can't be >= MAXVALUE (<replaceable class="parameter">max</replaceable>)
+       </computeroutput></term>
+      <listitem>
+       <para>
+	If the specified starting value is out of range.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry>
+      <term><computeroutput>
+ERROR:  AlterSequence: START value (<replaceable class="parameter">start</replaceable>) can't be < MINVALUE (<replaceable class="parameter">min</replaceable>)
+       </computeroutput></term>
+      <listitem>
+       <para>
+	If the specified starting value is out of range.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry>
+      <term><computeroutput>
+ERROR:  AlterSequence: MINVALUE (<replaceable class="parameter">min</replaceable>) can't be >= MAXVALUE (<replaceable class="parameter">max</replaceable>)
+       </computeroutput></term>
+      <listitem>
+       <para>
+	If the minimum and maximum values are inconsistent.
+       </para>
+      </listitem>
+     </varlistentry>
+    </variablelist>
+   </para>
+  </refsect2>
+ </refsynopsisdiv>
+
+ <refsect1 id="R1-SQL-ALTERSEQUENCE-1">
+  <title>
+   Description
+  </title>
+
+  <para>
+   See <xref linkend="SQL-CREATESEQUENCE"> for limitations, and uses
+   of sequences.
+  </para>
+ </refsect1>
+
+ <refsect1 id="R1-SQL-ALTERSEQUENCE-2">
+  <title>
+   Usage
+  </title>
+  <para>
+   Restart a sequence called <literal>serial</literal>, at 105:
+  </para>
+  <programlisting>
+ALTER SEQUENCE serial RESTART WITH 105;
+  </programlisting>
+ </refsect1>
+
+ <refsect1 id="R1-SQL-ALTERSEQUENCE-5">
+  <title>
+   Notes
+  </title>
+  <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 "holes" in
+   the sequence of assigned values. setval operations are never rolled back, either.
+  </para>
+ </refsect1>
+
+
+ <refsect1 id="R1-SQL-ALTERSEQUENCE-3">
+  <title>
+   Compatibility
+  </title>
+
+  <refsect2 id="R2-SQL-ALTERSEQUENCE-4">
+   <refsect2info>
+    <date>2003-03-02</date>
+   </refsect2info>
+   <title>
+    SQL99
+   </title>
+
+   <para>
+    <command>ALTER SEQUENCE</command> is a <productname>PostgreSQL</productname>
+    language extension.
+    There is no <command>ALTER SEQUENCE</command> statement
+    in <acronym>SQL99</acronym>.
+   </para>
+  </refsect2>
+ </refsect1>
+</refentry>
+
+<!-- Keep this comment at the end of the file
+Local variables:
+mode: sgml
+sgml-omittag:nil
+sgml-shorttag:t
+sgml-minimize-attributes:nil
+sgml-always-quote-attributes:t
+sgml-indent-step:1
+sgml-indent-data:t
+sgml-parent-document:nil
+sgml-default-dtd-file:"../reference.ced"
+sgml-exposed-tags:nil
+sgml-local-catalogs:"/usr/lib/sgml/catalog"
+sgml-local-ecat-files:nil
+End:
+-->
diff --git a/doc/src/sgml/ref/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml
index a357e434e37..550605447fb 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.30 2003/01/19 00:13:29 momjian Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_sequence.sgml,v 1.31 2003/03/20 07:02:07 momjian Exp $
 PostgreSQL documentation
 -->
 
@@ -22,7 +22,7 @@ PostgreSQL documentation
   </refsynopsisdivinfo>
   <synopsis>
 CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">seqname</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
-    [ MINVALUE <replaceable class="parameter">minvalue</replaceable> ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> ]
+    [ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
     [ START [ WITH ] <replaceable class="parameter">start</replaceable> ] [ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
   </synopsis>
   
@@ -63,7 +63,7 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">seqname</rep
       <listitem>
        <para>
 	The
-	<option>INCREMENT <replaceable class="parameter">increment</replaceable></option>
+	<option>INCREMENT BY <replaceable class="parameter">increment</replaceable></option>
 	clause is optional. A positive value will make an
 	ascending sequence, a negative one a descending sequence.
 	The default value is one (1).
@@ -73,12 +73,14 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">seqname</rep
 
      <varlistentry>
       <term><replaceable class="parameter">minvalue</replaceable></term>
+      <term>NO MINVALUE</term>
       <listitem>
        <para>
 	The optional clause <option>MINVALUE
 	 <replaceable class="parameter">minvalue</replaceable></option>
 	determines the minimum value
-	a sequence can generate. The defaults are 1 and -2^63-1 for
+	a sequence can generate. If this clause is not supplied or <option>NO MINVALUE</option>
+    is specified, then defaults will be used.  The defaults are 1 and -2^63-1 for
 	ascending and descending sequences, respectively.
        </para>
       </listitem>
@@ -86,13 +88,15 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">seqname</rep
 
      <varlistentry>
       <term><replaceable class="parameter">maxvalue</replaceable></term>
+      <term>NO MAXVALUE</term>
       <listitem>
        <para>
 	The optional clause <option>MAXVALUE
 	 <replaceable class="parameter">maxvalue</replaceable></option>
 	determines the maximum
-	value for the sequence. The defaults are 2^63-1 and -1 for
-	ascending and descending sequences, respectively.
+	value for the sequence. If this clause is not supplied or 
+    <option>NO MAXVALUE</option> is specified, then default values will be used.
+    The defaults are 2^63-1 and -1 for ascending and descending sequences, respectively.
        </para>
       </listitem>
      </varlistentry>
@@ -101,7 +105,7 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">seqname</rep
       <term><replaceable class="parameter">start</replaceable></term>
       <listitem>
        <para>
-	The optional <option>START
+	The optional <option>START WITH
 	 <replaceable class="parameter">start</replaceable>
 	 clause</option> enables the sequence to begin anywhere.
 	The default starting value is
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index 9f5cdf8b912..3486ca41823 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -1,5 +1,5 @@
 <!-- reference.sgml
-$Header: /cvsroot/pgsql/doc/src/sgml/reference.sgml,v 1.41 2002/12/06 05:00:10 momjian Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/reference.sgml,v 1.42 2003/03/20 07:02:07 momjian Exp $
 
 PostgreSQL Reference Manual
 -->
@@ -49,6 +49,7 @@ PostgreSQL Reference Manual
    &alterDatabase;
    &alterDomain;
    &alterGroup;
+   &alterSequence;
    &alterTable;
    &alterTrigger;
    &alterUser;