1.3.4 RETURNING Mapping and View-Target DML Transformation

The previous section (1.3.3) looked at the work the rewriter does to fill empty slots with values and expressions inside an INSERT/UPDATE/DELETE the user issued. Injecting defaults for omitted INSERT columns, resolving the DEFAULT keyword, and merging partial UPDATE assignments all belong to that work.

The other half of DML rewriting has a different flavor. It is the work of moving what existing references point to. It shows up in two places. One is when the RETURNING clause has to be resolved. The other is when a DML aimed at a view has to be rewritten as a DML against a base table. In both cases the expressions the user wrote stay the same; only what those expressions point to (the varno/varattno of Var nodes) gets swapped.

RULE's RETURNING is reshaped to the user's request

All three commands (INSERT/UPDATE/DELETE) can carry a RETURNING clause. It is the clause that hands the affected rows back as a result. RETURNING usually flows into the planning stage exactly as the user wrote it, but when DML is issued against a table that has an INSTEAD/ALSO rule attached, the rewriter adds one more mapping step.

Take a concrete scenario. Suppose an account_summary view and a rule on it are defined like this.

CREATE VIEW account_summary AS
    SELECT id, owner, balance FROM accounts;

CREATE RULE log_summary_insert AS ON INSERT TO account_summary
    DO INSTEAD INSERT INTO accounts (owner, balance)
                          VALUES (NEW.owner, NEW.balance)
                          RETURNING id, owner, balance;

This rule is an INSTEAD rule: when the user issues an INSERT to account_summary, it replaces that with an INSERT to accounts. The action itself carries a RETURNING clause whose list is the three columns id, owner, balance.

Now suppose the user issues this.

INSERT INTO account_summary (owner, balance) VALUES ('alice', 100)
   RETURNING id, balance;

The user's requested RETURNING list is two columns, id, balance. The shape differs from the rule action's RETURNING list of id, owner, balance. What the rewriter does here is clear. It reshapes the rule action's RETURNING list to the shape the user asked for, id, balance. The query that ends up being executed looks like this.

INSERT INTO accounts (owner, balance) VALUES ('alice', 100)
   RETURNING id, balance;

The user's shape is what ends up in the result. The RETURNING id, owner, balance written into the rule action itself acts only as a signal that "this rule knows how to resolve RETURNING over these columns"; the final shape follows the user's request.

There are three more branches.

First, the user did not write a RETURNING clause. In that case the rule action's RETURNING is simply discarded. The user said they don't want a result, so the RETURNING expressions are never evaluated.

Second, RETURNING lists appear on more than one rule action. Suppose, for example, account_summary has both of these rules attached.

CREATE RULE log_summary_insert AS ON INSERT TO account_summary
    DO INSTEAD INSERT INTO accounts (owner, balance)
                          VALUES (NEW.owner, NEW.balance)
                          RETURNING id, owner, balance;

CREATE RULE audit_summary_insert AS ON INSERT TO account_summary
    DO ALSO   INSERT INTO accounts_audit (owner, balance)
                          VALUES (NEW.owner, NEW.balance)
                          RETURNING audit_id, owner, balance;

DO ALSO does not intercept the user's command; it runs as an additional action alongside. Now a single INSERT event has two actions carrying RETURNING: one for accounts, the other for accounts_audit. When the user asks for RETURNING id, balance, there is no way to decide which action the result should come from. Because of this ambiguity, the rewriter reports an error at this point.

Third, an INSTEAD rule has no RETURNING at all but the user asked for one. This is also an error. The message is "cannot perform INSERT/UPDATE/DELETE RETURNING on relation X".

If we boil these three branches down to one line: the RULE system prioritizes the user's requested result shape, but the path is sound only when exactly one rule action can resolve it.

DML on a view is rewritten as DML on a base relation

When an INSERT/UPDATE/DELETE targets a view and that view is auto-updatable (a view that satisfies the simple-view expansion conditions from 1.3.1), the rewriter rewrites the view-target DML as a DML against the base relation. INSERT, UPDATE, DELETE, and MERGE all go through the same function rewriteTargetView.

