From 5f65225fa37bd244ee5487448ac542b2b2f47296 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Thu, 20 Mar 2003 07:02:11 +0000 Subject: 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 --- doc/src/sgml/ref/allfiles.sgml | 3 +- doc/src/sgml/ref/alter_sequence.sgml | 280 ++++++++++++++++++++++++++++++++++ doc/src/sgml/ref/create_sequence.sgml | 18 ++- doc/src/sgml/reference.sgml | 3 +- 4 files changed, 295 insertions(+), 9 deletions(-) create mode 100644 doc/src/sgml/ref/alter_sequence.sgml (limited to 'doc/src') 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 @@ @@ -40,6 +40,7 @@ Complete list of usable sgml source files in this directory. + 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 @@ + + + + + ALTER SEQUENCE + SQL - Language Statements + + + + ALTER SEQUENCE + + + alter the definition of a sequence generator + + + + + 1999-07-20 + + +ALTER SEQUENCE seqname [ INCREMENT [ BY ] increment ] + [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] + [ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] + + + + + 1998-09-11 + + + Inputs + + + + + + seqname + + + The name (optionally schema-qualified) of a sequence to be altered. + + + + + + increment + + + The + + 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. + + + + + + minvalue + NO MINVALUE + + + The optional clause + determines the minimum value + a sequence can generate. If 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. + + + + + + maxvalue + NO MAXVALUE + + + The optional clause + determines the maximum value for the sequence. If + 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. + + + + + + start + + + The optional enables the sequence to re-begin anywhere. + + + + + + cache + + + The 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. + + + + + + CYCLE + + + The optional keyword may be used to enable + the sequence to wrap around when the + maxvalue or + minvalue has been + reached by + an ascending or descending sequence respectively. If the limit is + reached, the next number generated will be the + minvalue or + maxvalue, + respectively. + + + + + + NO CYCLE + + + If the optional keyword is specified, any + calls to nextval after the sequence has reached + its maximum value will return an error. If neither + or are specified, + the old cycle behaviour will be maintained. + + + + + + + + + + 1998-09-11 + + + Outputs + + + + + + +ALTER SEQUENCE + + + + Message returned if the command is successful. + + + + + +ERROR: AlterSequence: MINVALUE (start) can't be >= MAXVALUE (max) + + + + If the specified starting value is out of range. + + + + + +ERROR: AlterSequence: START value (start) can't be < MINVALUE (min) + + + + If the specified starting value is out of range. + + + + + +ERROR: AlterSequence: MINVALUE (min) can't be >= MAXVALUE (max) + + + + If the minimum and maximum values are inconsistent. + + + + + + + + + + + Description + + + + See for limitations, and uses + of sequences. + + + + + + Usage + + + Restart a sequence called serial, at 105: + + +ALTER SEQUENCE serial RESTART WITH 105; + + + + + + Notes + + + 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. + + + + + + + Compatibility + + + + + 2003-03-02 + + + SQL99 + + + + ALTER SEQUENCE is a PostgreSQL + language extension. + There is no ALTER SEQUENCE statement + in SQL99. + + + + + + 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 @@ @@ -22,7 +22,7 @@ PostgreSQL documentation CREATE [ TEMPORARY | TEMP ] SEQUENCE seqname [ INCREMENT [ BY ] increment ] - [ MINVALUE minvalue ] [ MAXVALUE maxvalue ] + [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] @@ -63,7 +63,7 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE seqname The - + 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 seqname minvalue + NO MINVALUE The optional clause 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 + is specified, then defaults will be used. The defaults are 1 and -2^63-1 for ascending and descending sequences, respectively. @@ -86,13 +88,15 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE seqname maxvalue + NO MAXVALUE The optional clause 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 + is specified, then default values will be used. + The defaults are 2^63-1 and -1 for ascending and descending sequences, respectively. @@ -101,7 +105,7 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE seqnamestart - The optional 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 @@ @@ -49,6 +49,7 @@ PostgreSQL Reference Manual &alterDatabase; &alterDomain; &alterGroup; + &alterSequence; &alterTable; &alterTrigger; &alterUser; -- cgit v1.2.3