diff options
Diffstat (limited to 'doc/src/sgml/ref/create_sequence.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_sequence.sgml | 96 |
1 files changed, 36 insertions, 60 deletions
diff --git a/doc/src/sgml/ref/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml index 953ca916849..334d45dd8b9 100644 --- a/doc/src/sgml/ref/create_sequence.sgml +++ b/doc/src/sgml/ref/create_sequence.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_sequence.sgml,v 1.20 2001/09/13 15:55:24 petere Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_sequence.sgml,v 1.21 2001/11/17 22:20:34 tgl Exp $ Postgres documentation --> @@ -15,7 +15,7 @@ Postgres documentation CREATE SEQUENCE </refname> <refpurpose> - define a new sequence + define a new sequence generator </refpurpose> </refnamediv> <refsynopsisdiv> @@ -42,8 +42,8 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">seqname</rep <term>TEMPORARY or TEMP</term> <listitem> <para> - If specified, the sequence is created only for this session, and is - automatically dropped on session exit. + If specified, the sequence object is created only for this session, + and is automatically dropped on session exit. Existing permanent sequences with the same name are not visible (in this session) while the temporary sequence exists. </para> @@ -141,6 +141,8 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">seqname</rep <replaceable class="parameter">minvalue</replaceable> or <replaceable class="parameter">maxvalue</replaceable>, respectively. + Without CYCLE, after the limit is reached <function>nextval</> calls + will return an error. </para> </listitem> </varlistentry> @@ -222,81 +224,57 @@ ERROR: DefineSequence: MINVALUE (<replaceable class="parameter">min</replaceabl </title> <para> <command>CREATE SEQUENCE</command> will enter a new sequence number generator - into the current data base. This involves creating and initializing a + into the current database. This involves creating and initializing a new single-row table with the name <replaceable class="parameter">seqname</replaceable>. The generator will be owned by the user issuing the command. </para> <para> - After a sequence is created, you may use the function - <function>nextval('<replaceable class="parameter">seqname</replaceable>')</function> - to get a new number from the sequence. - The function - <function>currval('<replaceable class="parameter">seqname</replaceable>')</function> - may be used to determine the number returned by the last call to - <function>nextval('<replaceable class="parameter">seqname</replaceable>')</function> - for the specified sequence in the current session. - The function - <function>setval('<replaceable class="parameter">seqname</replaceable>', - <replaceable class="parameter">newvalue</replaceable>)</function> - may be used to set the current value of the specified sequence. - The next call to - <function>nextval('<replaceable class="parameter">seqname</replaceable>')</function> - will return the given value plus the sequence increment. + After a sequence is created, you use the functions + <function>nextval</function>, + <function>currval</function> and + <function>setval</function> + to operate on the sequence. These functions are documented in + the <citetitle>User's Guide</citetitle>. </para> <para> - Use a query like + Although you cannot update a sequence directly, you can use a query like <programlisting> SELECT * FROM <replaceable>seqname</replaceable>; </programlisting> - to examine the parameters of a sequence. - - As an alternative to fetching the - parameters from the original definition as above, you can use - - <programlisting> -SELECT last_value FROM <replaceable>seqname</replaceable>; - </programlisting> - - to obtain the last value allocated by any backend. - </para> - - <para> - 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 <quote>holes</quote> - in the sequence of assigned values. setval operations are never - rolled back, either. + to examine the parameters and current state of a sequence. In particular, + the <literal>last_value</> field of the sequence shows the last value + allocated by any backend process. (Of course, this value may be obsolete + by the time it's printed, if other processes are actively doing + <function>nextval</> calls.) </para> <caution> <para> - Unexpected results may be obtained if a cache setting greater than one + Unexpected results may be obtained if a <replaceable class="parameter">cache</replaceable> setting greater than one is used for a sequence object that will be used concurrently by multiple backends. Each backend will allocate and cache successive sequence values during one access to the sequence object and increase the sequence - object's last_value accordingly. Then, the next cache-1 uses of nextval + object's <literal>last_value</> accordingly. Then, the next <replaceable class="parameter">cache</replaceable>-1 uses of <function>nextval</> within that backend simply return the preallocated values without touching - the shared object. So, numbers allocated but not used in the current session - will be lost. Furthermore, although multiple backends are guaranteed to + the shared object. So, any numbers allocated but not used within a session + will be lost when that session ends. Furthermore, although multiple backends are guaranteed to allocate distinct sequence values, the values may be generated out of - sequence when all the backends are considered. (For example, with a cache + sequence when all the backends are considered. (For example, with a <replaceable class="parameter">cache</replaceable> setting of 10, backend A might reserve values 1..10 and return nextval=1, then backend B might reserve values 11..20 and return nextval=11 before backend - A has generated nextval=2.) Thus, with a cache setting of one it is safe - to assume that nextval values are generated sequentially; with a cache - setting greater than one you should only assume that the nextval values + A has generated nextval=2.) Thus, with a <replaceable class="parameter">cache</replaceable> setting of one it is safe + to assume that <function>nextval</> values are generated sequentially; with a <replaceable class="parameter">cache</replaceable> + setting greater than one you should only assume that the <function>nextval</> values are all distinct, not that they are generated purely sequentially. - Also, last_value will reflect the latest value reserved by any backend, - whether or not it has yet been returned by nextval. - Another consideration is that a setval executed on such a sequence + Also, <literal>last_value</> will reflect the latest value reserved by any backend, + whether or not it has yet been returned by <function>nextval</>. + Another consideration is that a <function>setval</> executed on such a sequence will not be noticed by other backends until they have used up any preallocated values they have cached. </para> @@ -313,7 +291,8 @@ SELECT last_value FROM <replaceable>seqname</replaceable>; Use <command>DROP SEQUENCE</command> to remove a sequence. </para> <para> - Each backend uses its own cache to store preallocated numbers. + When <replaceable class="parameter">cache</replaceable> is greater than + one, each backend uses its own cache to store preallocated numbers. Numbers that are cached but not used in the current session will be lost, resulting in <quote>holes</quote> in the sequence. </para> @@ -333,7 +312,7 @@ CREATE SEQUENCE serial START 101; <para> Select the next number from this sequence: <programlisting> -SELECT NEXTVAL ('serial'); +SELECT nextval('serial'); nextval ------- @@ -343,19 +322,16 @@ nextval <para> Use this sequence in an INSERT: <programlisting> -INSERT INTO distributors VALUES (NEXTVAL('serial'),'nothing'); +INSERT INTO distributors VALUES (nextval('serial'), 'nothing'); </programlisting> </para> <para> - Set the sequence value after a COPY FROM: + Update the sequence value after a COPY FROM: <programlisting> -CREATE FUNCTION distributors_id_max() RETURNS INT4 - AS 'SELECT max(id) FROM distributors' - LANGUAGE 'sql'; BEGIN; COPY distributors FROM 'input_file'; - SELECT setval('serial', distributors_id_max()); + SELECT setval('serial', max(id)) FROM distributors; END; </programlisting> </para> |