CREATE SEQUENCE

Define a new sequence generator.

Summary

CREATE [TEMPORARY | TEMP] SEQUENCE name
        [INCREMENT [BY] value]
        [MINVALUE minvalue | NO MINVALUE]
        [MAXVALUE maxvalue | NO MAXVALUE]
        [START [ WITH ] start]
        [CACHE cache]
        [[NO] CYCLE]
        [OWNED BY { table.column | NONE }]

describe

CREATE SEQUENCE Creates a new sequence generator. This involves creating and initializing new special single row tables. The generator will be owned by the user who issued the command.

If a schema name is specified, a sequence is created in the specified schema. Otherwise, it will be created in the current mode. Temporary sequences exist in special schemas, so the schema name may not be given when creating a temporary sequence. The sequence name must be different from the name of any other sequence, table, index, view, or external table in the same schema.

After creating a sequence, you can use the nextval() function to operate on the sequence. For example, to insert a row into a table that gets the next value of the sequence:

INSERT INTO distributors VALUES (nextval('myserial'), 'acme');

You can also use the function setval() to operate on sequences, but only for queries that do not operate on distributed data. For example, allow the following query because it resets the sequence counter value of the sequence generator process on the Master:

SELECT setval('myserial', 201);

But the following query will be rejected in the YMatrix database because it operates on distributed data:

INSERT INTO product VALUES (setval('myserial', 201), 'gizmo');

In a regular (non-distributed) database, functions that operate on sequences go to the local sequence list to get the value as needed. However, remember that in the YMatrix database, each Segment is its own different database process. Therefore, a Segment requires a real single point to get the sequence value so that all Segments are incremented correctly and the sequences are advanced in the correct order. The sequence server process runs on the Master and is the real point of the sequence in the YMatrix distributed database. Segment gets sequence values from Master at runtime.

Due to this distributed sequence design, there are some limitations on the functions of sequence operations in the YMatrix database:

The lastval() and currval() functions are not supported.
setval() can only be used to set the value of the sequence generator on the Master, and cannot be used in subqueries to update records on distributed table data.
Depending on the query, nextval() sometimes gets a block of value from the Master for use by the Segment. Therefore, if all blocks are not needed at the Segment level, the values in the sequence may sometimes be skipped. Note that a regular PostgreSQL database can do this as well, so this is not unique to YMatrix databases.

Although you cannot update the sequence directly, you can use a query like the following:

SELECT * FROM sequence_name;

Check the sequence's parameters and current status. In particular, the last_value field of the sequence displays the last value assigned by any session.

Parameters

TEMPORARY | TEMP

If specified, only the sequence object is created for this session and automatically deleted when the session exits. Existing permanent sequences with the same name are not visible when the temporary sequence exists (in this session), unless they are referenced with a schema-qualified name.

name

The name of the sequence to be created (can be specified by the schema).

Increment

Specifies which value to add to the current sequence value to create a new value. Positive values will form an ascending order, and negative values will form a descending order. The default value is 1.

minvalue

NO MINVALUE

Determines the minimum value that a sequence can generate. If this clause is not provided or NO MINVALUE is specified, the default value is used. The default values for ascending and descending are 1 and -263-1, respectively.

maxvalue

NO MAXVALUE

Determines the maximum value of the sequence. If this clause is not provided or NO MAXVALUE is specified, the default value is used. The default values for ascending and descending are 263-1 and -1, respectively.

start

Allow sequences to start anywhere. The default start values are the minimum value in ascending order and the maximum value in descending order.

cache

Specify how many sequence numbers to pre-allocate and store them in memory to speed up access. The minimum (default) value is 1 (no cache).

CYCLE

NO CYCLE

Sequence wrapping is allowed when the maximum value (increment) or minimum value (increment) is reached. If the limit is reached, the next number generated will be the minimum (ascending order) or the maximum (ascending order). If NO CYCLE is specified, any call to nextval() will return an error after the sequence reaches its maximum. If not specified, the default is NO CYCLE.

OWNED BY table.column

OWNED BY NONE

Associate the sequence with a specific table column so that if the column (or its entire table) is deleted, the sequence will also be automatically deleted. The specified table must have the same owner and the same schema as the sequence. OWNED BY NONE (default) specifies that this association does not exist.

Note

The sequence is based on bigint arithmetic, so the range cannot exceed the range of octet integers (-9223372036854775808 to 9223372036854775807).

Although multiple sessions are guaranteed to assign different sequence values, these values may be generated in order when all sessions are considered. For example, session A might retain the value 1..10 and return nextval = 1, and then session B might retain the value 11..20 and return nextval = 11 before session A generates nextval = 2. So you should just assume that nextval() values are all different, rather than generating them purely in order. Likewise, last_value will reflect the latest value retained by any session, whether nextval() has returned it or not.

Example

Create a sequence called myseq:

CREATE SEQUENCE myseq START 101;

Insert a row in the table to get the next value of the sequence named idseq:

INSERT INTO distributors VALUES (nextval('idseq'), 'acme');

Reset the sequence counter value on the Master:

SELECT setval('myseq', 201);

Illegal use of setval() in a YMatrix database (set sequence values on distributed data):

INSERT INTO product VALUES (setval('myseq', 201), 'gizmo');

compatibility

CREATE SEQUENCE complies with SQL standards, except for the following:

The ASdata_type expression specified in the SQL standard is not supported.
Use the nextval() function instead of the NEXT VALUE FOR expression specified in the SQL standard to get the next value.
The OWNED BY clause is a YMatrix database extension.