Skip to content

Commit 5f65225

Browse files
bmomjianbmomjian
committed
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 <rbt@rbt.ca>
1 parent 46bce08 commit 5f65225

22 files changed

+631
-125
lines changed

‎doc/src/sgml/ref/allfiles.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.52 2002/12/06 04:40:36 momjian Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.53 2003/03/20 07:02:07 momjian Exp $
33
PostgreSQL documentation
44
Complete list of usable sgml source files in this directory.
55
-->
@@ -40,6 +40,7 @@ Complete list of usable sgml source files in this directory.
4040
<!entity alterDatabase system "alter_database.sgml">
4141
<!entity alterDomain system "alter_domain.sgml">
4242
<!entity alterGroup system "alter_group.sgml">
43+
<!entity alterSequence system "alter_sequence.sgml">
4344
<!entity alterTable system "alter_table.sgml">
4445
<!entity alterTrigger system "alter_trigger.sgml">
4546
<!entity alterUser system "alter_user.sgml">

‎doc/src/sgml/ref/alter_sequence.sgml

Lines changed: 280 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,280 @@
1+
<!--
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_sequence.sgml,v 1.1 2003/03/20 07:02:07 momjian Exp $
3+
PostgreSQL documentation
4+
-->
5+
6+
<refentry id="SQL-ALTERSEQUENCE">
7+
<refmeta>
8+
<refentrytitle id="SQL-ALTERSEQUENCE-TITLE">ALTER SEQUENCE</refentrytitle>
9+
<refmiscinfo>SQL - Language Statements</refmiscinfo>
10+
</refmeta>
11+
<refnamediv>
12+
<refname>
13+
ALTER SEQUENCE
14+
</refname>
15+
<refpurpose>
16+
alter the definition of a sequence generator
17+
</refpurpose>
18+
</refnamediv>
19+
<refsynopsisdiv>
20+
<refsynopsisdivinfo>
21+
<date>1999-07-20</date>
22+
</refsynopsisdivinfo>
23+
<synopsis>
24+
ALTER SEQUENCE <replaceable class="parameter">seqname</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
25+
[ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
26+
[ RESTART [ WITH ] <replaceable class="parameter">start</replaceable> ] [ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
27+
</synopsis>
28+
29+
<refsect2 id="R2-SQL-ALTERSEQUENCE-1">
30+
<refsect2info>
31+
<date>1998-09-11</date>
32+
</refsect2info>
33+
<title>
34+
Inputs
35+
</title>
36+
<para>
37+
38+
<variablelist>
39+
<varlistentry>
40+
<term><replaceable class="parameter">seqname</replaceable></term>
41+
<listitem>
42+
<para>
43+
The name (optionally schema-qualified) of a sequence to be altered.
44+
</para>
45+
</listitem>
46+
</varlistentry>
47+
48+
<varlistentry>
49+
<term><replaceable class="parameter">increment</replaceable></term>
50+
<listitem>
51+
<para>
52+
The
53+
<option>INCREMENT BY <replaceable class="parameter">increment</replaceable></option>
54+
clause is optional. A positive value will make an
55+
ascending sequence, a negative one a descending sequence.
56+
If unspecified, the old increment value will be maintained.
57+
</para>
58+
</listitem>
59+
</varlistentry>
60+
61+
<varlistentry>
62+
<term><replaceable class="parameter">minvalue</replaceable></term>
63+
<term>NO MINVALUE</term>
64+
<listitem>
65+
<para>
66+
The optional clause <option>MINVALUE
67+
<replaceable class="parameter">minvalue</replaceable></option>
68+
determines the minimum value
69+
a sequence can generate. If <option>NO MINVALUE</option> is specified,
70+
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
71+
value will be maintained.
72+
</para>
73+
</listitem>
74+
</varlistentry>
75+
76+
<varlistentry>
77+
<term><replaceable class="parameter">maxvalue</replaceable></term>
78+
<term>NO MAXVALUE</term>
79+
<listitem>
80+
<para>
81+
The optional clause <option>MAXVALUE
82+
<replaceable class="parameter">maxvalue</replaceable></option>
83+
determines the maximum value for the sequence. If
84+
<option>NO MAXVALUE</option> is specified, the defaults are 2^63-1 and -1 for
85+
ascending and descending sequences, respectively, will be used. If
86+
neither option is specified, the current maximum value will be
87+
maintained.
88+
</para>
89+
</listitem>
90+
</varlistentry>
91+
92+
<varlistentry>
93+
<term><replaceable class="parameter">start</replaceable></term>
94+
<listitem>
95+
<para>
96+
The optional <option>RESTART WITH
97+
<replaceable class="parameter">start</replaceable>
98+
clause</option> enables the sequence to re-begin anywhere.
99+
</para>
100+
</listitem>
101+
</varlistentry>
102+
103+
<varlistentry>
104+
<term><replaceable class="parameter">cache</replaceable></term>
105+
<listitem>
106+
<para>
107+
The <option>CACHE <replaceable class="parameter">cache</replaceable></option> option
108+
enables sequence numbers to be preallocated
109+
and stored in memory for faster access. The minimum
110+
value is 1 (only one value can be generated at a time, i.e., no cache).
111+
If unspecified, the old cache value will be maintained.
112+
</para>
113+
</listitem>
114+
</varlistentry>
115+
116+
<varlistentry>
117+
<term>CYCLE</term>
118+
<listitem>
119+
<para>
120+
The optional <option>CYCLE</option> keyword may be used to enable
121+
the sequence to wrap around when the
122+
<replaceable class="parameter">maxvalue</replaceable> or
123+
<replaceable class="parameter">minvalue</replaceable> has been
124+
reached by
125+
an ascending or descending sequence respectively. If the limit is
126+
reached, the next number generated will be the
127+
<replaceable class="parameter">minvalue</replaceable> or
128+
<replaceable class="parameter">maxvalue</replaceable>,
129+
respectively.
130+
</para>
131+
</listitem>
132+
</varlistentry>
133+
134+
<varlistentry>
135+
<term>NO CYCLE</term>
136+
<listitem>
137+
<para>
138+
If the optional <option>NO CYCLE</option> keyword is specified, any
139+
calls to <function>nextval</function> after the sequence has reached
140+
its maximum value will return an error. If neither
141+
<option>CYCLE</option> or <option>NO CYCLE</option> are specified,
142+
the old cycle behaviour will be maintained.
143+
</para>
144+
</listitem>
145+
</varlistentry>
146+
</variablelist>
147+
</para>
148+
</refsect2>
149+
150+
<refsect2 id="R2-SQL-ALTERSEQUENCE-2">
151+
<refsect2info>
152+
<date>1998-09-11</date>
153+
</refsect2info>
154+
<title>
155+
Outputs
156+
</title>
157+
<para>
158+
159+
<variablelist>
160+
<varlistentry>
161+
<term><computeroutput>
162+
ALTER SEQUENCE
163+
</computeroutput></term>
164+
<listitem>
165+
<para>
166+
Message returned if the command is successful.
167+
</para>
168+
</listitem>
169+
</varlistentry>
170+
<varlistentry>
171+
<term><computeroutput>
172+
ERROR: AlterSequence: MINVALUE (<replaceable class="parameter">start</replaceable>) can't be >= MAXVALUE (<replaceable class="parameter">max</replaceable>)
173+
</computeroutput></term>
174+
<listitem>
175+
<para>
176+
If the specified starting value is out of range.
177+
</para>
178+
</listitem>
179+
</varlistentry>
180+
<varlistentry>
181+
<term><computeroutput>
182+
ERROR: AlterSequence: START value (<replaceable class="parameter">start</replaceable>) can't be < MINVALUE (<replaceable class="parameter">min</replaceable>)
183+
</computeroutput></term>
184+
<listitem>
185+
<para>
186+
If the specified starting value is out of range.
187+
</para>
188+
</listitem>
189+
</varlistentry>
190+
<varlistentry>
191+
<term><computeroutput>
192+
ERROR: AlterSequence: MINVALUE (<replaceable class="parameter">min</replaceable>) can't be >= MAXVALUE (<replaceable class="parameter">max</replaceable>)
193+
</computeroutput></term>
194+
<listitem>
195+
<para>
196+
If the minimum and maximum values are inconsistent.
197+
</para>
198+
</listitem>
199+
</varlistentry>
200+
</variablelist>
201+
</para>
202+
</refsect2>
203+
</refsynopsisdiv>
204+
205+
<refsect1 id="R1-SQL-ALTERSEQUENCE-1">
206+
<title>
207+
Description
208+
</title>
209+
210+
<para>
211+
See <xref linkend="SQL-CREATESEQUENCE"> for limitations, and uses
212+
of sequences.
213+
</para>
214+
</refsect1>
215+
216+
<refsect1 id="R1-SQL-ALTERSEQUENCE-2">
217+
<title>
218+
Usage
219+
</title>
220+
<para>
221+
Restart a sequence called <literal>serial</literal>, at 105:
222+
</para>
223+
<programlisting>
224+
ALTER SEQUENCE serial RESTART WITH 105;
225+
</programlisting>
226+
</refsect1>
227+
228+
<refsect1 id="R1-SQL-ALTERSEQUENCE-5">
229+
<title>
230+
Notes
231+
</title>
232+
<para>
233+
To avoid blocking of concurrent transactions that obtain numbers from the same
234+
sequence, a nextval operation is never rolled back; that is, once a value has
235+
been fetched it is considered used, even if the transaction that did the nextval
236+
later aborts. This means that aborted transactions may leave unused "holes" in
237+
the sequence of assigned values. setval operations are never rolled back, either.
238+
</para>
239+
</refsect1>
240+
241+
242+
<refsect1 id="R1-SQL-ALTERSEQUENCE-3">
243+
<title>
244+
Compatibility
245+
</title>
246+
247+
<refsect2 id="R2-SQL-ALTERSEQUENCE-4">
248+
<refsect2info>
249+
<date>2003-03-02</date>
250+
</refsect2info>
251+
<title>
252+
SQL99
253+
</title>
254+
255+
<para>
256+
<command>ALTER SEQUENCE</command> is a <productname>PostgreSQL</productname>
257+
language extension.
258+
There is no <command>ALTER SEQUENCE</command> statement
259+
in <acronym>SQL99</acronym>.
260+
</para>
261+
</refsect2>
262+
</refsect1>
263+
</refentry>
264+
265+
<!-- Keep this comment at the end of the file
266+
Local variables:
267+
mode: sgml
268+
sgml-omittag:nil
269+
sgml-shorttag:t
270+
sgml-minimize-attributes:nil
271+
sgml-always-quote-attributes:t
272+
sgml-indent-step:1
273+
sgml-indent-data:t
274+
sgml-parent-document:nil
275+
sgml-default-dtd-file:"../reference.ced"
276+
sgml-exposed-tags:nil
277+
sgml-local-catalogs:"/usr/lib/sgml/catalog"
278+
sgml-local-ecat-files:nil
279+
End:
280+
-->

‎doc/src/sgml/ref/create_sequence.sgml

Lines changed: 11 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_sequence.sgml,v 1.30 2003/01/19 00:13:29 momjian Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_sequence.sgml,v 1.31 2003/03/20 07:02:07 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -22,7 +22,7 @@ PostgreSQL documentation
2222
</refsynopsisdivinfo>
2323
<synopsis>
2424
CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">seqname</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
25-
[ MINVALUE <replaceable class="parameter">minvalue</replaceable> ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> ]
25+
[ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
2626
[ START [ WITH ] <replaceable class="parameter">start</replaceable> ] [ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
2727
</synopsis>
2828

@@ -63,7 +63,7 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">seqname</rep
6363
<listitem>
6464
<para>
6565
The
66-
<option>INCREMENT <replaceable class="parameter">increment</replaceable></option>
66+
<option>INCREMENT BY <replaceable class="parameter">increment</replaceable></option>
6767
clause is optional. A positive value will make an
6868
ascending sequence, a negative one a descending sequence.
6969
The default value is one (1).
@@ -73,26 +73,30 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">seqname</rep
7373

7474
<varlistentry>
7575
<term><replaceable class="parameter">minvalue</replaceable></term>
76+
<term>NO MINVALUE</term>
7677
<listitem>
7778
<para>
7879
The optional clause <option>MINVALUE
7980
<replaceable class="parameter">minvalue</replaceable></option>
8081
determines the minimum value
81-
a sequence can generate. The defaults are 1 and -2^63-1 for
82+
a sequence can generate. If this clause is not supplied or <option>NO MINVALUE</option>
83+
is specified, then defaults will be used. The defaults are 1 and -2^63-1 for
8284
ascending and descending sequences, respectively.
8385
</para>
8486
</listitem>
8587
</varlistentry>
8688

8789
<varlistentry>
8890
<term><replaceable class="parameter">maxvalue</replaceable></term>
91+
<term>NO MAXVALUE</term>
8992
<listitem>
9093
<para>
9194
The optional clause <option>MAXVALUE
9295
<replaceable class="parameter">maxvalue</replaceable></option>
9396
determines the maximum
94-
value for the sequence. The defaults are 2^63-1 and -1 for
95-
ascending and descending sequences, respectively.
97+
value for the sequence. If this clause is not supplied or
98+
<option>NO MAXVALUE</option> is specified, then default values will be used.
99+
The defaults are 2^63-1 and -1 for ascending and descending sequences, respectively.
96100
</para>
97101
</listitem>
98102
</varlistentry>
@@ -101,7 +105,7 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">seqname</rep
101105
<term><replaceable class="parameter">start</replaceable></term>
102106
<listitem>
103107
<para>
104-
The optional <option>START
108+
The optional <option>START WITH
105109
<replaceable class="parameter">start</replaceable>
106110
clause</option> enables the sequence to begin anywhere.
107111
The default starting value is

‎doc/src/sgml/reference.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!-- reference.sgml
2-
$Header: /cvsroot/pgsql/doc/src/sgml/reference.sgml,v 1.41 2002/12/06 05:00:10 momjian Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/reference.sgml,v 1.42 2003/03/20 07:02:07 momjian Exp $
33

44
PostgreSQL Reference Manual
55
-->
@@ -49,6 +49,7 @@ PostgreSQL Reference Manual
4949
&alterDatabase;
5050
&alterDomain;
5151
&alterGroup;
52+
&alterSequence;
5253
&alterTable;
5354
&alterTrigger;
5455
&alterUser;

0 commit comments

Comments
 (0)