Take an example first. Suppose a view and a user query like this.

CREATE VIEW active_accounts AS
    SELECT * FROM accounts WHERE deleted_at IS NULL;

UPDATE active_accounts SET balance = balance + 100
    WHERE owner = 'alice'
    RETURNING id, balance;

The user issued a query shaped like an UPDATE against the active_accounts view. The relation targeted by the UPDATE, i.e., the relation whose rows will be modified, is called the result relation in PG terminology. Here the slot for the result relation holds active_accounts. A view doesn't carry data of its own, so it cannot be updated as-is; the update has to flow through to the base table behind the view, which is accounts.

In one line, the transformation replaces every view reference inside the query with a reference to the base relation. Broken down into steps, three things happen.

First, the view RTE that sat in the result relation slot is swapped for the base relation RTE. In the example above, the slot initially held an RTE pointing to active_accounts; that gets replaced with the RTE of the base table accounts that the view definition points to.

Second, every Var node inside the query (Var in targetList, Var in RETURNING, Var in ON CONFLICT) has its attno rewritten from the view's column index to the base table's column index. One point worth pinning down here. When the user issues a DML targeting a view, the column names they write in the RETURNING clause are the view's column names. That's because when the analyzer resolves the column references in RETURNING, it looks at the catalog of the result relation, which is the view. After that, the rewriter rewrites those Var nodes' attno to point at slot numbers in the base table. SQL text is not what changes; one integer field on a tree node is swapped for a different number.

In our example, the view is shaped as SELECT * FROM accounts, so the view's id maps straight to the base's id. Rewriting attno is the whole story. If the view had instead been written as SELECT id AS account_id, owner, balance FROM accounts, with an alias renaming a column, the user would have written RETURNING account_id, and that Var's attno would be rewritten to point at the base's id slot. The label on the column the user gets back stays exactly as they wrote it, i.e., the view column name (account_id), and the value inside that column is read from the base's id.

Third, if the view definition had a WHERE clause, that condition is added to the user's query. The view definition in our example is ... WHERE deleted_at IS NULL, and that condition defines which rows are "visible" through the view. The UPDATE the user issued must target rows that fall within the view's visibility range, so the view's WHERE is ANDed onto the user's WHERE. UPDATE and DELETE pick up the merge this way; INSERT creates new rows and has no WHERE clause of its own for the view's WHERE to attach to, so it skips this step.

Conceptually, the rewriter takes the query above and produces something like this.

UPDATE accounts SET balance = balance + 100
    WHERE owner = 'alice' AND deleted_at IS NULL
    RETURNING id, balance;

The view definition's WHERE (deleted_at IS NULL) got ANDed onto the user's WHERE (owner = 'alice'), and the Var nodes in RETURNING have been remapped from view column indexes to base column indexes in accounts. The SQL above isn't real SQL text; it's an analogy that pictures the transformed result in SQL form. Inside the tree, the Var nodes in RETURNING simply point at base column slots; the result is not re-emitted as SQL text. The user thinks they updated a view, but the base table is what's actually updated, and the view no longer appears in the post-transformation query.

A security_barrier view pins the view's WHERE in front of securityQuals

If a view was created with the security_barrier option, one thing changes. Instead of being ANDed onto the user's WHERE in the usual way, the view definition's WHERE is pinned at the front of the base relation RTE's securityQuals. This is the same securityQuals list from 1.3.2, the container that held RLS quals. Going into the same container brings the same protection effect. The planner cannot reorder a leaky function the user planted in their WHERE clause to be evaluated before the view's filter. That stops a leaky function from bypassing the view's filter to see base rows.

WITH CHECK OPTION stops a new row from breaking the view's visibility

If the view carries WITH CHECK OPTION, one more thing is added. It is the promise that any row coming in (or being changed) through INSERT/UPDATE must satisfy the view's WHERE again. Adding WITH CHECK OPTION to the end of the view definition looks like this.

CREATE VIEW active_accounts AS
    SELECT * FROM accounts WHERE deleted_at IS NULL
    WITH CHECK OPTION;

