Enforce limits per group

Use an assertion when a rule must hold for every group of rows, such as “at most 4 tickets per flight” or “at most 46 kg of checked baggage per passenger”. The engine checks the rule before it admits each INSERT and UPDATE, on every core, so the application does not need to count rows first. This guide designs a bound, tests a violation, monitors it, and then changes and removes it.

The examples use the sample table tickets(id, flight_id, passenger_id, seat, baggage_kg, checked_in, note). All output on this page was produced by running the statements on the current engine.

Before you begin

• You need a table whose rows carry the columns that define a group. Here a group is flight_id for a count limit, and passenger_id for a baggage limit.
• CREATE ASSERTION and DROP ASSERTION are DDL. On a server with auth = scram they need the admin role.
• Know the limits of the rule language. Only upper bounds on COUNT(*) or SUM(<column>) are supported. See CREATE ASSERTION, DROP ASSERTION.

Step 1: Design the bound

Answer three questions:

1. Which columns define a group? These go in GROUP BY (...), in parentheses. A group exists while it holds at least one row.
2. What is limited? The number of rows (COUNT(*)) or the sum of one column (SUM(<column>)). In this build, SUM takes an int64 column.
3. What is the bound? Write <= N or < N. The engine stores < N as <= N - 1, and error messages show the stored bound.

Rules that cannot be written as an assertion are refused when you create them, with the byte position of the refused part. This includes lower bounds (>, >=), equality (=), MIN, MAX, AVG, COUNT(<column>) and DISTINCT.

Step 2: Create the assertion

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

The sample table already declares seat_limit, so drop it first, or the CREATE is refused with assertion "seat_limit" already exists:

kdbs> DROP ASSERTION seat_limit;
DROPPED ASSERTION name=seat_limit assertion_id=1
kdbs> CREATE ASSERTION seat_limit ON tickets GROUP BY (flight_id) CHECK COUNT(*) <= 4;
CREATED ASSERTION name=seat_limit assertion_id=3 on=tickets cabin_root=145 rows=9 groups=3 enforcing=1

CREATE ASSERTION scans the table once. rows= and groups= show what it counted, and enforcing=1 means writes are now checked.

If existing data already breaks the bound, the statement fails and nothing is created:

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

A rule outside the supported class is refused with its position:

kdbs> CREATE ASSERTION bad ON tickets GROUP BY (flight_id) CHECK COUNT(*) = 4;
ERR NOT_IMPLEMENTED retryable=0 equality assertions (=) are not supported (byte 68); enforcing = means enforcing a lower bound, which v1 excludes, and reading it as <= would enforce something other than what was written (docs/spec/assertion.md AS11)

Step 3: Test a violation

Fill one group to its bound, then try one more row. In tickets, flight 3 already has three tickets.

kdbs> INSERT INTO tickets VALUES (3, 4, '43A', 0, 0, NULL);
INSERTED oid=4019 id=10 page=139 slot=9
kdbs> INSERT INTO tickets VALUES (3, 5, '43B', 0, 0, NULL);
ERR ASSERTION_VIOLATION retryable=0 assertion "seat_limit" group (flight_id=3): COUNT(*) would exceed bound 4
kdbs> INSERT INTO tickets VALUES (4, 5, '20A', 0, 0, NULL);
INSERTED oid=4019 id=11 page=139 slot=10

The refused row is not written. Other groups are not affected.

UPDATE is checked by how much it changes a group. Here the baggage_cap assertion (SUM(baggage_kg) <= 46 per passenger_id) refuses an update that raises one weight too far. Passenger 1 is already at 46 kg:

kdbs> UPDATE tickets SET baggage_kg = 30 WHERE id = 1;
ERR ASSERTION_VIOLATION retryable=0 assertion "baggage_cap" group (passenger_id=1): SUM(baggage_kg) would exceed bound 46

DELETE is never checked, because removing rows cannot break an upper bound.

Handle the error in the application

ASSERTION_VIOLATION has retryable=0: sending the same statement again fails the same way. Report it to the user, or change the request.

Inside an explicit transaction, the violation aborts the transaction. Every later statement is refused until you send ROLLBACK:

kdbs> BEGIN TRANSACTION;
BEGIN trx_id=19 isolation=read committed durability=group
kdbs> INSERT INTO tickets VALUES (4, 1, '20B', 5, 0, NULL);
ERR ASSERTION_VIOLATION retryable=0 assertion "baggage_cap" group (passenger_id=1): SUM(baggage_kg) would exceed bound 46
kdbs> COMMIT;
ERR current transaction is aborted; commands are ignored until ROLLBACK
kdbs> ROLLBACK;
ROLLBACK

Step 4: Monitor with SHOW ASSERTIONS

kdbs> SHOW ASSERTIONS;
assertions=2
assertion_id=2 name=baggage_cap rel=tickets enforcing=1 checks=14 violations=2 reserved=11 aborted=0 def=CREATE ASSERTION baggage_cap ON tickets GROUP BY (passenger_id) CHECK SUM(baggage_kg) <= 46
assertion_id=3 name=seat_limit rel=tickets enforcing=1 checks=3 violations=1 reserved=2 aborted=0 def=CREATE ASSERTION seat_limit ON tickets GROUP BY (flight_id) CHECK COUNT(*) <= 4

Field	Meaning
enforcing	1 when writes are checked.
checks	Writes checked against the assertion.
violations	Writes refused. A rising count means clients are reaching the limit.
reserved, aborted	Live counters the engine keeps for admitted and rolled-back reservations.
def	The declaration as stored.

After a restart, assertions are rebuilt and enforce at once. SHOW ASSERTIONS reports enforcing=0 only for an assertion whose state could not be rebuilt.

Step 5: Change the bound

There is no ALTER ASSERTION. Drop the assertion and create it again with the new bound:

kdbs> DROP ASSERTION seat_limit;
DROPPED ASSERTION name=seat_limit assertion_id=3
kdbs> CREATE ASSERTION seat_limit ON tickets GROUP BY (flight_id) CHECK COUNT(*) <= 5;
CREATED ASSERTION name=seat_limit assertion_id=5 on=tickets cabin_root=147 rows=11 groups=4 enforcing=1

Between the drop and the new create, nothing limits the group. Assertion DDL is not transactional, so you cannot wrap the two statements in one transaction to close that gap.

An assertion also blocks some changes to its table:

kdbs> ALTER TABLE tickets RENAME TO bookings;
ERR assertion 'baggage_cap' stores its declaration against this relation's current names; DROP ASSERTION, rename, then re-declare it
kdbs> DROP TABLE tickets;
ERR assertion 'baggage_cap' is declared on this relation; DROP ASSERTION first

Verify

• SHOW ASSERTIONS lists the assertion with enforcing=1 and the def= you expect.
• A test write that exceeds the bound returns ASSERTION_VIOLATION and names the group and the bound.

Clean up

kdbs> DROP ASSERTION seat_limit;
DROPPED ASSERTION name=seat_limit assertion_id=5

Dropping a name that does not exist is an error:

kdbs> DROP ASSERTION nosuch;
ERR no assertion named 'nosuch' (byte 15)

Next steps

• Assertions: how the engine keeps a running value per group.
• CREATE ASSERTION, DROP ASSERTION: full syntax.
• Errors and refusals: how to handle each error token.