Sequences

A sequence is a named counter that returns one integer at a time.

Use a sequence when the number itself has meaning to people or external systems: an invoice number, an order number, a ticket number, or a short reference printed on a receipt.

Do not use a sequence just because a table needs a primary key. For primary keys, prefer OID, UUID, or gen_uuid_v7():

CREATE TABLE orders (
  id OID PRIMARY KEY NOT NULL DEFAULT (gen_id()),
  order_no INT64 GENERATED BY DEFAULT AS IDENTITY,
  total FLOAT64 NOT NULL
);

The id is the database key. It is generated independently by each writer, so when a table's key range is split, the ranges can keep accepting writes independently. The order_no is the human-facing number.

That distinction matters in a distributed database. A sequence is one logical counter. Inserts that need a value from it coordinate through that counter, so a sequence can become the table's point of contention. CACHE can reduce how often the counter commits, but it does not make a sequence as independent as an OID or UUID generator.

Quick Start

Create a sequence, then use nextval as a column default:

CREATE SEQUENCE order_no START WITH 1000;

CREATE TABLE orders (
  id OID PRIMARY KEY NOT NULL DEFAULT (gen_id()),
  no INT64 NOT NULL DEFAULT (nextval('order_no')),
  total FLOAT64 NOT NULL
);

INSERT INTO orders (total) VALUES (19.90);
SELECT no, total FROM orders;

If the sequence is used only by one table column, an identity column is usually the cleaner spelling:

CREATE TABLE invoices (
  id OID PRIMARY KEY NOT NULL DEFAULT (gen_id()),
  invoice_no INT64 GENERATED BY DEFAULT AS IDENTITY,
  total FLOAT64 NOT NULL
);

CamusDB creates and owns the backing sequence for the identity column.

Create A Sequence

CREATE SEQUENCE [IF NOT EXISTS] name [ option ... ];

Common options:

Option	Default	Meaning
START [WITH] n	MINVALUE	First value the sequence returns.
INCREMENT [BY] n	1	Step between values. Must be positive.
MINVALUE n / NO MINVALUE	1	Lowest value the sequence may hold.
MAXVALUE n / NO MAXVALUE	none	Highest value the sequence may return.
CACHE n	1	Values reserved per durable counter update.
NO CYCLE	accepted	The only supported cycle behavior.

Example:

CREATE SEQUENCE invoice_no
  START WITH 1000
  INCREMENT BY 1
  MINVALUE 1000
  MAXVALUE 999999
  CACHE 1;

A sequence name shares a namespace with tables and views. You cannot have a table and a sequence with the same name in one database.

CYCLE is not supported. A sequence value is guaranteed unique for the lifetime of the sequence; wrapping back to the minimum would reissue values.

Use Values

Sequence functions:

Function	Meaning
nextval('s')	Advance s and return the new value.
currval('s')	Return the last value this transaction drew from s.
lastval()	Return the last value this transaction drew from any sequence.
setval('s', n [, is_called])	Move the counter.

nextval

Use nextval in a column default or in an insert value:

INSERT INTO orders (id, no, total)
VALUES (gen_id(), nextval('order_no'), 25.00);

CamusDB reserves the values a statement needs before the statement runs. That is why sequence calls are accepted only where the engine can know the count in advance:

• INSERT ... VALUES expressions.
• Column defaults used by INSERT ... VALUES.
• Column defaults used by INSERT ... SELECT.
• A SELECT with no FROM clause.

Other positions are rejected with CADB0547, including filters, joins, aggregates, stored view bodies, check constraints, and UPDATE ... SET.

currval and lastval

currval and lastval are scoped to the transaction:

BEGIN;
INSERT INTO orders (total) VALUES (19.90);
SELECT currval('order_no');
COMMIT;

Outside an explicit transaction, each statement is its own transaction. A later statement cannot ask what a previous autocommit statement drew; it gets CADB0546.

setval

Use setval to move a free-standing sequence:

SELECT setval('order_no', 5000);          -- next nextval returns 5001
SELECT setval('order_no', 5000, false);   -- next nextval returns 5000

setval is accepted only as the whole projection of a SELECT without FROM, with at most one setval call in the statement.

Moving a counter downward can make the sequence reissue values that committed rows already hold. If the sequence feeds a unique column, later inserts can fail with a duplicate-key error. Check the table first, or move the sequence above the highest stored value.

Identity Columns

Identity columns are table columns backed by owned sequences:

CREATE TABLE tickets (
  id OID PRIMARY KEY NOT NULL DEFAULT (gen_id()),
  ticket_no INT64 GENERATED ALWAYS AS IDENTITY,
  subject STRING NOT NULL
);

CREATE TABLE notes (
  id OID PRIMARY KEY NOT NULL DEFAULT (gen_id()),
  note_no INT64 GENERATED BY DEFAULT AS IDENTITY,
  body STRING NOT NULL
);

GENERATED ALWAYS AS IDENTITY means callers cannot supply their own value for the column. Name the insert columns and omit the identity column:

INSERT INTO tickets (subject) VALUES ('Boarding pass printer');

GENERATED BY DEFAULT AS IDENTITY means the sequence supplies a value only when the insert omits the column. If an insert supplies a value, CamusDB writes that value and does not advance the sequence.