If we issue an UPDATE against a view defined this way, the row produced by the UPDATE has to still satisfy deleted_at IS NULL. That stops the user from setting deleted_at to some timestamp via that UPDATE and shoving the row they were updating outside the view's visibility range. The rewriter turns this promise into a check entry and pins it at the front of the Query's withCheckOptions list.

The same container from 1.3.2 shows up again. The withCheckOptions list that held RLS WITH CHECK conditions now also takes the view's CHECK OPTION in the same place. The only difference is the identifier that distinguishes the kind of check; the enforcement mechanism is the same. Even the enforcement semantics (an error is raised on violation, not a silent drop) are identical.

Consider the case where RLS and view CHECK OPTION are both active on the same table/view. Say the base table accounts has an RLS WITH CHECK policy saying "an INSERTed row's owner must always be current_user", and the active_accounts view above carries a WITH CHECK OPTION saying "the row must satisfy deleted_at IS NULL". When the user issues an INSERT through active_accounts, the rewriter pins both conditions onto the withCheckOptions list together. The executor checks the new row against both conditions in order; a violation of either raises an error. Because they sit in the same list and are enforced the same way, the enforcement mechanism is single.

View transformation unwinds recursively

When the view-target DML work finishes, the resulting query re-enters RewriteQuery recursively. The base table might still have another view attached, and if RLS is attached to the base table, it gets applied in step 2 (fireRIRrules) that runs after this transformation. That's why multi-layer constructions like view-on-view, view-on-RLS, and RLS-on-view each unwind at their own layer.

What this means in practice

First, code that takes the RETURNING result from a view-target DML by positional index can break when the view definition changes. When you issue a DML against a view with RETURNING *, the result columns are labeled with the view's column names, but the data is actually read from the base table. Take a view defined like this.

CREATE VIEW account_summary AS
    SELECT id, owner, balance FROM accounts;

Application code issues an INSERT against this view, takes the RETURNING *, and assumes the first slot is id.

row = cur.execute(
    "INSERT INTO account_summary (owner, balance) VALUES (%s, %s) "
    "RETURNING *",
    ("alice", 100),
).fetchone()
new_id = row[0]   # assumed to be id

Later, during operation, suppose the team decides to add created_at to the view and redefines the column order.

CREATE OR REPLACE VIEW account_summary AS
    SELECT created_at, id, owner, balance FROM accounts;

If the same code keeps running, row[0] is now created_at, and new_id ends up with a timestamp. The code still compiles, no SQL error fires, so the bug is found late. Reading by column name (row["id"]) lets the value find its own slot even if the view's column order shifts. On code paths that issue DML through views, standardizing on reading RETURNING results by column name is the safer choice.

Second, an UPDATE against a view with WITH CHECK OPTION cannot shove the very row it is updating outside the view. One common trap shows up on the soft-delete pattern. Soft-delete means not actually DELETEing a row but recording a deletion timestamp in a column like deleted_at, treating the row as "removed". A view that exposes only "rows not yet deleted" is then layered on top. active_accounts is exactly that shape: in the base accounts the row stays put, and only rows with deleted_at IS NULL are exposed through the view. When application code tries to soft-delete a row through that same view, as in UPDATE active_accounts SET deleted_at = now() WHERE id = ?, the row resulting from that UPDATE no longer satisfies deleted_at IS NULL. The row falls outside the view's visibility range (the area of "visible rows" the view's WHERE defines). WITH CHECK OPTION blocks this with an error. The application meant to soft-delete, but the view's visibility promise stands in direct conflict with that intent, so the operation is rejected.

There are two ways to resolve the conflict. One is to issue the soft-delete UPDATE directly against the base table instead of routing it through the view. The view keeps the read-side visibility job, and the soft-delete write happens on the base. The other is to intentionally drop the view's CHECK OPTION and instead enforce, at the application layer, the promise that "INSERTed/UPDATEd rows must satisfy deleted_at IS NULL". The application takes on the promise the engine used to enforce.