diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/datatype.sgml | 27 | ||||
| -rw-r--r-- | doc/src/sgml/func.sgml | 14 | ||||
| -rw-r--r-- | doc/src/sgml/ref/alter_sequence.sgml | 45 | ||||
| -rw-r--r-- | doc/src/sgml/ref/alter_table.sgml | 4 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_sequence.sgml | 50 | ||||
| -rw-r--r-- | doc/src/sgml/ref/drop_sequence.sgml | 3 |
6 files changed, 108 insertions, 35 deletions
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 35e71004c18..c5ba6c4d973 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.169 2006/07/06 01:46:37 momjian Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.170 2006/08/21 00:57:23 tgl Exp $ --> <chapter id="datatype"> <title id="datatype-title">Data Types</title> @@ -705,17 +705,19 @@ CREATE TABLE <replaceable class="parameter">tablename</replaceable> ( <programlisting> CREATE SEQUENCE <replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq; CREATE TABLE <replaceable class="parameter">tablename</replaceable> ( - <replaceable class="parameter">colname</replaceable> integer DEFAULT nextval('<replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq') NOT NULL + <replaceable class="parameter">colname</replaceable> integer NOT NULL DEFAULT nextval('<replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq') ); +ALTER SEQUENCE <replaceable class="parameter">tablename</replaceable>_<replaceable class="parameter">colname</replaceable>_seq OWNED BY <replaceable class="parameter">tablename</replaceable>.<replaceable class="parameter">colname</replaceable>; </programlisting> Thus, we have created an integer column and arranged for its default values to be assigned from a sequence generator. A <literal>NOT NULL</> constraint is applied to ensure that a null value cannot be explicitly - inserted, either. In most cases you would also want to attach a + inserted, either. (In most cases you would also want to attach a <literal>UNIQUE</> or <literal>PRIMARY KEY</> constraint to prevent duplicate values from being inserted by accident, but this is - not automatic. + not automatic.) Lastly, the sequence is marked as <quote>owned by</> + the column, so that it will be dropped if the column or table is dropped. </para> <note> @@ -749,20 +751,9 @@ CREATE TABLE <replaceable class="parameter">tablename</replaceable> ( <para> The sequence created for a <type>serial</type> column is - automatically dropped when the owning column is dropped, and - cannot be dropped otherwise. (This was not true in - <productname>PostgreSQL</productname> releases before 7.3. Note - that this automatic drop linkage will not occur for a sequence - created by reloading a dump from a pre-7.3 database; the dump - file does not contain the information needed to establish the - dependency link.) Furthermore, this dependency between sequence - and column is made only for the <type>serial</> column itself. If - any other columns reference the sequence (perhaps by manually - calling the <function>nextval</> function), they will be broken - if the sequence is removed. Using a <type>serial</> column's sequence - in such a fashion is considered bad form; if you wish to feed several - columns from the same sequence generator, create the sequence as an - independent object. + automatically dropped when the owning column is dropped. + You can drop the sequence without dropping the column, but this + will force removal of the column default expression. </para> </sect2> </sect1> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 113411f78b5..2b2a0a0d18c 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.330 2006/08/17 23:04:03 tgl Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.331 2006/08/21 00:57:23 tgl Exp $ --> <chapter id="functions"> <title>Functions and Operators</title> @@ -9863,10 +9863,14 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); <para> <function>pg_get_serial_sequence</function> fetches the name of the - sequence associated with a <type>serial</> or <type>bigserial</> - column. The name is suitably formatted for passing to the sequence - functions (see <xref linkend="functions-sequence">). NULL is - returned if the column does not have an associated sequence. + sequence associated with a column, or NULL if there is no sequence + associated with the column. The result is suitably formatted for passing + to the sequence functions (see <xref linkend="functions-sequence">). + This association can be modified or removed with <command>ALTER SEQUENCE + OWNED BY</>. (The function probably should have been called + <function>pg_get_owned_sequence</function>; its name reflects the fact + that it's typically used with <type>serial</> or <type>bigserial</> + columns.) </para> <para> 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 diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 17b0f3667da..d7c31064a06 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.88 2006/08/03 20:57:06 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.89 2006/08/21 00:57:24 tgl Exp $ PostgreSQL documentation --> @@ -371,7 +371,7 @@ where <replaceable class="PARAMETER">action</replaceable> is one of: <listitem> <para> This form moves the table into another schema. Associated indexes, - constraints, and SERIAL-column sequences are moved as well. + constraints, and sequences owned by table columns are moved as well. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml index 7a094f6ef23..5fa16c2a81c 100644 --- a/doc/src/sgml/ref/create_sequence.sgml +++ b/doc/src/sgml/ref/create_sequence.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/create_sequence.sgml,v 1.43 2005/11/01 21:09:50 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/create_sequence.sgml,v 1.44 2006/08/21 00:57:24 tgl Exp $ PostgreSQL documentation --> @@ -23,6 +23,7 @@ PostgreSQL documentation CREATE [ TEMPORARY | TEMP ] 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 ] [ START [ 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 } ] </synopsis> </refsynopsisdiv> @@ -193,6 +194,22 @@ SELECT * FROM <replaceable>name</replaceable>; </para> </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. The specified table must have the same owner and be in + the same schema as the sequence. + <literal>OWNED BY NONE</literal>, the default, specifies that there + is no such association. + </para> + </listitem> + </varlistentry> </variablelist> </refsect1> @@ -300,11 +317,38 @@ END; <command>CREATE SEQUENCE</command> conforms to the <acronym>SQL</acronym> standard, with the following exceptions: <itemizedlist> - <listitem><para>The standard's <literal>AS <data type></literal> expression is not supported.</para></listitem> - <listitem><para>Obtaining the next value is done using the <function>nextval()</> function instead of the standard's <command>NEXT VALUE FOR</command> expression.</para></listitem> + <listitem> + <para> + The standard's <literal>AS <data type></literal> expression is not + supported. + </para> + </listitem> + <listitem> + <para> + Obtaining the next value is done using the <function>nextval()</> + function instead of the standard's <command>NEXT VALUE FOR</command> + expression. + </para> + </listitem> + <listitem> + <para> + The <literal>OWNED BY</> clause is a <productname>PostgreSQL</> + extension. + </para> + </listitem> </itemizedlist> </para> </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="sql-altersequence" endterm="sql-altersequence-title"></member> + <member><xref linkend="sql-dropsequence" endterm="sql-dropsequence-title"></member> + </simplelist> + </refsect1> + </refentry> <!-- Keep this comment at the end of the file diff --git a/doc/src/sgml/ref/drop_sequence.sgml b/doc/src/sgml/ref/drop_sequence.sgml index 074f4e7216d..d730d683d3f 100644 --- a/doc/src/sgml/ref/drop_sequence.sgml +++ b/doc/src/sgml/ref/drop_sequence.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/drop_sequence.sgml,v 1.24 2005/11/19 17:39:44 adunstan Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/drop_sequence.sgml,v 1.25 2006/08/21 00:57:24 tgl Exp $ PostgreSQL documentation --> @@ -105,6 +105,7 @@ DROP SEQUENCE serial; <simplelist type="inline"> <member><xref linkend="sql-createsequence" endterm="sql-createsequence-title"></member> + <member><xref linkend="sql-altersequence" endterm="sql-altersequence-title"></member> </simplelist> </refsect1> |