CamusDB also accepts SERIAL and BIGSERIAL as shorthand for an INT64 column backed by an owned sequence:

CREATE TABLE invoices (
  id OID PRIMARY KEY NOT NULL DEFAULT (gen_id()),
  invoice_no SERIAL,
  total FLOAT64 NOT NULL
);

Owned Sequences

An identity sequence belongs to its column.

• DROP SEQUENCE on the owned sequence is rejected with CADB0548.
• Dropping the column or dropping the table with FORCE removes the sequence.
• TRUNCATE ... RESTART IDENTITY restarts the owned sequence.
• TRUNCATE ... CONTINUE IDENTITY leaves it alone.

A free-standing sequence used in a default is not owned by the table:

CREATE SEQUENCE shared_no;

CREATE TABLE a (
  id OID PRIMARY KEY NOT NULL DEFAULT (gen_id()),
  no INT64 DEFAULT (nextval('shared_no'))
);

CREATE TABLE b (
  id OID PRIMARY KEY NOT NULL DEFAULT (gen_id()),
  no INT64 DEFAULT (nextval('shared_no'))
);

Use this shape only when sharing one counter across tables is intentional.

Truncate And Identity

TRUNCATE TABLE orders CONTINUE IDENTITY;  -- default
TRUNCATE TABLE orders RESTART IDENTITY;

RESTART IDENTITY restarts only sequences owned by columns of the truncated table. It does not reset a free-standing sequence that a default calls with nextval, because another table may be using that same counter.

Change Or Drop A Sequence

ALTER SEQUENCE order_no RESTART WITH 500;
ALTER SEQUENCE order_no RESTART;
ALTER SEQUENCE order_no INCREMENT BY 10;
ALTER SEQUENCE order_no MAXVALUE 100000;
ALTER SEQUENCE order_no NO MAXVALUE;
ALTER SEQUENCE order_no RENAME TO order_number;
DROP SEQUENCE order_number;

An ALTER is checked against the definition it produces. For example, if you raise MINVALUE above the recorded START, change both together:

ALTER SEQUENCE order_no MINVALUE 100 START WITH 100;

ALTER SEQUENCE ... RESTART cannot run inside an explicit transaction. Moving a counter is immediate and cannot be undone by a later ROLLBACK.

Inspect Sequences

SHOW SEQUENCES;
SHOW SEQUENCES LIKE 'order%';
SHOW CREATE SEQUENCE order_no;
COMMENT ON SEQUENCE order_no IS 'one per customer order';

SHOW SEQUENCES reports the name, reserved position, start value, increment, minimum, maximum, cache, owning column, and comment.

The reserved_upto column is a ceiling, not necessarily the last value issued:

CREATE SEQUENCE s CACHE 1000;
SHOW SEQUENCES LIKE 's';        -- reserved_upto is NULL

SELECT nextval('s');            -- 1
SELECT nextval('s');            -- 2
SHOW SEQUENCES LIKE 's';        -- reserved_upto is 1000

With CACHE 1, the default, reserved_upto advances one value at a time. With a larger cache, it shows the top of the reserved block.

Distributed Behavior

Sequences are durable and replicated, but they are still counters. Keep these properties in mind:

• A sequence guarantees unique values for the life of the sequence.
• Sequence values are not a perfect insert order. During leadership changes, a later insert can receive a lower value than an earlier insert.
• Values are not rolled back. A failed or rolled-back transaction can leave a gap.
• CACHE improves throughput by reserving a block of values at a time. Unused values from a block can be skipped after restart, eviction, or leadership movement.
• setval, ALTER SEQUENCE ... RESTART, and TRUNCATE ... RESTART IDENTITY can wait for the storage lease, around five seconds, so stale cached blocks cannot keep issuing values.

These are normal sequence semantics, similar to PostgreSQL. They are the reason sequences are good for external numbers but not ideal as the physical key that drives high-write tables.

Branches, Backups, And Drops

Dropping a database removes its sequence counters.

Branching a database gives the branch its own counters, seeded above the source's reserved ceiling. That avoids collisions with rows inherited from the source, at the cost of a possible gap. See Database Branching.

Backups carry sequence records. Restoring a backup restores the counters with the rows, at the reserved ceiling. No manual reseeding step is required. See Backup And Restore.

Privileges

Sequence statements reuse existing database privileges:

Statement	Privilege on the database
CREATE SEQUENCE	CreateTable
DROP SEQUENCE	Drop
ALTER SEQUENCE, COMMENT ON SEQUENCE	Alter
SHOW SEQUENCES, SHOW CREATE SEQUENCE	Select
nextval, setval	Update
currval, lastval	Select

Errors

Code	Meaning
CADB0542	The name is already taken by a sequence, table, or view.
CADB0543	The named sequence does not exist.
CADB0544	The sequence has no value left to issue.
CADB0545	The sequence definition is invalid.
CADB0546	currval or lastval was called before this transaction drew a value.
CADB0547	A sequence call was written where CamusDB cannot know how many values it would draw.
CADB0548	The sequence is owned by an identity column, or another object depends on it.
CADB0535	The sequence partition had no confirmed leader for the retry window. Retry the statement.

See Also

• Tables And Columns
• Data Types
• TRUNCATE TABLE
• Database Branching
• Configuration, for sequence_retry_budget_ms.