From 2043340b8702c1ce94609db7b5aaf3a79c3c9b45 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 17 Nov 2001 22:20:34 +0000 Subject: Expand documentation for sequence functions (nextval and friends). Place it in the expected place in the User's Guide, rather than hiding it in the command reference page for CREATE SEQUENCE. --- doc/src/sgml/ref/create_sequence.sgml | 96 +++++++++++++---------------------- 1 file changed, 36 insertions(+), 60 deletions(-) (limited to 'doc/src/sgml/ref/create_sequence.sgml') 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 @@ @@ -15,7 +15,7 @@ Postgres documentation CREATE SEQUENCE - define a new sequence + define a new sequence generator @@ -42,8 +42,8 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE seqnameTEMPORARY or TEMP - 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. @@ -141,6 +141,8 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE seqnameminvalue or maxvalue, respectively. + Without CYCLE, after the limit is reached nextval calls + will return an error. @@ -222,81 +224,57 @@ ERROR: DefineSequence: MINVALUE (min CREATE SEQUENCE 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 seqname. The generator will be owned by the user issuing the command. - After a sequence is created, you may use the function - nextval('seqname') - to get a new number from the sequence. - The function - currval('seqname') - may be used to determine the number returned by the last call to - nextval('seqname') - for the specified sequence in the current session. - The function - setval('seqname', - newvalue) - may be used to set the current value of the specified sequence. - The next call to - nextval('seqname') - will return the given value plus the sequence increment. + After a sequence is created, you use the functions + nextval, + currval and + setval + to operate on the sequence. These functions are documented in + the User's Guide. - Use a query like + Although you cannot update a sequence directly, you can use a query like SELECT * FROM seqname; - to examine the parameters of a sequence. - - As an alternative to fetching the - parameters from the original definition as above, you can use - - -SELECT last_value FROM seqname; - - - to obtain the last value allocated by any backend. - - - - 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. + to examine the parameters and current state of a sequence. In particular, + the 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 + nextval calls.) - Unexpected results may be obtained if a cache setting greater than one + Unexpected results may be obtained if a cache 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 last_value accordingly. Then, the next cache-1 uses of 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 cache 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 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 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, 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 will not be noticed by other backends until they have used up any preallocated values they have cached. @@ -313,7 +291,8 @@ SELECT last_value FROM seqname; Use DROP SEQUENCE to remove a sequence. - Each backend uses its own cache to store preallocated numbers. + When cache 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 holes in the sequence. @@ -333,7 +312,7 @@ CREATE SEQUENCE serial START 101; Select the next number from this sequence: -SELECT NEXTVAL ('serial'); +SELECT nextval('serial'); nextval ------- @@ -343,19 +322,16 @@ nextval Use this sequence in an INSERT: -INSERT INTO distributors VALUES (NEXTVAL('serial'),'nothing'); +INSERT INTO distributors VALUES (nextval('serial'), 'nothing'); - Set the sequence value after a COPY FROM: + Update the sequence value after a COPY FROM: -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; -- cgit v1.2.3