Deleting Records

PormG provides methods for removing data while ensuring referential integrity through cascade support and safety flags for bulk operations.

Single Record Deletion

To delete records, apply filters to the objects manager and call delete().

# Delete specific records
query = M.Just_a_test_deletion.objects;
query.filter("test_result" => 10)
delete(query)

Generated SQL (PostgreSQL):

SELECT "Tb"."id" FROM "just_a_test_deletion" AS "Tb" WHERE "Tb"."test_result" = $1 FOR UPDATE
-- Parameters: [10]
DELETE FROM "just_a_test_deletion" AS "Tb" WHERE "Tb"."id" = ANY($1)
-- Parameters: [<the keys the SELECT returned>]

just_a_test_deletion has a CASCADE dependent, so its keys are read first (see A filtered delete that cascades reads its keys first).

Return Value: The function returns a tuple containing the total count of deleted rows and a dictionary of counts per table (useful when cascades are involved):

(1, Dict{String, Integer}("just_a_test_deletion" => 1))

Deleting a Fetched Row

A PormGRow you already have in hand — from get(), first(), last(), or list() — can delete itself with row.delete(). It is located by its primary key and routed through the same deletion collector as query.delete(), so cascade / on_delete handling is identical. It returns the same (total, per-table counts) tuple.

status = M.Status.objects.get("statusid" => 200)
total, counts = status.delete()
# (1, Dict{String, Integer}("status" => 1))

The row must have had its primary key projected (rows from get()/first()/last()/list() always do). The in-memory row is not mutated — its field data is stale after the delete.

Deletion with Conditions

# Delete with multiple conditions
query = M.Just_a_test_deletion.objects;
query.filter("test_result__@in" => [11, 12], "test_result2__@isnull" => true)
delete(query)

Generated SQL (PostgreSQL):

SELECT "Tb"."id" FROM "just_a_test_deletion" AS "Tb"
WHERE "Tb"."test_result" = ANY($1) AND "Tb"."test_result2" IS NULL FOR UPDATE
-- Parameters: [[11, 12]]   (SQLite and MySQL render IN (?, ?) with two values; SQLite takes no FOR UPDATE)
DELETE FROM "just_a_test_deletion" AS "Tb" WHERE "Tb"."id" = ANY($1)
-- Parameters: [<the keys the SELECT returned>]

just_a_test_deletion has a CASCADE dependent, so the filter is read once, by the key read, and the DELETE (with every statement of the cascade) is scoped by the keys it returns; see A filtered delete that cascades reads its keys first. A model with nothing to cascade to is deleted by one statement that carries the filter itself.

Using show_query=:sql reveals the statements without executing them — a String for a single statement, a Vector of Strings when the delete cascades:

sql = delete(query, show_query=:sql)
# ["SELECT \"Tb\".\"id\" FROM \"just_a_test_deletion\" AS \"Tb\" WHERE \"Tb\".\"test_result\" = ANY(\$1) AND \"Tb\".\"test_result2\" IS NULL FOR UPDATE",
#  "DELETE FROM \"just_a_test_deletion\" AS \"Tb\" WHERE \"Tb\".\"id\" = ANY(\$1)"]

The filters always land on the row they select, as above — never only in a pk IN (SELECT …) subquery (a filter that crosses a relation adds one for the index, beside the fence; see below). That is what makes a filter a guard you can rely on under concurrency; see Filters are a fence.

change_data Guard

If the connection is configured with change_data: false, any call to delete() raises a WritesDisabledError at the ORM layer before generating SQL.

# connection.yml: change_data: false
query = M.Just_a_test_deletion.objects.filter("id" => 1)
delete(query)
# ERROR: WritesDisabledError: Error in delete: the connection db is not allowed to write.
# Writes are disabled by default — set change_data: true under the `config:` block of the
# active environment in connection.yml to enable creates, updates, and deletes.

See Connection YML for the change_data configuration option.

