The write that cannot answer

Update, 29 October 2026. The pull request this post was waiting on, #105714, was closed unmerged on 17 October. Alexey Milovidov replaced it with a much smaller implementation, PR #126476, merged on 24 October and shipping in 26.11. It works differently: RETURNING (SELECT …) runs over the stream of rows being inserted, not over the table afterwards. I tested it on a 26.11 development build. Sections 5 and 6 are rewritten with the results, and the prediction in section 6 turned out to be wrong: RETURNING does not see what an insert-triggered materialized view writes.

A full field test of what shipped — including the rows it returns that were never stored — is in INSERT … RETURNING can return rows ClickHouse never stored.

In ClickHouse, a SELECT can be a function call. An INSERT cannot be anything but a shout into a room.

That sounds like a small ergonomic gap — the kind of thing you work around with a second query and forget about. It is not. If you are building anything where a write means something — a request, a command, a job, a syscall — the absence of a value coming back out of a write does not cost you one query. It costs you a table, a dispatcher, a polling loop, a delivery guarantee you cannot actually make, and a credential sitting on disk.

This is the story of issue #7206, which I opened in October 2019 and which is still open; of the two sibling issues it collided with; of what its absence forced in a system I have been building on ClickHouse 26.7; and of how INSERT … RETURNING finally landed in 26.11 — not through the pull request I was watching, but through a much smaller one that replaced it.

1. The shape of the hole

Start concrete. Here are two ways to expose an operation to a caller in a ClickHouse-only system.

-- shape 1: the caller SELECTs
SELECT * FROM sys.verb(arg = 'x');

-- shape 2: the caller INSERTs
INSERT INTO sys.requests (verb, args) VALUES ('create-database', map('db','houses'));

Shape 1 answers. The result set is the answer — a parameterized view runs, and the caller reads rows. Shape 2 cannot answer, ever, because a write has no result set to put an answer in.

The obvious objection is: make the write synchronous. That does not help, and this is the part worth internalising:

CREATE TABLE sys.exec (query String, settings Map(LowCardinality(String), String))
  ENGINE = QueryRunner SETTINGS mode = 'synchronous', threads = 1;

mode = 'synchronous' makes the caller wait for the statement to execute. It still hands back nothing. If the statement fails, the INSERT still returns success. The exception exists only in system.query_log. Verified on 26.7:

Failure	Surfaced to the caller?	Recorded?
admission — a `CONSTRAINT` rejects the enqueue	yes, synchronously (`Code: 469`)	yes
execution — the queued statement fails	no — the `INSERT` returns success	yes, in `system.query_log`

So blocking and answering are two different things, and ClickHouse gives you the first without the second. Which means: “did my write’s effect succeed?” is an unanswerable question on the write path. Not a hard question. An unanswerable one, at the level of the protocol.

Everything below is what you build to answer it anyway.

2. October 2019

I filed #7206, “Return modified rows”, on 2019-10-07. It asked for the PostgreSQL notation, and gave three use cases that I still think are the right three:

Read back what the server computed. DEFAULT and MATERIALIZED columns are evaluated server-side, so the client that wrote the row does not know what it wrote:
```
INSERT INTO events (entity, key, value) VALUES ('cpu', 'temp', 81.2)
RETURNING dt, ts;
```
Archiving without partition gymnastics. Today, moving a departed customer’s data to cold storage means designing your partition key around a question — when will this customer leave? — that has no answer at CREATE TABLE time. With RETURNING it is one statement:
```
INSERT INTO remote('archive_server', 'default', 'customer')
  DELETE FROM customer WHERE cust_id = 123
  RETURNING *;
```
Triggers, without a trigger subsystem. Point a URL engine table at an HTTP endpoint and RETURNING becomes the body of an AFTER DELETE trigger, with no new concept added to the server.

The issue got exactly one comment in six years and ten months. In November 2023 someone noted it was probably a duplicate of #5912. That is the entire discussion.

3. The sibling issues, and why it actually stalled

The duplicate pointer was correct, and the sibling is more interesting than my issue.

#5912 — “INSERT AND SELECT query (RFC)”, opened 2019-07-06 by Alexey Milovidov, three months before mine. Body, in full: “Do INSERT SELECT and output results of SELECT to user (tee).” Then the comment thread, which is the best compressed argument I know of for why “no one has asked for this” is weak evidence:

