CREATE ASSERTION, DROP ASSERTION

Declare an upper bound on COUNT(*) or SUM(column) per group, enforced on every write.

An assertion declares a limit that every group of rows in a table must stay within, for example “at most 180 bookings per flight”. The engine checks the limit before it admits each write, and refuses a write that would break it. The application does not count rows before it writes.

Syntax

CREATE ASSERTION <name> ON <table> GROUP BY (<column> [, ...])
    CHECK { COUNT(*) | SUM(<column>) } { < | <= } <non-negative integer>

DROP ASSERTION <name>

Arguments

Argument	Description
`<name>`	Name of the assertion. It must be unique.
`<table>`	The table the assertion applies to.
`GROUP BY (<column>, ...)`	The columns that define a group. Required, and written in parentheses (unlike `GROUP BY` in `SELECT`).
`COUNT(*)`	Bounds the number of rows in each group.
`SUM(<column>)`	Bounds the sum of a column in each group.
`<` or `<=`	The bound is an upper bound. `< N` is enforced as `<= N - 1`.
`<non-negative integer>`	The bound, as an integer literal.

Examples

Limit rows per group

This is how the sample seat_limit is defined: at most 4 tickets for each flight.

CREATE ASSERTION seat_limit ON tickets GROUP BY (flight_id) CHECK COUNT(*) <= 4;

Limit a sum per group

CREATE ASSERTION load_cap ON tickets GROUP BY (flight_id) CHECK SUM(baggage_kg) < 80;

CREATED ASSERTION name=load_cap assertion_id=3 on=tickets cabin_root=145 rows=9 groups=3 enforcing=1

A refused write

A write that would take a group past its bound is refused, and the error names the assertion, the group and the enforced bound. In the sample tickets table, flight 1 already has 4 tickets under seat_limit (COUNT(*) <= 4), and passenger 1 already carries 46 kg under baggage_cap (SUM(baggage_kg) <= 46):

kdbs> INSERT INTO tickets VALUES (1, 5, '16C', 0, 0, NULL)
ERR ASSERTION_VIOLATION retryable=0 assertion "seat_limit" group (flight_id=1): COUNT(*) would exceed bound 4

kdbs> INSERT INTO tickets VALUES (4, 1, '01A', 10, 0, NULL)
ERR ASSERTION_VIOLATION retryable=0 assertion "baggage_cap" group (passenger_id=1): SUM(baggage_kg) would exceed bound 46

kdbs> INSERT INTO tickets VALUES (4, 1, '01A', 0, 0, NULL)
INSERTED oid=4019 id=10 page=139 slot=9

The first write adds a fifth ticket to flight 1. The second adds 10 kg to a passenger at the limit. The third adds no weight, and it goes to flight 4, so it is admitted.

An assertion that the existing rows already break is refused when it is created:

kdbs> CREATE ASSERTION gate_cap ON tickets GROUP BY (flight_id) CHECK COUNT(*) <= 3
ERR ASSERTION_VIOLATION retryable=0 assertion "gate_cap" group (flight_id=1): COUNT(*) would exceed bound 3

Remove an assertion

DROP ASSERTION load_cap;

How it works

CREATE ASSERTION scans the table once. If existing data already breaks the bound, the statement fails.
The engine keeps a running count or sum for every group. Checking a write reads only that group’s running value, so the cost of a check does not grow with the size of the group.
INSERT is checked before the row’s id is allocated. UPDATE is checked by how much it changes the group.
On an instance with several cores, an assertion is one limit for the whole instance: two sessions on different cores that both try to take the last place in a group get one success and one ASSERTION_VIOLATION.
SHOW ASSERTIONS lists every assertion with enforcing=, and counters for checks and violations.

Limitations

The supported rules are exactly the ones the syntax above allows. Everything else is refused, with the byte position of the part that was refused:

Lower bounds (>, >=) and equality (=).
MIN, MAX, AVG, COUNT(<column>) and DISTINCT.
DEFERRABLE, NOT DEFERRABLE, INITIALLY ... and NOT VALID.
!=, a negative bound, or a bound that is not a literal.
COUNT(*) <= 0 and COUNT(*) < 1, because a group exists only while it holds a row.

Try it. The sandbox starts with two assertions on the tickets table, seat_limit and baggage_cap. Run SHOW ASSERTIONS; to see them.