CREATE TRANSITION, DROP TRANSITION

Declare the states a column may start in, the moves it may make and the states a row may be deleted from, enforced on every write.

A Transition declares the allowed life of one column’s value: the states a row may be inserted in, the old => new moves an update may make, and the states a row may be deleted from. The engine checks every write against it and refuses one that leaves the declared graph. See Transitions for how it works.

Syntax

CREATE TRANSITION <name> ON <table>(<column>)
    ON INSERT (<state> [, ...])
    ON UPDATE (<state> => <state> [, ...])
    [ON DELETE FROM (<state> [, ...])]

DROP TRANSITION <name>

SHOW TRANSITIONS

Arguments

Argument	Description
`<name>`	Name of the Transition. It must be unique.
`<table>(<column>)`	The governed column. A `NOT NULL` signed integer, `char(N)` or `varchar(N)` column, not the primary key.
`ON INSERT (...)`	Required. The states an `INSERT` may write.
`ON UPDATE (...)`	Required. The allowed moves, each written `old => new`.
`ON DELETE FROM (...)`	Optional. The states a row may be deleted from. Left out, every delete is allowed.
`<state>`	A string literal for a text column, an integer literal for an integer column.

The clauses must appear in the order ON INSERT, ON UPDATE, ON DELETE FROM.

Examples

Declare an order lifecycle

CREATE TABLE orders (id int64, customer varchar(32), status varchar(16));

CREATE TRANSITION order_status ON orders(status)
  ON INSERT ('NEW')
  ON UPDATE ('NEW' => 'CHECKED', 'CHECKED' => 'FINALIZED',
             'NEW' => 'CANCELLED', 'CHECKED' => 'CANCELLED')
  ON DELETE FROM ('NEW', 'CANCELLED');

CREATED TRANSITION name=order_status transition_id=1 on=orders column=status rows=0

rows is the number of existing rows the statement checked.

A refused write

kdbs> INSERT INTO orders VALUES ('kim', 'NEW')
INSERTED oid=4000 id=1 page=131 slot=0
kdbs> UPDATE orders SET status = 'FINALIZED' WHERE id = 1
ERR TRANSITION_VIOLATION retryable=0 transition 'order_status' on column 'status': 'NEW' => 'FINALIZED' is not a declared edge, row id 1 (byte 27)

Refused declarations

Each is refused with the byte of the part at fault, and nothing is created:

kdbs> CREATE TRANSITION bad ON orders(status) ON INSERT ('NEW')
ERR CREATE TRANSITION requires ON UPDATE, in the order ON INSERT, ON UPDATE, ON DELETE FROM (byte 57)
kdbs> CREATE TRANSITION bad2 ON orders(id) ON INSERT (1) ON UPDATE (1 => 2)
ERR a Transition cannot govern the tuple key 'id': it is never updated (byte 33)
kdbs> CREATE TRANSITION bad3 ON orders(customer) ON INSERT ('A') ON UPDATE ('A' => 'B', 'C' => 'D')
ERR state 'C' is reachable from no ON INSERT state (byte 82)
kdbs> CREATE TRANSITION order_status2 ON orders(status) ON INSERT ('NEW') ON UPDATE ('NEW' => 'DONE')
ERR column 'status' already carries transition 'order_status' (byte 42)

An existing row outside the state set refuses the declaration:

kdbs> CREATE TABLE tasks (id int64, state int32)
CREATED oid=4004
kdbs> INSERT INTO tasks VALUES (5)
INSERTED oid=4004 id=1 page=135 slot=0
kdbs> CREATE TRANSITION task_state ON tasks(state) ON INSERT (1) ON UPDATE (1 => 2)
ERR TRANSITION_VIOLATION retryable=0 transition 'task_state' on column 'state': an existing row is in state 5, which the declaration does not name, row id 1

Remove a Transition

kdbs> DROP TRANSITION order_status
DROPPED TRANSITION name=order_status transition_id=1

Dropping it lifts the refusal of PURGE on the table.

How it works

CREATE TRANSITION scans the table once and refuses if a row’s state is outside the state set. It does not check how existing rows reached their states.
While it runs it holds the table exclusively. If a write to the table is in flight, it is refused with the retryable TXN_CONFLICT.
A string state compares byte for byte. An integer state compares by value.
SHOW TRANSITIONS lists every Transition with its table, column and declaration.

Limitations

A declaration is refused for an unknown or system table, an unknown column, the primary key, a nullable column or one of another type, a missing ON INSERT or ON UPDATE, a malformed =>, a literal of the wrong kind or longer than char(N), a state no walk from an initial state reaches, a second Transition on the column, or a name already taken.
One Transition per column.
CREATE TRANSITION and DROP TRANSITION are not undone by ROLLBACK.
PURGE is refused on a table that carries a Transition.
DROP TABLE of a table with a Transition is refused inside an explicit transaction. In autocommit it drops the table’s Transitions too.