Add a RESTART (without parameter) option to ALTER SEQUENCE, allowing a

sequence to be reset to its original starting value.  This requires adding the
original start value to the set of parameters (columns) of a sequence object,
which is a user-visible change with potential compatibility implications;
it also forces initdb.

Also add hopefully-SQL-compatible RESTART/CONTINUE IDENTITY options to
TRUNCATE TABLE.  RESTART IDENTITY executes ALTER SEQUENCE RESTART for all
sequences "owned by" any of the truncated relations.  CONTINUE IDENTITY is
a no-op option.

Zoltan Boszormenyi

2 files changed, 59 insertions, 12 deletions

diff --git a/doc/src/sgml/ref/alter_sequence.sgml b/doc/src/sgml/ref/alter_sequence.sgml
index 3c982eee6f1..31e64dac35d 100644
--- a/doc/src/sgml/ref/alter_sequence.sgml
+++ b/doc/src/sgml/ref/alter_sequence.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/alter_sequence.sgml,v 1.19 2007/10/25 18:54:03 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/alter_sequence.sgml,v 1.20 2008/05/16 23:36:04 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -26,7 +26,7 @@ PostgreSQL documentation
   <synopsis>
 ALTER SEQUENCE <replaceable class="parameter">name</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 ]
+    [ RESTART [ [ WITH ] <replaceable class="parameter">start</replaceable> ] ] [ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
     [ OWNED BY { <replaceable class="parameter">table</replaceable>.<replaceable class="parameter">column</replaceable> | NONE } ]
 ALTER SEQUENCE <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
 ALTER SEQUENCE <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
@@ -112,12 +112,15 @@ ALTER SEQUENCE <replaceable class="parameter">name</replaceable> SET SCHEMA <rep
       <term><replaceable class="parameter">start</replaceable></term>
       <listitem>
        <para>
-        The optional clause <literal>RESTART WITH <replaceable
-        class="parameter">start</replaceable></literal> changes the
+        The optional clause <literal>RESTART [ WITH <replaceable
+        class="parameter">start</replaceable> ]</literal> changes the
         current value of the sequence.  This is equivalent to calling the
         <function>setval</> function with <literal>is_called</literal> =
         <literal>false</>: the specified value will be returned by the
         <emphasis>next</> call of <function>nextval</>.
+        Writing <literal>RESTART</> with no <replaceable
+        class="parameter">start</replaceable> value is equivalent to supplying
+        the start value used when the sequence was created.
        </para>
       </listitem>
      </varlistentry>
diff --git a/doc/src/sgml/ref/truncate.sgml b/doc/src/sgml/ref/truncate.sgml
index 486a2d3e992..effe903b099 100644
--- a/doc/src/sgml/ref/truncate.sgml
+++ b/doc/src/sgml/ref/truncate.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/truncate.sgml,v 1.25 2008/03/28 00:21:55 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/truncate.sgml,v 1.26 2008/05/16 23:36:04 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -20,7 +20,8 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ CASCADE | RESTRICT ]
+TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ... ]
+    [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -51,6 +52,25 @@ TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ C
    </varlistentry>
 
    <varlistentry>
+    <term><literal>RESTART IDENTITY</literal></term>
+    <listitem>
+     <para>
+      Automatically restart sequences owned by columns of
+      the truncated table(s).
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><literal>CONTINUE IDENTITY</literal></term>
+    <listitem>
+     <para>
+      Do not change the values of sequences.  This is the default.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
     <term><literal>CASCADE</literal></term>
     <listitem>
      <para>
@@ -66,7 +86,7 @@ TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ C
     <listitem>
      <para>
       Refuse to truncate if any of the tables have foreign-key references
-      from tables that are not to be truncated.  This is the default.
+      from tables that are not listed in the command.  This is the default.
      </para>
     </listitem>
    </varlistentry>
@@ -119,11 +139,23 @@ TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ C
      cause visible inconsistency between the contents of the truncated
      table and other tables in the database.
    </para>
+  </warning>
+
+  <para>
+   <command>TRUNCATE</> is transaction-safe with respect to the data
+   in the tables: the truncation will be safely rolled back if the surrounding
+   transaction does not commit.
+  </para>
 
+  <warning>
    <para>
-    <command>TRUNCATE</> is transaction-safe, however: the truncation
-    will be safely rolled back if the surrounding transaction does not
-    commit.
+    Any <command>ALTER SEQUENCE RESTART</> operations performed as a
+    consequence of using the <literal>RESTART IDENTITY</> option are
+    nontransactional and will not be rolled back.  To minimize risk,
+    these operations are performed only after all the rest of
+    <command>TRUNCATE</>'s work is done.  In practice this will only
+    be an issue if <command>TRUNCATE</> is performed inside a
+    transaction block that is aborted afterwards.
    </para>
   </warning>
  </refsect1>
@@ -132,7 +164,8 @@ TRUNCATE [ TABLE ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ C
   <title>Examples</title>
 
   <para>
-   Truncate the tables <literal>bigtable</literal> and <literal>fattable</literal>:
+   Truncate the tables <literal>bigtable</literal> and
+   <literal>fattable</literal>:
 
 <programlisting>
 TRUNCATE bigtable, fattable;
@@ -140,6 +173,14 @@ TRUNCATE bigtable, fattable;
   </para>
 
   <para>
+   The same, and also reset any associated sequence generators:
+
+<programlisting>
+TRUNCATE bigtable, fattable RESTART IDENTITY;
+</programlisting>
+  </para>
+
+  <para>
    Truncate the table <literal>othertable</literal>, and cascade to any tables
    that reference <literal>othertable</literal> via foreign-key
    constraints:
@@ -154,7 +195,10 @@ TRUNCATE othertable CASCADE;
   <title>Compatibility</title>
 
   <para>
-   There is no <command>TRUNCATE</command> command in the SQL standard.
+   The draft SQL:2008 standard includes a <command>TRUNCATE</command> command,
+   but at this writing it is uncertain whether that will reach standardization
+   or be fully compatible with <productname>PostgreSQL</productname>'s
+   implementation.
   </para>
  </refsect1>
 </refentry>