Fix all known problems with pg_dump's handling of serial sequences

by abandoning the idea that it should say SERIAL in the dump.  Instead,
dump serial sequences and column defaults just like regular ones.
Add a new backend command ALTER SEQUENCE OWNED BY to let pg_dump recreate
the sequence-to-column dependency that was formerly created "behind the
scenes" by SERIAL.  This restores SERIAL to being truly "just a macro"
consisting of component operations that can be stated explicitly in SQL.
Furthermore, the new command allows sequence ownership to be reassigned,
so that old mistakes can be cleaned up.

Also, downgrade the OWNED-BY dependency from INTERNAL to AUTO, since there
is no longer any very compelling argument why the sequence couldn't be
dropped while keeping the column.  (This forces initdb, to be sure the
right kinds of dependencies are in there.)

Along the way, add checks to prevent ALTER OWNER or SET SCHEMA on an
owned sequence; you can now only do this indirectly by changing the
owning table's owner or schema.  This is an oversight in previous
releases, but probably not worth back-patching.

1 files changed, 39 insertions, 6 deletions

diff --git a/doc/src/sgml/ref/alter_sequence.sgml b/doc/src/sgml/ref/alter_sequence.sgml
index 3e7937e185b..3d6d5caf8f6 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.13 2005/11/01 21:09:50 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/alter_sequence.sgml,v 1.14 2006/08/21 00:57:24 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -27,6 +27,7 @@ PostgreSQL documentation
 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 ]
+    [ OWNED BY { <replaceable class="parameter">table</replaceable>.<replaceable class="parameter">column</replaceable> | NONE } ]
 ALTER SEQUENCE <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
   </synopsis>
  </refsynopsisdiv>
@@ -163,6 +164,24 @@ ALTER SEQUENCE <replaceable class="parameter">name</replaceable> SET SCHEMA <rep
       </listitem>
      </varlistentry>
 
+   <varlistentry>
+    <term><literal>OWNED BY</literal> <replaceable class="parameter">table</replaceable>.<replaceable class="parameter">column</replaceable></term>
+    <term><literal>OWNED BY NONE</literal></term>
+    <listitem>
+     <para>
+      The <literal>OWNED BY</literal> option causes the sequence to be
+      associated with a specific table column, such that if that column
+      (or its whole table) is dropped, the sequence will be automatically
+      dropped as well.  If specified, this association replaces any
+      previously specified association for the sequence.  The specified
+      table must have the same owner and be in the same schema as the
+      sequence.
+      Specifying <literal>OWNED BY NONE</literal> removes any existing
+      association, making the sequence <quote>free-standing</>.
+     </para>
+    </listitem>
+   </varlistentry>
+
      <varlistentry>
       <term><replaceable class="parameter">new_schema</replaceable></term>
       <listitem>
@@ -191,8 +210,11 @@ ALTER SEQUENCE serial RESTART WITH 105;
 
   <para>
    To avoid blocking of concurrent transactions that obtain numbers from the
-   same sequence, <command>ALTER SEQUENCE</command> is never rolled back;
-   the changes take effect immediately and are not reversible.
+   same sequence, <command>ALTER SEQUENCE</command>'s effects on the sequence
+   generation parameters are never rolled back;
+   those changes take effect immediately and are not reversible.  However,
+   the <literal>OWNED BY</> and <literal>SET SCHEMA</> clauses are ordinary
+   catalog updates and can be rolled back.
   </para>
 
   <para>
@@ -200,7 +222,8 @@ ALTER SEQUENCE serial RESTART WITH 105;
    <function>nextval</> results in backends,
    other than the current one, that have preallocated (cached) sequence
    values. They will use up all cached values prior to noticing the changed
-   sequence parameters.  The current backend will be affected immediately.
+   sequence generation parameters.  The current backend will be affected
+   immediately.
   </para>
 
   <para>
@@ -217,10 +240,20 @@ ALTER SEQUENCE serial RESTART WITH 105;
   <para>
    <command>ALTER SEQUENCE</command> conforms to the <acronym>SQL</acronym>
    standard,
-   except for the <literal>SET SCHEMA</literal> variant, which is a
-   <productname>PostgreSQL</productname> extension.
+   except for the <literal>OWNED BY</> and <literal>SET SCHEMA</literal>
+   clauses, which are <productname>PostgreSQL</productname> extensions.
   </para>
  </refsect1>
+
+ <refsect1>
+  <title>See Also</title>
+
+  <simplelist type="inline">
+   <member><xref linkend="sql-createsequence" endterm="sql-createsequence-title"></member>
+   <member><xref linkend="sql-dropsequence" endterm="sql-dropsequence-title"></member>
+  </simplelist>
+ </refsect1>
+
 </refentry>
 
 <!-- Keep this comment at the end of the file