Query shapes delete() refuses

# ✗ all four raise UnsafeMutationError
M.Result.objects.filter("points" => 0).limit(10).delete()
M.Result.objects.filter("points" => 0).offset(5).delete()
M.Result.objects.filter("points" => 0).order_by("-points").delete()
M.Result.objects.filter("points" => 0).distinct().delete()

# ✓ bound the set with the filter instead
ids = M.Result.objects.filter("points" => 0).limit(10).values("resultid") |> DataFrame
M.Result.objects.filter("resultid__@in" => ids.resultid).delete()

Bulk Deletion

By default, calling delete() on a query without filters raises UnsafeMutationError to prevent accidental data loss. You must explicitly set allow_delete_all=true.

# Delete all records (requires explicit permission)
query = M.Just_a_test_deletion.objects
delete(query, allow_delete_all=true)

Generated SQL (PostgreSQL):

DELETE FROM "just_a_test_deletion" AS "Tb"

# Selective bulk deletion
query = M.Result.objects
query.filter("raceid__year__@lt" => 1960)
delete(query)

Generated SQL (PostgreSQL):

SELECT "Tb"."resultid" FROM "result" AS "Tb"
WHERE "Tb"."resultid" IN (SELECT DISTINCT "Tb"."resultid"
  FROM "result" as "Tb"
  INNER JOIN "race" AS "Tb_1" ON "Tb"."raceid" = "Tb_1"."raceid"
  WHERE "Tb_1"."year" < $1)
  AND EXISTS (SELECT 1 FROM (SELECT 1) AS "__pormg_anchor"
  INNER JOIN "race" AS "Tb_1" ON "Tb"."raceid" = "Tb_1"."raceid"
  WHERE "Tb_1"."year" < $2) FOR UPDATE
-- Parameters: [1960, 1960]
DELETE FROM "result" AS "Tb" WHERE "Tb"."resultid" = ANY($1)
-- (plus a DELETE or UPDATE per dependent table with rows, scoped by the same keys)

