CREATE SEQUENCE

CREATE SEQUENCE
Prev	Up	9.3. SQL Commands	Home	Next

CREATE SEQUENCE — define a new sequence generator

Synopsis

CREATE SEQUENCE [ IF NOT EXISTS ] name
    [ AS data_type ]
    [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ]
    WITH ( [ global ],
           [ block_size = block_size ]
         )

Description

Shardman extensions to the CREATE SEQUENCE command enable creation of global sequence number generators. This command creates an ordinary PostgreSQL sequence on all nodes in a cluster and records the sequence parameters in the global sequence state dictionary. (See Section 2.1.7 for details.)

After a global sequence is created, the shardman.next_value function can be used to generate sequence values that are guaranteed be unique across the entire cluster. The standard sequence manipulation functions (e.g. nextval and setval) must not be used on global sequences, and such use may lead to unexpected results.

Parameters

In addition to the parameters recognized by PostgreSQL, the following parameters are supported by Shardman.

global: If specified, the sequence object is created as a Shardman-managed global sequence.
block_size: The "block size" parameter is the number of elements allocated for a local sequence. The default block size equals 65536.

Notes

Global sequences are meant to behave similarly to ordinary PostgreSQL sequences (see CREATE SEQUENCE) with some limitations, the most important one being that a global sequence is always increasing. There's no support for negative increment values or wraparound (as in CYCLE), which also means there's practically no difference between a minimum sequence value and its starting value, so both parameters cannot be provided at the same time to avoid confusion.

Just like with regular sequence objects, the DROP SEQUENCE command removes a global sequence and the ALTER SEQUENCE command allows to change some of the global sequence parameters.

Examples

Create a global sequence called serial.

CREATE SEQUENCE serial MINVALUE 100 WITH (global);

Select the next number from this sequence:

SELECT shardman.next_value('serial');

 next_value
------------
        100
(1 row)