From 2b2a50722cb1863147b4a86b3db80553f989a14c Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 21 Aug 2006 00:57:26 +0000 Subject: 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. --- doc/src/sgml/datatype.sgml | 27 +++++++------------ doc/src/sgml/func.sgml | 14 ++++++---- doc/src/sgml/ref/alter_sequence.sgml | 45 ++++++++++++++++++++++++++----- doc/src/sgml/ref/alter_table.sgml | 4 +-- doc/src/sgml/ref/create_sequence.sgml | 50 ++++++++++++++++++++++++++++++++--- doc/src/sgml/ref/drop_sequence.sgml | 3 ++- 6 files changed, 108 insertions(+), 35 deletions(-) (limited to 'doc/src') 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 @@ - + Data Types @@ -705,17 +705,19 @@ CREATE TABLE tablename ( CREATE SEQUENCE tablename_colname_seq; CREATE TABLE tablename ( - colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL + colname integer NOT NULL DEFAULT nextval('tablename_colname_seq') ); +ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname; Thus, we have created an integer column and arranged for its default values to be assigned from a sequence generator. A 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 UNIQUE or 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 owned by + the column, so that it will be dropped if the column or table is dropped. @@ -749,20 +751,9 @@ CREATE TABLE tablename ( The sequence created for a serial column is - automatically dropped when the owning column is dropped, and - cannot be dropped otherwise. (This was not true in - PostgreSQL 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 serial column itself. If - any other columns reference the sequence (perhaps by manually - calling the nextval function), they will be broken - if the sequence is removed. Using a 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. 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 @@ - + Functions and Operators @@ -9863,10 +9863,14 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); pg_get_serial_sequence fetches the name of the - sequence associated with a serial or bigserial - column. The name is suitably formatted for passing to the sequence - functions (see ). 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 ). + This association can be modified or removed with ALTER SEQUENCE + OWNED BY. (The function probably should have been called + pg_get_owned_sequence; its name reflects the fact + that it's typically used with serial or bigserial + columns.) 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 @@ @@ -27,6 +27,7 @@ PostgreSQL documentation ALTER SEQUENCE name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] + [ OWNED BY { table.column | NONE } ] ALTER SEQUENCE name SET SCHEMA new_schema @@ -163,6 +164,24 @@ ALTER SEQUENCE name SET SCHEMA + + OWNED BY table.column + OWNED BY NONE + + + The OWNED BY 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 OWNED BY NONE removes any existing + association, making the sequence free-standing. + + + + new_schema @@ -191,8 +210,11 @@ ALTER SEQUENCE serial RESTART WITH 105; To avoid blocking of concurrent transactions that obtain numbers from the - same sequence, ALTER SEQUENCE is never rolled back; - the changes take effect immediately and are not reversible. + same sequence, ALTER SEQUENCE's effects on the sequence + generation parameters are never rolled back; + those changes take effect immediately and are not reversible. However, + the OWNED BY and SET SCHEMA clauses are ordinary + catalog updates and can be rolled back. @@ -200,7 +222,8 @@ ALTER SEQUENCE serial RESTART WITH 105; 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. @@ -217,10 +240,20 @@ ALTER SEQUENCE serial RESTART WITH 105; ALTER SEQUENCE conforms to the SQL standard, - except for the SET SCHEMA variant, which is a - PostgreSQL extension. + except for the OWNED BY and SET SCHEMA + clauses, which are PostgreSQL extensions. + + + See Also + + + + + + + @@ -371,7 +371,7 @@ where action is one of: 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. 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 @@ @@ -23,6 +23,7 @@ PostgreSQL documentation CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] + [ OWNED BY { table.column | NONE } ] @@ -193,6 +194,22 @@ SELECT * FROM name; + + + OWNED BY table.column + OWNED BY NONE + + + The OWNED BY 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. + OWNED BY NONE, the default, specifies that there + is no such association. + + + @@ -300,11 +317,38 @@ END; CREATE SEQUENCE conforms to the SQL standard, with the following exceptions: - The standard's AS <data type> expression is not supported. - Obtaining the next value is done using the nextval() function instead of the standard's NEXT VALUE FOR expression. + + + The standard's AS <data type> expression is not + supported. + + + + + Obtaining the next value is done using the nextval() + function instead of the standard's NEXT VALUE FOR + expression. + + + + + The OWNED BY clause is a PostgreSQL + extension. + + + + + See Also + + + + + + + @@ -105,6 +105,7 @@ DROP SEQUENCE serial; + -- cgit v1.2.3