result has dependents to cascade to, so the filter renders in the key read. On a model with nothing to cascade to it renders the same way in the DELETE itself. A filter that crosses a relation renders twice, and both halves matter. The IN (…) selects the rows through the primary key, which is what the planner uses an index for. The correlated EXISTS puts the same filter on the row being deleted, which is what PostgreSQL re-checks if that row changes while the delete waits on its lock (see Filters are a fence). Each value is therefore bound twice. The joins are the ones a read of the same filter renders — a nullable foreign key stays a LEFT JOIN, so an __@isnull filter through it still matches rows with no related row at all. So does a reverse or many-to-many relation that an __@isnull => true filter or a Qor arm reads (#1367): the delete reaches the rows with no child there, as the read returns them.

Cascade Deletion

A ForeignKey declared on_delete="CASCADE" makes PormG's deletion collector remove the related records too, in dependency order and inside the same transaction.

# This will also delete related Result records if they reference this Race
query = M.Race.objects
query.filter("name" => "Cancelled Grand Prix")
delete(query)

Generated SQL (PostgreSQL):

DELETE FROM "race" AS "Tb" 
WHERE "Tb"."raceid" = ANY($1)
-- Note: the race's keys are read first, `SELECT "Tb"."raceid" FROM "race" AS "Tb"
-- WHERE "Tb"."name" = $1 FOR UPDATE` with ["Cancelled Grand Prix"], and this DELETE binds them.
-- Dependent rows in "result" are removed by PormG's deletion collector, which emits their
-- DELETE separately in the same transaction (see the counts in the returned per-table Dict)

Rows that reference each other

A SET_NULL or SET_DEFAULT relation never constrains the order of the deletes: PormG runs its UPDATE before the DELETEs, so by the time a row is deleted nothing points at it through that column. A delete whose rows reference each other through such a relation runs as in Django, on every engine:

# A team lineage: `predecessor = ForeignKey("Team", on_delete = SET_NULL, null = true)`,
# Tyrrell <- BAR <- Honda. Deletes Tyrrell and sets BAR's predecessor to NULL.
M.Team.objects.filter("predecessor__@isnull" => true).delete()

# A team's favourite driver (`SET_NULL`) races for another team; a driver CASCADEs from its team.
# Deletes Tyrrell and its drivers, and nulls the favourite of every team that favoured one of them.
M.Team.objects.filter("name" => "Tyrrell").delete()

The order is the statements': the UPDATEs, then the drivers, then the team, so MySQL's check at each statement holds too.

A tree: a self-referential CASCADE

A model with a CASCADE foreign key to itself is a tree: a category and its subcategories, a threaded comment and its replies, a team lineage. Deleting a node deletes everything below it, and the dependents of every row deleted, as in Django:

# `predecessor = ForeignKey("Team", on_delete = CASCADE, null = true)`, Tyrrell <- BAR <- Honda <- Brawn,
# and a driver CASCADEs from its team. Deletes Honda, Brawn and the drivers of both.
M.Team.objects.filter("name" => "Honda").delete()

PormG reads the node's descendants by their keys, one level per query, each with its parent (SELECT "Tb"."id", "Tb"."predecessor" FROM "team" AS "Tb" WHERE "Tb"."predecessor" IN (…), FOR UPDATE on PostgreSQL and MySQL). Every row is deleted at its deepest level, deepest first, each level after its own dependents: when the filter matches a node and one of its descendants (filter("name__@in" => ["Tyrrell", "Honda"])), Honda is deleted at its depth, not with Tyrrell. Every DELETE then removes rows that nothing still references, so the foreign key's own ON DELETE CASCADE (which PormG's migrations write) never has a row to act on, and no engine needs to defer the key.

Each level is a plan of its own, in chunks on SQLite and MySQL like the root's keys: it repeats the dependents' existence checks, their locks, their SET_NULL / SET_DEFAULT updates and their DELETEs, so a tree costs about one cascade per level, plus one read per level. A matched row found again below another matched row is read again with its subtree, so a filter matching many rows of one deep tree ("every category of a site") reads more than once per row. The tree's depth is capped at 50 levels below the deleted rows.

Rows whose references form a loop (Ligier's predecessor is Prost, and Prost's is Ligier) have no such order. PostgreSQL and SQLite check the key at COMMIT, so the loop's rows go in one DELETE, after everything below them, and the delete counts every one of them. MySQL checks the key at each statement, so the delete raises BackendCapabilityError naming deferred_foreign_keys and the looped keys, before anything is written. The 50-level cap bounds a loop too, to 51 rows. Break the loop first (update("predecessor" => nothing) on one of the rows), then delete.

The walk covers the model the delete is on, when that model has one CASCADE key to itself. A tree reached through another model (deleting a constructor whose teams form a lineage), a model with two self-referential CASCADE keys, and an unfiltered delete (allow_delete_all) of a tree whose rows reference each other are still refused as a cycle, below. show_query on a mock connection shows the root's statements only, since a mock reads no keys.

Filters are a fence on PostgreSQL

A filtered delete() is safe to use as a compare-and-delete — "remove this result only if it is still classified Finished" — even while other sessions write to the same rows:

# Remove the result only if it is still classified "Finished" (statusid 1)
M.Result.objects.filter("resultid" => 7654, "statusid" => 1).delete()

Under PostgreSQL's default READ COMMITTED isolation, a DELETE that finds its row locked by another transaction waits, and when that transaction commits it re-checks the row's new version against the statement's own WHERE. Because PormG puts every filter on the row being deleted — directly, or through a correlated EXISTS when the filter crosses a relation — that re-check sees all of them. If the other transaction changed statusid, the row no longer matches and is left alone; the delete reports 0 rows. When the model has rows to cascade to, the statement that waits is the key read, SELECT … WHERE <filters> FOR UPDATE, and its lock re-checks the row the same way: a row that stopped matching is not among the keys, and nothing of it is deleted.

The fence covers the filters themselves, not the contents of a subquery you pass into one. In filter("resultid__@in" => M.Result.objects.filter("points" => 0).values("resultid")) the outer "Tb"."resultid" IN (…) is re-checked, but the inner query is an independent read of result taken before the wait, so a row whose points changed meanwhile still matches. Put a condition you rely on as a guard directly in the filter ("points" => 0), not inside a subquery.

The same holds for every statement a cascade emits (each child is matched on its own foreign key, so a child re-parented mid-delete is neither deleted nor nulled on the old parent's account) and for update(), joined filters included.

A filtered delete that cascades reads its keys first

The cascade deletes children before their parent, and each statement picks its rows when it runs. A root filter read again by every statement could match something different in each one, because the statements before it have written rows. A filter that reads the rows the cascade deletes is one case:

# Delete every race where someone started from pole, with its results, laps and standings
M.Race.objects.filter("result__grid" => 1).delete()

Read three times, result.grid = 1 matches the races in the first statement, and nothing in the last one, the race's own DELETE: its results are already gone. A filter on the root's own columns is the other: every SET_NULL / SET_DEFAULT update runs before any DELETE, and when one of them writes the root's table, the root's DELETE would match the rows it had just re-pointed.

So whenever the root has something to cascade to (a CASCADE, SET_NULL or SET_DEFAULT relation), delete() first reads the root's primary keys, once, before it writes anything. Every statement after that is scoped by those keys, and nothing reads the filter again:

steps = M.Race.objects.filter("year" => 2009, "result__grid" => 1).delete(show_query = :dict)
[(s[:operation], s[:model]) for s in steps]
# (:lock, "race")                      — the key read; `:select` on SQLite
# (:delete, "driver_standings")
# …
# (:delete, "race")

steps[1][:sql_text]
# SELECT "Tb"."raceid" FROM "race" AS "Tb" WHERE "Tb"."raceid" IN (SELECT DISTINCT "Tb"."raceid"
#   FROM "race" as "Tb"
#    INNER JOIN "result" AS "Tb_1" ON "Tb"."raceid" = "Tb_1"."raceid"
#   WHERE "Tb"."year" = $1 AND "Tb_1"."grid" = $2)
#   AND EXISTS (SELECT 1 FROM (SELECT 1) AS "__pormg_anchor"
#    INNER JOIN "result" AS "Tb_1" ON "Tb"."raceid" = "Tb_1"."raceid"
#   WHERE "Tb"."year" = $3 AND "Tb_1"."grid" = $4) FOR UPDATE

last(steps)[:sql_text]
# DELETE FROM "race" AS "Tb" WHERE "Tb"."raceid" = ANY($1)

It does not matter what the filter reads: a filter across a relation (a __ path, either direction, or a cjoin), a filter that holds a query ("x__@in" => M.Other.objects…, Subquery, Exists) and a filter on the root's own columns are all read once. The key read also covers the rows a SET_NULL or SET_DEFAULT re-points and a second cascade path that runs after the first one. Two deletes keep a single statement: a root with nothing to cascade to, and an unfiltered delete (allow_delete_all = true), whose DELETE takes every row whatever the cascade wrote.

• The key read is the root's lock on PostgreSQL and MySQL (SELECT … FOR UPDATE, below). Where the cascade has rows to write below the root, it replaces the root's own lock statement. Where it has none (races with no results yet), the delete used to be a single unlocked DELETE, and it is now the key read plus that DELETE, with the matched rows locked in between. On SQLite the key read is always one more SELECT.
• The keys come back to the client. A delete of a million races ships a million keys.
• SQLite and MySQL bind a list one value at a time, so a large key set runs the cascade in chunks, all inside the same transaction: the engine's bind limit divided by 16, the number of cascade paths into one table a statement can carry (2047 keys on SQLite, 256 on MySQL). PostgreSQL binds the whole list as one array. Each chunk repeats the cascade's statements and its existence checks, and a failure in any chunk rolls back every chunk before it. The cost per root stays flat as long as each child table's foreign key has an index, which PormG's migrations create for every ForeignKey: on SQLite, roots with one child each took about 7 µs per root at 1, 2 and 10 chunks (14 ms for 2047 roots, 153 ms for 20 470), and on MySQL 8.4 about 36–41 µs (11 ms for 256 roots, 91 ms for 2560). On a child table with no index on that column (a table PormG did not create), each chunk's DELETE of the roots scans the whole child table for every root, because the later chunks' children are still there: 10 chunks took 6.1 s on SQLite instead of 0.15 s. Index the column.
• Inspection reads the keys when the pool is a live one, as the cascade's existence checks already do, so show_query shows the statements that would run. Nothing is written.

A cascade locks its parents first on PostgreSQL

A cascade has one more ordering problem on top of its per-statement filters. Children are deleted before their parent, and a child's statement decides which parents it belongs to when it runs. Say another session renames a race while its delete is in progress:

1. The delete removes the race's results.
2. The other session renames the race.
3. The race's own DELETE now correctly skips it, because it no longer matches the filter.

The race survives, but its results are already gone.

To prevent this, delete() on PostgreSQL and MySQL first locks every parent in the cascade, from the root down, with SELECT … FOR UPDATE. Each lock uses the same predicate as that model's DELETE. A locked parent row cannot change until the delete commits, so children are selected from parents whose own columns stay fixed. If another session already changed a parent while the lock was waiting, PostgreSQL re-checks the new version: the parent is not locked, and none of its children are deleted. A filtered root is locked through its key read; see A filtered delete that cascades reads its keys first.

This applies at every depth. Deleting a circuit locks the circuit and then its races. A race moved to another circuit mid-delete is therefore neither deleted nor stripped of its results.

The locks are part of what runs, so the inspection shows them as :lock steps ahead of the writes:

steps = M.Race.objects.filter("name" => "Monaco Grand Prix", "year" => 2009).delete(show_query = :dict)
[(s[:operation], s[:model]) for s in steps]
# (:lock, "race")                      — the race's key read, which is its lock
# (:delete, "driver_standings")
# (:delete, "lap_times")
# (:delete, "constructor_results")
# (:delete, "result")
# (:delete, "qualifying")
# (:delete, "constructor_standings")
# (:delete, "race")

steps[1][:sql_text]
# SELECT "Tb"."raceid" FROM "race" AS "Tb" WHERE "Tb"."name" = $1 AND "Tb"."year" = $2 FOR UPDATE

result has no lock here because none of its dependent rows exist, so no statement reads it as a parent. A parent below the root is locked by its own SELECT count(*) FROM (SELECT 1 … FOR UPDATE) over the predicate its DELETE renders, and an unfiltered root (allow_delete_all) is locked that way too. The child tables' order follows the collector and may vary between runs; the locks always come first, root first.

What this costs and what it doesn't:

• A delete with nothing to cascade takes no lock, so it emits exactly one statement, as before. Leaves of the cascade are never locked as parents.
• The same rows are locked, but for longer. FOR UPDATE is the lock each parent's own DELETE takes anyway. It is now held from the first statement of the cascade instead of only near the end. While a large cascade runs, a concurrent insert of a child row, an update of a parent, or a select_for_update on it waits for the whole cascade.
• It adds some work. Each parent level costs one extra statement, and each parent row gets a row-lock write before its DELETE writes the row again.
• Locks are acquired in a different order: parents first, where the deletes alone went children first. A writer that locks a child and then its parent can now deadlock with a delete. PostgreSQL detects the deadlock and aborts one transaction with an error; it does not hang.
• SQLite takes no locks. Its writers are serialized for the whole delete transaction, so no parent can change under a cascade there.

One case remains unpinned: a parent set that grows. A filtered root's set cannot, since its keys are read once (#1370), but an unfiltered root, or a parent below the root, can gain a row that another session commits mid-cascade, and that row can be deleted without the children an earlier statement already passed over. The database's foreign keys catch this wherever one exists (see Concurrency below). Where that decides which parents go, pin the table yourself, for example with select_for_update inside a transaction. Or serialize the two writers with an advisory lock.

Concurrency: a cascade path can be pruned out from under you on PostgreSQL

delete() plans the cascade by probing each declared path — "are there any result rows for these races?" — and dropping the paths that come back empty, so a delete only emits statements for relations that actually have rows. Planning and the statements it produces run inside the same transaction. What that is worth differs by backend:

So on PostgreSQL (and MySQL) two sessions can interleave like this:

1. Session A calls delete() on a race. Planning probes lap_times, finds nothing, drops the path.
2. Session B inserts a lap_times row for that race and commits.
3. Session A's deletes run. The race goes; the new lap time was never in PormG's plan.

On a schema PormG's migrations built, the database is the backstop. Migrations emit each ForeignKey's own on_delete into the DDL — ON DELETE CASCADE, SET NULL, SET DEFAULT, RESTRICT — so whatever the collector skipped, the database still knows about. What that means depends on the action:

• CASCADE / SET_NULL / SET_DEFAULT — the three that emit statements. PostgreSQL performs the skipped action itself when PormG deletes the parent row (a referential action other than NO ACTION is not deferred, even on these DEFERRABLE INITIALLY DEFERRED constraints), so the row is removed, nulled or defaulted anyway and the delete succeeds. A filtered root's own DELETE is scoped by the keys it read first, so a row such an action re-points is not deleted on its account. What you lose is the accounting, not the rows: the per-table counts in the returned (total, Dict) tally only statements PormG issued, so work the database did on a pruned path is not counted. Treat those counts as a report of what the ORM did, not an audit of the transaction. MySQL also performs CASCADE and SET NULL at the parent's DELETE, but enforces SET DEFAULT as RESTRICT, so there a row on a pruned SET_DEFAULT path fails the delete (MySQL Guide).
• PROTECT / RESTRICT — pruning applies here too, because the probe runs for every reverse relation before PormG looks at the action at all; that is what makes PROTECT existence-driven. So a row inserted after the probe means ProtectedError is not raised, and the DELETE instead hits the DDL's ON DELETE RESTRICT, which PostgreSQL cannot defer. The delete still fails — same refusal, but reported as the driver's foreign-key error rather than PormG's.

An unset on_delete is unaffected either way: it builds no cascade path, so there is nothing to prune.

Two shapes are genuinely exposed, because there is no database action to fall back on:

• a ForeignKey declared db_constraint = false, which suppresses the constraint;
• a hand-written model over a table PormG did not create — a legacy or unmanaged schema — whose actual ON DELETE clause is absent or disagrees with what the model declares.

In both, a row inserted on a pruned path simply survives with a dangling reference. If that is your situation, serialize the delete against the writer yourself — an advisory lock around both is the usual answer.

PormG deliberately does not raise the isolation level or lock the probed child rows on your behalf. Either would change the failure modes of every delete, to close a window that the database already covers wherever the constraint is real. A higher isolation level brings serialization failures the caller must retry; locking the probed rows means blocking on rows another transaction holds. The parent locks described in A cascade locks its parents first are a different matter. They cover a parent that changes, not a child that is inserted, and they take only locks the cascade's own DELETEs would take anyway (just earlier), so they leave this window as it is.

try
    M.Driver.objects.filter("driverid" => 1).delete()
catch e
    e isa ProtectedError || rethrow()
    @warn "Reassign or delete the referencing rows first" msg=error_message(e)
end

See Models and Fields for more details on configuring deletion behavior (CASCADE, PROTECT, SET_NULL, etc.).