Date	Comment
2020-01-14	“Looks like no one needs it.”
2020-06-26	“It is also known as INSERT RETURNING…”
2022-08-14	“I need this feature.”
2023-04-12	“We need this feature.”
2023-12-26	“I needed this feature again”

All five by the same person. The 2023 comment came with a concrete proposal — INSERT INTO t AND SELECT … outputs the SELECT to the client, plain INSERT INTO t SELECT … stays silent.

#21697 — “INSERT … RETURNING clause”, opened 2021-03-13, also by Milovidov, contains the actual technical reason nothing shipped for five years. A contributor volunteered the same day, then hit the wall:

in ClickHouse, when parsing insert query, all data of the query after values is treated as data to be inserted.

That is the whole problem, and it is a parser problem, not a semantics problem. In PostgreSQL, RETURNING sits at the end because the statement ends. In ClickHouse, INSERT … VALUES hands the rest of the byte stream to the format reader — there is no “end of statement” to attach a clause to. Milovidov offered two escapes: allow RETURNING only for VALUES, or move the clause somewhere else in the statement and work out the syntax later. Neither was taken up. I commented “Duplicate of #7206 :)” in November 2021 and the thread went quiet for four more years.

Three open issues, two of them by the project’s founder, one clearly-identified parser obstacle, and no implementation. That is the state I designed against. (When PR #126476 merged on 2026-10-24 it closed #5912 and #21697; #7206 is still open.)

4. What the absence builds

Here is the part that is not about ClickHouse’s issue tracker.

I have been specifying a kernel — an agent OS whose kernel is a ClickHouse database, where a syscall is a row and IPC is SQL over a governed connection. No daemon, no broker, no runtime the realm does not already have. Everything below is verified on ClickHouse 26.7, in a disposable container, roughly seventy probes.

The first thing that fell out of the probing was that a syscall has to answer six independent questions, and one of them should not have had to exist:

Trigger — what causes execution (caller’s SELECT, caller’s INSERT, or a clock).
Blocking — does the call wait for the effect?
Answering — does the caller receive a result?
Executor identity — whose authority runs the statement.
Requester identity — carried as authority, as data, or not at all.
Delivery — what failure does (at-most-once, at-least-once).

Axis 3 is the tell. In a database with RETURNING, “does it block” and “does it answer” are the same axis — you waited, so you got the rows. In ClickHouse they come apart, and the design has to carry that separation everywhere.

The reachable corners of that space are four mechanisms:

	trigger	blocks	answers	executor	delivery
A `DEFINER` view	caller’s `SELECT`	yes	yes	the definer	—
B `executable()` behind a `DEFINER` view	caller’s `SELECT`	yes	yes	a stored credential	at-least-once
C `QueryRunner`	caller’s `INSERT`	optional	no	the requester	at-most-once
D refreshable MV (a clock)	a schedule	—	—	nobody	at-least-once

And they compose into a trilemma:

a synchronous answer · effects beyond the caller’s own rights · no credential at rest — pick two.

The reasoning is short. Performing an effect the caller cannot perform requires another principal’s authority. Answering synchronously requires that principal to be inside the caller’s query path. The only thing that sits inside a SELECT and runs as somebody else is a DEFINER view over executable() — and a script authenticates with its own credential, so that credential lives on disk. A pays by doing no writes. B pays by holding a credential. C pays by never answering.

Every leg of that trilemma is downstream of the missing RETURNING.

What “C pays by never answering” costs in practice

Mechanism C is the good one, security-wise. The caller inserts a typed verb into a request table; a DEFINER materialized view generates the SQL from a template; the runner is INVOKER, so the statement executes as — and is attributed to — the original requester, who never authored SQL and holds no grant on the executor:

CREATE MATERIALIZED VIEW sys.mv_create_database TO sys.exec
  DEFINER = kernel SQL SECURITY DEFINER AS
WITH $$CREATE DATABASE {0};$$ AS $create_database
SELECT format($create_database, args['db']) AS query,
       map('create_if_not_exists', '1')     AS settings
FROM sys.requests WHERE verb = 'create-database';

That is clean. And the caller learns nothing about whether it worked.

Since there is no transaction, a multi-statement verb whose third statement fails leaves the first two applied, silently. The workaround is a conditional completion marker — a final statement that writes a row only if the end state it asserts actually exists:

INSERT INTO sys.verb_done
SELECT 'onboard', {who:String}
WHERE (SELECT count() FROM system.tables
        WHERE database = 'house_' || {who:String} AND name = {table:String}) > 0;

Verified: the broken verb left the database created, the table absent, and no marker; the healthy verb left the table and the marker. So the system can answer did this verb complete — but only by the caller polling a second table. sys.call_results exists for exactly one reason: it is a RETURNING clause spelled as infrastructure.

And the polling loop is where it gets expensive

A poll needs something to dispatch the work and write the result. An insert-triggered MV cannot call executable() (Code: 397 — MVs cannot be created from table functions), so the dispatcher has to be a refreshable MV, which is a background loop, which means delivery is now at-least-once and unbounded: a crashed, silent, or pending handler is re-dispatched on every refresh, forever.

Bounding that turned into five attempts, each of which failed differently:

Attempt	Why it failed
exclude on any result row	strands an approval-required call at `pending` forever
exclude on non-terminal latest (`argMax`)	`ts` is millisecond-precision; a tied `pending` beats `done`
terminal-existence, “claim once”	a crashed handler writes nothing, so it reads as never-attempted
one `accepted` marker + `MAX_ATTEMPTS`	the count never grows past 1; the bound existed only in prose
marker per attempt + reaper	the count proves the marker view ran, not that a handler was dispatched

The common root: these are independent refreshable views over an append-only log, with no ordering between them and no way for a dispatcher to record that it consumed anything. A GROUP BY count is monotone — it can tell you how many attempts began, never whether one was taken. Coupling a marker to an actual dispatch is what a lease does and what a join cannot. The prerequisite is a KeeperMap CAS lease per call, and until it exists that path carries only naturally re-runnable verbs.

One missing clause, and the bill is: a result table, a refreshable dispatcher, a distributed consensus primitive, and a credential on disk.

5. What actually landed

The attempt: PR #105714

PR #105714, opened 2026-05-24, set out to close #21697. It grew to 2,948 added lines across 52 files and 169 commits, and it was labelled backward-incompatible. Its design: run the INSERT to completion, then run a user-supplied SELECT in the same session and return its result in one round trip. That made RETURNING a reserved keyword, rejected async_insert, and was explicitly not atomic: RETURNING (SELECT count() FROM t) counted the table, not your rows.

Its author was candid from the first comment: “This is ALL the AI. but I’m annoyed the referenced issue still exists. so it is a thing to do in background.” What “in background” meant was months of CI triage — parser round-trips, settings precedence, sanitizer-flaky tests, and one real regression in profile-event accounting for every INSERT. In September the author concluded that core work in the query path needs “more depth and deeper domain context than I can bring alone”, and that “while this PR is a failure, to me it is a success”. On 17 October Milovidov closed it “in favor of a much simpler, minimal implementation”.

What shipped: PR #126476, in 26.11

PR #126476, Add minimal INSERT … RETURNING (SELECT …), was merged on 24 October 2026. It is not in the 26.10 release; it ships in 26.11. The idea is different in kind, not just smaller: the RETURNING query runs on top of the inserted blocks. Inside it, the destination table refers to the stream of data being written, after omitted columns are filled with defaults — the same way a materialized view reads an insert.

INSERT INTO t (x) RETURNING (SELECT id, x) VALUES (1), (2);
INSERT INTO t RETURNING (SELECT count(), max(x) FROM t) SELECT … FROM src;

The clause goes after the column list and before the data, because everything after VALUES or FORMAT is data — the 2021 parser wall, solved by placement. RETURNING is not a reserved keyword, the result streams back while the client is still sending, async_insert is ignored for these queries, and destinations that read other tables (Alias, Distributed, Buffer) are refused, because access checks and row policies of the underlying tables would not apply to the substituted stream.

I ran it on a 26.11 development build (26.11.1.879, the official head image) in clickhouse local:

CREATE TABLE t (id UUID DEFAULT generateUUIDv4(), x UInt32, created DateTime DEFAULT now())
ENGINE = MergeTree ORDER BY x;

INSERT INTO t (x) RETURNING (SELECT x, length(toString(id)) AS uuid_len, created > 0 AS has_created)
VALUES (1), (2);

1	36	1
2	36	1

The generated UUID and the DEFAULT now() value come back exactly as written. And the table name inside RETURNING really is the stream, not the table:

INSERT INTO t (x) VALUES (10), (11), (12);
INSERT INTO t (x) RETURNING (SELECT count() AS returned_count, max(x) AS returned_max FROM t)
VALUES (20), (21);
SELECT count() FROM t;

2	21
7

Two rows returned, seven in the table. This is the opposite of #105714’s semantics, and it is better for the original request: my 2019 use case 1 — read back the MATERIALIZED and DEFAULT values the server computed — is now served properly, with no hand-written predicate to isolate “my” rows. Use case 3, the URL-engine trigger, still works without it. Use case 2 needs DELETE … RETURNING, which this still is not — so #7206 is only partly answered.

An Alias destination is refused as documented:

Code: 48. DB::Exception: RETURNING is not supported for INSERT into a table with the engine Alias,
which reads from other tables. (NOT_IMPLEMENTED)

6. What changes for a system built on the hole

The earlier version of this section predicted that RETURNING would let the caller’s syscall read its own completion marker inline — if the RETURNING query could see the effects of the materialized-view chain the insert triggers. I flagged that as the load-bearing assumption and as untested. It is now tested, and it is false:

CREATE TABLE src (k UInt32) ENGINE = MergeTree ORDER BY k;
CREATE TABLE dst (k UInt32, doubled UInt32) ENGINE = MergeTree ORDER BY k;
CREATE MATERIALIZED VIEW mv TO dst AS SELECT k, k * 2 AS doubled FROM src;

INSERT INTO src RETURNING (SELECT count() AS dst_rows_seen_by_returning FROM dst) VALUES (1), (2), (3);
SELECT count() FROM dst;

0
3

Inside RETURNING, the view’s target is still empty; a moment later it holds the three rows. A join from the inserted stream to the target shows the same thing. That follows directly from the design — RETURNING reads a fork of the insert stream, placed in front of the destination’s sink — so it is not a bug and not something to wait for.

So, for the kernel above:

Mechanism C does not get an answer from RETURNING. A verb whose result is produced by the insert-triggered chain — the QueryRunner statement, the verb_done marker — is still invisible at the moment the caller’s INSERT returns. The completion marker remains a row you poll for, and sys.call_results, the dispatcher and the lease stay.

What it does give is the request’s identity. The caller gets back, in the same round trip, what the server generated for the request row — an id, a timestamp, a default-filled field — so the poll that follows is keyed precisely, without a second query to find out what was just written. That is a real improvement to the client side of every mechanism, and it costs nothing.

The trilemma is unchanged. RETURNING moves no mechanism from one corner to another; verbs that need authority the caller lacks still need a stored credential and the per-handler question.

That is the corrected shape of it. A missing clause made the architecture larger, and the clause that finally arrived answers the question it was asked in 2019 — “what did the server write?” — rather than the one this architecture needed — “what happened because of it?”. The second question still needs a result table.

7. Coda

The two things I keep coming back to.

“Looks like no one needs it,” written in January 2020, was wrong in a specific and reproducible way: the person who wrote it needed it three times over the next four years, and each time wrote it down in the same thread. Absence of demand in an issue tracker measures how many people will file, not how many are affected. Everyone building request/response on top of a write is affected. Almost none of them will file, because the workaround — a second table and a poll — is available, and available workarounds are how a missing primitive stays invisible for seven years.

And: the strongest argument for a database feature is usually not a use case. It is a diagram of what people build when it is missing. Mine is a result table, a refreshable dispatcher, five failed attempts at a delivery bound, a distributed lease, and a credential on disk — all of it machinery for carrying a value backwards across a boundary the protocol does not let it cross.

Sources: #7206, #5912, #21697 (#5912 and #21697 closed by PR #126476 on 2026-10-24), PR #105714 (closed unmerged 2026-10-17), PR #126476 (merged 2026-10-24, in 26.11). Kernel behaviour verified on ClickHouse 26.7.1.1315, self-hosted, 2026-08-02. RETURNING behaviour verified on a 26.11 development build (26.11.1.879), 2026-10-29. Written 2026-08-04; updated 2026-10-29.