PostgreSQL Rule System & Query Rewriter — Views, DO INSTEAD, and RLS

Contents:

• Theoretical Background
• Common DBMS Design
• PostgreSQL’s Approach
• Source Walkthrough
• Source verification (as of 2026-06-05)
• Beyond PostgreSQL — Comparative Designs & Research Frontiers
• Sources

Theoretical Background

A SQL statement, once parsed and analyzed, is not yet ready to optimize. Two of the language’s most useful abstractions — views and access rules — are defined in terms of other queries, and a query that mentions a view is really shorthand for the query the view stands for. Resolving that shorthand is query rewriting (also query modification): a source-to-source transformation on the query representation that runs before cost-based optimization, replacing references to derived relations and rule-governed relations with the queries that define them.

The technique was named and first implemented in INGRES. Stonebraker, Wong, Kreps & Held’s “The Design and Implementation of INGRES” (TODS 1976) introduced query modification as the mechanism for both views and access control: a view is stored as its defining query, and any query over the view is modified by substituting that defining query for the view name; protection (access control) and integrity constraints are enforced by the same machinery — the system appends a qualification to the user’s query so the user can only ever touch the rows the rule permits. The crucial observation is that views, permissions, and integrity are all the same operation — rewriting a query into a more qualified one — and so all three can share one engine that runs ahead of the optimizer.

Database System Concepts (Silberschatz, Korth & Sudarshan, 7e) frames the same idea in §4.2 “Views”. A view is “a relation that is not part of the logical model but is made visible to a user as a virtual relation,” and “view definitions … are substituted into queries using the view” (§4.2.3 “Materialized Views” contrasts this with physically storing the result). The textbook’s “view expansion” — “a way to define the meaning of views defined in terms of other views” by “replacing the view relation by … its definition” and “repeating this … until no more view relations are present” (§4.2.4) — is exactly the recursive substitution a rewriter performs, and the textbook even notes that expansion “should not loop” unless the view definitions are cyclic, which is precisely the recursion the engine must detect and forbid.

POSTGRES, Berkeley’s successor to INGRES and the direct ancestor of PostgreSQL, generalized query modification into a first-class, extensible rule system. Stonebraker, Rowe & Hirohama’s “The Implementation of POSTGRES” (IEEE TKDE 1990) and the earlier “The Design of POSTGRES” (SIGMOD 1986) describe a production rule subsystem (PRS, later PRS2) in which rules of the form ON event TO object WHERE qual DO [INSTEAD] action are stored in the catalog and applied by the system. POSTGRES deliberately implemented views as rules — a view is just a relation carrying an ON SELECT … DO INSTEAD rule whose action is the view’s defining query — so that the view machinery and the general rule machinery are one and the same. Two rule application strategies were offered: a query-rewrite implementation (modify the query before execution, as INGRES did) and a tuple-level implementation (mark tuples and fire rules during execution); modern PostgreSQL keeps the query-rewrite strategy for the rule system and uses triggers for the per-tuple strategy.

Three properties of rule-based rewriting shape every engine that adopts it, and are worth naming before reading any source:

1. One query becomes a list of queries. An INSTEAD rule can replace the original; an ALSO rule adds work alongside it; a DO NOTHING rule deletes it. The rewriter’s output is therefore a list of queries, possibly empty, possibly longer than one.
2. Substitution is recursive and must terminate. A view over a view over a table expands in layers; a rule whose action references the ruled relation can loop. The engine needs explicit cycle detection, not just structural recursion.
3. Rewriting runs before planning, on the logical Query, not the physical plan. The output is still a Query tree — the planner’s input — so the rewriter speaks the parser’s language, manipulating range-table entries, target lists, and qualifications, never plan nodes or tuples.

PostgreSQL is a faithful, if elaborated, implementation of the POSTGRES rule system: its rewriter substitutes stored rule actions into the Query tree between parse analysis and planning, with views as ON SELECT rules, INSERT/UPDATE/DELETE rules for the general case, and row-level security policies bolted onto the same recursion as a late pass. The rest of this document traces those pieces in the REL_18 source.

Common DBMS Design

The theory gives the model — substitute defining queries for derived relations, before the optimizer. This section names the engineering conventions a production rewriter adopts to make that model correct and safe, the patterns the textbook leaves implicit. PostgreSQL’s specific choices in ## PostgreSQL's Approach are one set of dials within this shared space.

Rewrite on the logical query representation, not on text. A mature engine never rewrites SQL strings; it rewrites the analyzed query tree, where names are already resolved to object IDs, types are fixed, and the range table enumerates every relation in scope. Substituting a view means splicing the view’s already-analyzed sub-tree into the outer tree and fixing up variable references — not re-parsing text. This makes rewriting deterministic and immune to re-parsing ambiguity.

Views as the degenerate case of rules. Rather than special-casing views in the catalog as a distinct object kind with their own expansion path, the INGRES/POSTGRES lineage models a view as a relation that owns a single unconditional ON SELECT DO INSTEAD rule. View expansion then is rule application; there is one code path, not two. The cost is that “updating a view” is not free — the engine must either find an INSTEAD rule / INSTEAD OF trigger that says how, or derive an automatic update by pulling the view’s base relation up into the modifying query.

Distinguish read rules from write rules. Read-side expansion (ON SELECT) and write-side rules (ON INSERT/UPDATE/DELETE) have opposite shapes. A read rule always fires and always replaces (INSTEAD), is unconditional, and substitutes a sub-select for a relation reference. A write rule may be conditional, may be ALSO (additive) rather than INSTEAD, may carry a RETURNING list, and produces additional whole queries rather than splicing a sub-tree. Engines that conflate the two end up with awkward special cases; separating them — as PostgreSQL does with fireRIRrules (retrieve / read) vs. fireRules (write) — keeps each path simple.

Conditional INSTEAD needs a complementary default query. If a rule says “INSTEAD of this UPDATE, do that — but only WHERE qual,” the rows where qual is false still need the original action. The standard trick is to emit, alongside the rule action (carrying +qual), a copy of the original query with AND NOT qual (more precisely AND qual IS NOT TRUE, to get three-valued logic right) attached. The two together partition the input rows.

Bounded recursion via an explicit work stack. Because a rule action can reference the ruled relation, and a view can reference itself transitively, the engine carries an explicit set of (relation, event) pairs currently being rewritten and raises an error on re-entry, rather than relying on the C stack to blow up. View expansion carries an analogous “active retrieve rules” OID set.

Lock acquisition is the rewriter’s hidden job. A stored rule action references relations that the original parse did not lock. Before the action’s sub-tree can be planned and executed, the rewriter must take the right lock mode on every relation the action mentions — otherwise the schema could change underfoot between rewrite and execute. This “acquire locks while substituting” responsibility is easy to overlook and a frequent source of subtle bugs.

Row-level security is a rewrite, not an executor check. The cleanest place to enforce per-row access policies is the same place views are expanded: the rewriter attaches the policy’s qualification to the relation’s range-table entry as a security-barrier qualification, so the planner and executor enforce it as an ordinary, but optimization-fenced, WHERE clause. This reuses the query-modification idea from INGRES verbatim, three decades later.

flowchart LR
  P["Parser +<br/>parse analysis"] --> QA["Query tree<br/>QSRC_ORIGINAL"]
  QA --> RW
  subgraph RW["Query Rewriter (QueryRewrite)"]
    direction TB
    S1["Step 1: RewriteQuery<br/>fire write rules + auto-update views"]
    S2["Step 2: fireRIRrules<br/>expand views + SEARCH/CYCLE + RLS"]
    S3["Step 3: assign canSetTag<br/>command result tag"]
    S1 --> S2 --> S3
  end
  RW --> QL["List of Query trees<br/>0, 1, or many"]
  QL --> PL["Planner<br/>one PlannedStmt each"]
  PL --> EX["Executor"]

Figure 1 — Where rewriting sits. The rewriter is the stage between parse analysis and planning. It consumes one Query and emits a list of Query trees, each of which the planner turns into a PlannedStmt. Cross-refs: postgres-analyze-transform.md owns the parse-analysis stage that produces the input; postgres-planner-overview.md owns the planner that consumes the output.

PostgreSQL’s Approach

PostgreSQL’s rewriter lives under src/backend/rewrite/. The public entry point is QueryRewrite in rewriteHandler.c, called from pg_rewrite_query in tcop/postgres.c once per top-level analyzed query. A rule is a RewriteRule struct (built from a pg_rewrite catalog row, cached in the relcache as rel->rd_rules), and a view is a relation whose rd_rules contains exactly one CMD_SELECT rule named _RETURN.

The three-step entry point

QueryRewrite is deliberately thin. It asserts the input is an unmodified original query, then runs three steps:

// QueryRewrite — src/backend/rewrite/rewriteHandler.c (condensed)
List *
QueryRewrite(Query *parsetree)
{
    int64       input_query_id = parsetree->queryId;
    List       *querylist;
    List       *results;

    Assert(parsetree->querySource == QSRC_ORIGINAL);
    Assert(parsetree->canSetTag);

    /* Step 1: apply all non-SELECT rules, possibly getting 0 or many queries */
    querylist = RewriteQuery(parsetree, NIL, 0, 0);

    /* Step 2: apply all the RIR (retrieve-instead-retrieve) rules on each query */
    results = NIL;
    foreach(l, querylist)
    {
        Query  *query = (Query *) lfirst(l);
        query = fireRIRrules(query, NIL);
        query->queryId = input_query_id;
        results = lappend(results, query);
    }

    /* Step 3: decide which result query sets the command tag */
    /* ... if the original survives it sets the tag; else the last INSTEAD ... */
    return results;
}

The naming reflects POSTGRES history: RIR = “retrieve instead retrieve,” i.e. the unconditional ON SELECT DO INSTEAD rules that define views. Step 1 handles write rules and produces the set of queries to run; step 2 expands views (and applies RLS and SEARCH/CYCLE) inside each; step 3 is bookkeeping for which query, if any, reports the row count.

flowchart TB
  QR["QueryRewrite(parsetree)"] --> RQ["RewriteQuery<br/>(step 1: write rules)"]
  RQ -->|"list of product queries"| LOOP["for each result query"]
  LOOP --> RIR["fireRIRrules<br/>(step 2)"]
  RIR --> VE["expand view RTEs<br/>ApplyRetrieveRule"]
  RIR --> SC["expand SEARCH / CYCLE<br/>rewriteSearchAndCycle"]
  RIR --> RLS["apply RLS policies<br/>get_row_security_policies"]
  VE -.->|"recurse into view body"| RIR
  RIR --> TAG["step 3: assign canSetTag"]
  TAG --> OUT["List of plannable Query trees"]

Figure 2 — Control flow of QueryRewrite. Step 1 (RewriteQuery) flattens write rules into a list; step 2 (fireRIRrules) expands views, recursive-CTE SEARCH/CYCLE clauses, and row-level security inside each result; step 3 assigns the command tag.

Views are ON SELECT DO INSTEAD rules

A view has no storage. CREATE VIEW v AS SELECT … creates an empty relation of relkind RELKIND_VIEW and then installs an ON SELECT rule through DefineQueryRewrite. That function enforces the rule that only views may carry ON SELECT rules, that the action must be a single unconditional SELECT, and that its target list matches the view’s columns exactly:

// DefineQueryRewrite — src/backend/rewrite/rewriteDefine.c (condensed)
if (event_type == CMD_SELECT)
{
    /* Rules ON SELECT are restricted to view definitions, so this had
     * better be a view ... */
    if (event_relation->rd_rel->relkind != RELKIND_VIEW &&
        event_relation->rd_rel->relkind != RELKIND_MATVIEW)
        ereport(ERROR, /* "relation cannot have ON SELECT rules" */ ...);

    /* ... the one action must be an unconditional INSTEAD SELECT ... */
    if (length(action) != 1 || query->commandType != CMD_SELECT)
        ereport(ERROR, /* "rules on SELECT must have action INSTEAD SELECT" */ ...);
    if (event_qual != NULL)
        ereport(ERROR, /* "event qualifications are not implemented for rules on SELECT" */ ...);

    /* ... the SELECT's targetlist must exactly match the view's columns ... */
    checkRuleResultList(query->targetList, RelationGetDescr(event_relation),
                        true, /* requireColumnNameMatch = */ event_relation->rd_rel->relkind != RELKIND_MATVIEW);

    /* ... and the rule must be named _RETURN (ViewSelectRuleName) */
}

To read a view’s defining query at rewrite time, get_view_query simply finds that _RETURN rule’s single action in the relcache (returning a read-only pointer the caller must copy before scribbling on):

// get_view_query — src/backend/rewrite/rewriteHandler.c (condensed)
Query *
get_view_query(Relation view)
{
    Assert(view->rd_rel->relkind == RELKIND_VIEW);
    for (int i = 0; i < view->rd_rules->numLocks; i++)
    {
        RewriteRule *rule = view->rd_rules->rules[i];
        if (rule->event == CMD_SELECT)
        {
            if (list_length(rule->actions) != 1)
                elog(ERROR, "invalid _RETURN rule action specification");
            return (Query *) linitial(rule->actions);   /* read-only! */
        }
    }
    elog(ERROR, "failed to find _RETURN rule for view");
}

The two-step encoding — empty relation plus a rule — is why information_schema.views, pg_get_viewdef, and \d+ v all reconstruct the view definition from the stored Query: the Query is the view.

DefineQueryRewrite even contains the relation-to-view conversion path: if an ON SELECT rule is installed on a plain table that has no data and no disallowed features, the function flips the relation’s relkind to RELKIND_VIEW and detaches its storage — the mechanism pg_dump once relied on to recreate views as “table plus rule.” The same function takes AccessExclusiveLock on the event relation precisely because installing an ON SELECT rule must lock out any concurrent SELECT that might already be scanning the soon-to-be-virtual relation:

// DefineQueryRewrite — src/backend/rewrite/rewriteDefine.c (condensed)
/* If we are installing an ON SELECT rule, we had better grab
 * AccessExclusiveLock to ensure no SELECTs are currently running on the
 * event relation. ... */
event_relation = table_open(event_relid, AccessExclusiveLock);

if (event_relation->rd_rel->relkind != RELKIND_RELATION &&
    event_relation->rd_rel->relkind != RELKIND_MATVIEW &&
    event_relation->rd_rel->relkind != RELKIND_VIEW &&
    event_relation->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
    ereport(ERROR, /* "relation cannot have rules" */ ...);

The lock level is matched in DefineRule (via RangeVarGetRelid(stmt->relation, AccessExclusiveLock, false)) so the two entry points agree.

fireRIRrules — view expansion, the SELECT-side workhorse

Step 2 of QueryRewrite is fireRIRrules. It walks the range table of the query (and recurses into sub-queries, CTEs, and SubLinks). For each relation RTE that has ON SELECT rules, it applies ApplyRetrieveRule, which converts the relation RTE into a sub-query RTE whose sub-query is the view body:

// ApplyRetrieveRule — src/backend/rewrite/rewriteHandler.c (condensed)
static Query *
ApplyRetrieveRule(Query *parsetree, RewriteRule *rule, int rt_index,
                  Relation relation, List *activeRIRs)
{
    Query *rule_action;
    RangeTblEntry *rte;

    if (list_length(rule->actions) != 1)
        elog(ERROR, "expected just one rule action");
    if (rule->qual != NULL)
        elog(ERROR, "cannot handle qualified ON SELECT rule");

    /* ... (UPDATE/DELETE-on-view path adds a target-relation copy + wholerow Var) ... */

    /* Make a modifiable copy of the view query and lock the rels it mentions */
    rule_action = copyObject(linitial(rule->actions));
    AcquireRewriteLocks(rule_action, true, (rc != NULL));

    /* Recursively expand any view references inside the view */
    rule_action = fireRIRrules(rule_action, activeRIRs);

    /* Plug the view query in as a subselect: relation RTE -> subquery RTE */
    rte = rt_fetch(rt_index, parsetree->rtable);
    rte->rtekind = RTE_SUBQUERY;
    rte->subquery = rule_action;
    rte->security_barrier = RelationIsSecurityView(relation);
    rte->tablesample = NULL;
    rte->inh = false;
    return parsetree;
}

The mutation rte->rtekind = RTE_SUBQUERY; rte->subquery = rule_action; is the whole game: the planner later treats the view exactly like an inline sub-query (and usually flattens it back out via subquery pull-up). Two details matter. First, security_barrier is copied from the view relation — a WITH (security_barrier) view fences the sub-query against qual reordering, which is the foundation RLS builds on. Second, the recursive fireRIRrules(rule_action, activeRIRs) call expands views nested inside the view; recursion is bounded by the activeRIRs OID list, checked just before the call:

// fireRIRrules — src/backend/rewrite/rewriteHandler.c (condensed)
if (list_member_oid(activeRIRs, RelationGetRelid(rel)))
    ereport(ERROR,
            (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
             errmsg("infinite recursion detected in rules for relation \"%s\"",
                    RelationGetRelationName(rel))));
activeRIRs = lappend_oid(activeRIRs, RelationGetRelid(rel));
foreach(l, locks)
    parsetree = ApplyRetrieveRule(parsetree, lfirst(l), rt_index, rel, activeRIRs);
activeRIRs = list_delete_last(activeRIRs);

Materialized views (RELKIND_MATVIEW) are not expanded by fireRIRrules when referenced — they have real storage, so the loop continues past them. Their ON SELECT rule is used only at REFRESH time, when the MV’s own defining query is run.

RewriteQuery and fireRules — the write-rule path

Step 1, RewriteQuery, handles CMD_INSERT / CMD_UPDATE / CMD_DELETE / CMD_MERGE. For each, it first normalizes the target list (rewriteTargetListIU — fill in column defaults, expand DEFAULT markers, reorder to physical column order), then collects the applicable rules with matchLocks and fires them with fireRules:

// RewriteQuery — src/backend/rewrite/rewriteHandler.c (condensed)
locks = matchLocks(event, rt_entry_relation, result_relation, parsetree, &hasUpdate);

product_orig_rt_length = list_length(parsetree->rtable);
product_queries = fireRules(parsetree, result_relation, event, locks,
                            &instead, &returning, &qual_product);

matchLocks returns only the rules whose event matches and (for write rules) whose target is the result relation, while also honoring the session replication role and forbidding rules on MERGE targets. fireRules then walks the matched rules and classifies each by QuerySource:

// fireRules — src/backend/rewrite/rewriteHandler.c (condensed)
foreach(l, locks)
{
    RewriteRule *rule_lock = (RewriteRule *) lfirst(l);
    Node       *event_qual = rule_lock->qual;
    QuerySource qsrc;

    if (rule_lock->isInstead)
    {
        if (event_qual != NULL)
            qsrc = QSRC_QUAL_INSTEAD_RULE;     /* conditional INSTEAD */
        else
        {
            qsrc = QSRC_INSTEAD_RULE;          /* unconditional INSTEAD */
            *instead_flag = true;
        }
    }
    else
        qsrc = QSRC_NON_INSTEAD_RULE;          /* ALSO rule */

    if (qsrc == QSRC_QUAL_INSTEAD_RULE && !*instead_flag)
    {
        /* build the complementary "default" query: original AND NOT qual */
        if (*qual_product == NULL)
            *qual_product = copyObject(parsetree);
        *qual_product = CopyAndAddInvertedQual(*qual_product, event_qual,
                                               rt_index, event);
    }

    foreach(r, rule_lock->actions)        /* rewrite & emit each rule action */
    {
        Query *rule_action = rewriteRuleAction(parsetree, lfirst(r),
                                               event_qual, rt_index, event,
                                               returning_flag);
        rule_action->querySource = qsrc;
        rule_action->canSetTag = false;
        results = lappend(results, rule_action);
    }
}

The three QuerySource values encode the rule taxonomy: QSRC_INSTEAD_RULE (unconditional INSTEAD — the original query is dropped), QSRC_QUAL_INSTEAD_RULE (conditional INSTEAD — the original survives but gets the negated qual via CopyAndAddInvertedQual, which attaches rule_qual IS NOT TRUE so three-valued logic is handled), and QSRC_NON_INSTEAD_RULE (DO ALSO — additive, the original runs too). A CMD_NOTHING action emits nothing, so DO INSTEAD NOTHING simply deletes the query. rewriteRuleAction is the heavy lifter: it deep-copies the stored action, acquires locks (AcquireRewriteLocks), and merges the action’s range table into the parse tree’s, offsetting var numbers and fixing OLD/NEW references (PRS2_OLD_VARNO / PRS2_NEW_VARNO) so the action’s OLD/NEW resolve against the original query’s relation and target list.

Auto-updatable views — pulling the base relation up

If an INSERT/UPDATE/DELETE targets a view and no unconditional INSTEAD rule and no INSTEAD OF trigger fired, RewriteQuery tries to rewrite the modification against the view’s base relation via rewriteTargetView:

// RewriteQuery — src/backend/rewrite/rewriteHandler.c (condensed)
if (!instead &&
    rt_entry_relation->rd_rel->relkind == RELKIND_VIEW &&
    !view_has_instead_trigger(rt_entry_relation, event, parsetree->mergeActionList))
{
    if (qual_product != NULL)        /* conditional INSTEAD blocks auto-update */
        error_view_not_updatable(rt_entry_relation, parsetree->commandType,
                                 parsetree->mergeActionList,
                                 gettext_noop("Views with conditional DO INSTEAD rules are not automatically updatable."));

    parsetree = rewriteTargetView(parsetree, rt_entry_relation);

    if (parsetree->commandType == CMD_INSERT)
        product_queries = lcons(parsetree, product_queries);
    else
        product_queries = lappend(product_queries, parsetree);
    instead = true;       /* prevent the original from being emitted again */
    returning = true;
    updatableview = true;
}

rewriteTargetView checks the view is auto-updatable (view_query_is_auto_updatable — single base relation, no aggregates / DISTINCT / GROUP BY etc.), opens the base relation with RowExclusiveLock, appends a new RTE for the base relation to the query’s range table, and re-points the view’s target-list Vars at it with ChangeVarNodes, so the modification now targets the base table. Because the result may again be a view, the rewritten query is fed back through RewriteQuery recursively. WITH CHECK OPTION is enforced here by accumulating withCheckOptions, the same list RLS uses.

Bounded write-rule recursion

Just as view expansion uses activeRIRs, write-rule recursion uses an explicit rewrite_events list of (relation, event) pairs. Before recursing into product queries, RewriteQuery checks the pair is not already in flight:

// RewriteQuery — src/backend/rewrite/rewriteHandler.c (condensed)
foreach(n, rewrite_events)
{
    rewrite_event *rev = (rewrite_event *) lfirst(n);
    if (rev->relation == RelationGetRelid(rt_entry_relation) && rev->event == event)
        ereport(ERROR,
                (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
                 errmsg("infinite recursion detected in rules for relation \"%s\"",
                        RelationGetRelationName(rt_entry_relation))));
}
rev = palloc(sizeof(rewrite_event));
rev->relation = RelationGetRelid(rt_entry_relation);
rev->event = event;
rewrite_events = lappend(rewrite_events, rev);
/* ... recurse into each product query ... */
rewrite_events = list_delete_last(rewrite_events);

The row-level-security seam

Row-level security is applied as the last pass of fireRIRrules, after view expansion, CTE recursion, and SubLink processing — deliberately, because RLS quals may themselves contain SubLinks that would otherwise be walked twice. For every ordinary or partitioned-table RTE, the rewriter asks the RLS subsystem for the policy qualifications and prepends them to the RTE’s securityQuals:

// fireRIRrules — src/backend/rewrite/rewriteHandler.c (condensed)
get_row_security_policies(parsetree, rte, rt_index,
                          &securityQuals, &withCheckOptions,
                          &hasRowSecurity, &hasSubLinks);

if (securityQuals != NIL || withCheckOptions != NIL)
{
    /* if RLS quals have SubLinks, lock their rels and fire RIR rules on them,
       guarding against infinite recursion via activeRIRs */
    ...
    /* prepend RLS quals so they sort *before* any security-barrier-view quals */
    rte->securityQuals = list_concat(securityQuals, rte->securityQuals);
    parsetree->withCheckOptions = list_concat(withCheckOptions,
                                              parsetree->withCheckOptions);
}
if (hasRowSecurity)
    parsetree->hasRowSecurity = true;

get_row_security_policies (in rowsecurity.c) returns securityQuals (applied to reads — SELECT/UPDATE/DELETE visibility) and withCheckOptions (applied to writes — new/updated rows must satisfy the policy or the statement errors). Placing the quals on securityQuals rather than the ordinary WHERE is what makes them security-barrier quals: the planner will not push a potentially-leaky user function below them. The deeper mechanics of policy selection, USING vs. WITH CHECK, permissive vs. restrictive combination, and FORCE ROW LEVEL SECURITY live in the cross-referenced postgres-row-level-security.md; this document covers only the seam where the rewriter invokes them.

SEARCH / CYCLE clause expansion

Also folded into fireRIRrules is the expansion of SEARCH and CYCLE clauses on recursive CTEs — a convenient place since the pass already visits every Query. rewriteSearchAndCycle rewrites a WITH RECURSIVE … SEARCH BREADTH/DEPTH FIRST or … CYCLE CTE into a plain recursive CTE with extra columns: a depth-tracking ROW(...) / array for SEARCH, and a visited-rows array plus a cycle-mark CASE expression for CYCLE.

// fireRIRrules — src/backend/rewrite/rewriteHandler.c (condensed)
foreach(lc, parsetree->cteList)
{
    CommonTableExpr *cte = lfirst_node(CommonTableExpr, lc);
    if (cte->search_clause || cte->cycle_clause)
    {
        cte = rewriteSearchAndCycle(cte);
        lfirst(lc) = cte;
    }
}

This keeps the planner and executor ignorant of SEARCH/CYCLE — by the time they see the CTE it is an ordinary recursive union with the bookkeeping columns materialized as SQL.

Source Walkthrough

Anchor on symbol names, not line numbers. The PostgreSQL source moves between releases; a function or struct name is the stable handle. Use git grep -n '<symbol>' src/backend/rewrite/ to locate the current position. The line numbers in the position-hint table were observed at commit 273fe94 (REL_18_STABLE) and are quick hints only.

Entry & orchestration (rewriteHandler.c)

• QueryRewrite — the single public entry; runs the three steps (RewriteQuery → per-query fireRIRrules → command-tag assignment). Called from pg_rewrite_query in tcop/postgres.c.
• RewriteQuery — step 1; fires write rules for INSERT/UPDATE/DELETE/MERGE, normalizes target lists, handles auto-updatable views, recurses into product queries and into data-modifying CTEs. Carries the rewrite_events recursion stack.
• fireRIRrules — step 2; walks the range table, expands views via ApplyRetrieveRule, recurses into sub-queries / CTEs / SubLinks, expands SEARCH/CYCLE, and applies RLS as the final pass. Carries the activeRIRs OID list.

Write-rule machinery (rewriteHandler.c)

• matchLocks — select the RewriteRules from relation->rd_rules whose event matches and (for write rules) whose target is the result relation; honors SessionReplicationRole and blocks rules on MERGE targets.
• fireRules — iterate matched rules; classify INSTEAD / conditional INSTEAD / ALSO into QuerySource; build the qual_product default copy; emit each action via rewriteRuleAction.
• rewriteRuleAction — deep-copy a stored rule action, acquire its locks, merge its range table into the parse tree, offset varnos, and fix OLD/NEW (PRS2_OLD_VARNO / PRS2_NEW_VARNO) references.
• CopyAndAddInvertedQual / AddInvertedQual — attach rule_qual IS NOT TRUE to the surviving original query for conditional INSTEAD rules.
• rewriteTargetListIU — fill in column defaults, expand SetToDefault markers, handle GENERATED / identity columns, reorder to physical order.
• rewriteValuesRTE / rewriteValuesRTEToNulls — multi-row INSERT … VALUES default handling.

View expansion & updatable views (rewriteHandler.c)

• ApplyRetrieveRule — convert a view’s relation RTE into a sub-query RTE carrying the view body; set security_barrier; recurse via fireRIRrules.
• get_view_query — fetch the view’s _RETURN rule action from the relcache (read-only pointer).
• rewriteTargetView — pull a view’s single base relation up into a modifying query so the view becomes auto-updatable; enforce WITH CHECK OPTION.
• view_has_instead_trigger — does the view have an INSTEAD OF trigger for this event (so auto-update is not attempted)?
• error_view_not_updatable — the family of “cannot insert/update/delete view” errors with their hints.
• markQueryForLocking — push FOR [KEY] UPDATE/SHARE down through an expanded view’s contained relations.

Lock acquisition & manipulation (rewriteHandler.c, rewriteManip.c)

• AcquireRewriteLocks — take the right lock mode on every relation a (possibly stored) query mentions; fix dropped-column JOIN alias vars; recurse into sub-queries, CTEs, and SubLinks.
• acquireLocksOnSubLinks — the walker that reaches SubLink sub-queries.
• ChangeVarNodes / OffsetVarNodes / ReplaceVarsFromTargetList (in rewriteManip.c) — the var-renumbering and substitution primitives the rewriter uses to splice trees together.

Rule definition (rewriteDefine.c)

• DefineRule — execute CREATE RULE; parse-analyze the action/qual then call DefineQueryRewrite.
• DefineQueryRewrite — the core: validate the event/relkind combination, enforce the ON SELECT = view invariant, optionally convert a relation to a view, and InsertRule into pg_rewrite.
• checkRuleResultList — verify a SELECT/RETURNING target list is type-compatible (and for views, name-compatible) with the relation.
• InsertRule — write the pg_rewrite catalog row; SetRelationRuleStatus flips pg_class.relhasrules and broadcasts an SI invalidation.

Row-level security seam (rowsecurity.c)

• get_row_security_policies — return securityQuals (read-side) and withCheckOptions (write-side) for an RTE, plus hasRowSecurity / hasSubLinks flags; the rewriter’s only entry into the RLS subsystem.

Recursive-CTE clause expansion (rewriteSearchCycle.c)

• rewriteSearchAndCycle — rewrite a SEARCH / CYCLE recursive CTE into a plain recursive union with extra bookkeeping columns.
• make_path_rowexpr — build the ROW(...) used for the search/cycle column.

Key data shapes

• RewriteRule (rewrite/prs2lock.h) — ruleId, event (a CmdType), qual, actions, enabled, isInstead. A relation’s rules hang off rel->rd_rules (RuleLock, also in prs2lock.h; rel->rd_rules is declared in utils/rel.h).
• QuerySource enum (nodes/parsenodes.h) — QSRC_ORIGINAL, QSRC_PARSER, QSRC_INSTEAD_RULE, QSRC_QUAL_INSTEAD_RULE, QSRC_NON_INSTEAD_RULE; tags each Query with where it came from.
• RangeTblEntry (nodes/parsenodes.h) — the rewriter flips rtekind from RTE_RELATION to RTE_SUBQUERY for views and writes securityQuals / security_barrier for RLS and barrier views.
• PRS2_OLD_VARNO / PRS2_NEW_VARNO (nodes/primnodes.h) — the reserved range-table indices (1 and 2) the parser assigns to OLD / NEW inside rule actions.

Position hints (as of 2026-06-05, REL_18 273fe94)

Symbol	File	Line
QueryRewrite	rewriteHandler.c	4635
RewriteQuery	rewriteHandler.c	3947
fireRIRrules	rewriteHandler.c	2026
fireRules	rewriteHandler.c	2458
matchLocks	rewriteHandler.c	1671
rewriteRuleAction	rewriteHandler.c	350
CopyAndAddInvertedQual	rewriteHandler.c	2355
rewriteTargetListIU	rewriteHandler.c	808
ApplyRetrieveRule	rewriteHandler.c	1746
get_view_query	rewriteHandler.c	2549
rewriteTargetView	rewriteHandler.c	3281
view_has_instead_trigger	rewriteHandler.c	2588
error_view_not_updatable	rewriteHandler.c	3186
markQueryForLocking	rewriteHandler.c	1926
AcquireRewriteLocks	rewriteHandler.c	147
DefineRule	rewriteDefine.c	190
DefineQueryRewrite	rewriteDefine.c	224
checkRuleResultList	rewriteDefine.c	506
InsertRule	rewriteDefine.c	52
get_row_security_policies	rowsecurity.c	98
rewriteSearchAndCycle	rewriteSearchCycle.c	203
make_path_rowexpr	rewriteSearchCycle.c	117

Source verification (as of 2026-06-05)

Claims in this document were checked against the REL_18_STABLE working tree at commit 273fe94. The load-bearing ones:

• QueryRewrite is the single entry point and runs exactly three steps. Read in full: step 1 RewriteQuery(parsetree, NIL, 0, 0), step 2 the foreach over querylist calling fireRIRrules(query, NIL), step 3 the canSetTag assignment loop. The asserts parsetree->querySource == QSRC_ORIGINAL and parsetree->canSetTag confirm it is only applied to top-level queries. Verified by reading QueryRewrite.

• A view is a relation with one ON SELECT DO INSTEAD _RETURN rule. DefineQueryRewrite’s event_type == CMD_SELECT branch requires relkind VIEW or MATVIEW, a single unconditional CMD_SELECT action, event_qual == NULL, and the name ViewSelectRuleName (_RETURN). get_view_query finds that rule by scanning rd_rules for the CMD_SELECT event and returns its single action. Verified by reading both functions.

• View expansion mutates the RTE in place: RTE_RELATION → RTE_SUBQUERY. ApplyRetrieveRule ends with rte->rtekind = RTE_SUBQUERY; rte->subquery = rule_action; rte->security_barrier = RelationIsSecurityView(relation);. Verified by reading ApplyRetrieveRule.

• View-expansion recursion is bounded by activeRIRs, write-rule recursion by rewrite_events. fireRIRrules raises errmsg("infinite recursion detected in rules for relation …") if list_member_oid(activeRIRs, …) before pushing the OID; RewriteQuery raises the same error from its rewrite_events (relation, event) scan. Verified by reading both recursion guards.

• Conditional INSTEAD rules emit a qual_product default copy with the inverted qual. fireRules, under QSRC_QUAL_INSTEAD_RULE and !*instead_flag, calls CopyAndAddInvertedQual, which (per its header comment) adds 'AND rule_qual IS NOT TRUE' — explicitly not NOT x — to get three-valued logic right. Verified by reading fireRules and CopyAndAddInvertedQual.

• Auto-updatable views pull the base relation up; conditional INSTEAD rules block this. RewriteQuery’s !instead && relkind == VIEW && !view_has_instead_trigger(...) branch calls error_view_not_updatable when qual_product != NULL, else rewriteTargetView, which opens the base rel with RowExclusiveLock, appends a new RTE, and re-points Vars with ChangeVarNodes. Verified by reading the branch and rewriteTargetView.

• RLS is the last pass of fireRIRrules and prepends to securityQuals. The RLS loop runs after view expansion, CTE, and SubLink passes; it calls get_row_security_policies and does rte->securityQuals = list_concat(securityQuals, rte->securityQuals) and appends to parsetree->withCheckOptions. The header comment states the reason it is done last (to avoid query_tree_walker recursing into the new quals twice). Verified by reading the RLS block of fireRIRrules and the get_row_security_policies signature in rowsecurity.c.

• AcquireRewriteLocks is what locks relations referenced by stored rule actions. rewriteRuleAction and ApplyRetrieveRule both call AcquireRewriteLocks(rule_action, true, …) immediately after copyObject-ing the stored action, because the original parse did not lock those relations. The function’s header documents the schema-stability purpose and the forExecute lock-mode logic. Verified by reading AcquireRewriteLocks and both call sites.

• SEARCH/CYCLE expansion happens inside fireRIRrules. The cte->search_clause || cte->cycle_clause loop calls rewriteSearchAndCycle; the file header in rewriteSearchCycle.c documents the exact target rewrite (extra ROW(...) / array columns). Verified by reading the loop and rewriteSearchAndCycle’s prologue.

Open questions

1. Exact interaction of WITH CHECK OPTION accumulation between rewriteTargetView and the RLS withCheckOptions. Both append to parsetree->withCheckOptions, but the ordering and how the executor distinguishes view CHECK OPTIONs from RLS WITH CHECK at run time was not fully traced here. Investigation path: read ExecWithCheckOptions in executor/execMain.c and WithCheckOption in parsenodes.h.

2. How pg_rewrite rule storage and relcache rd_rules are kept in sync across DDL. SetRelationRuleStatus broadcasts an SI invalidation, but the precise relcache rebuild path for RuleLock was taken on the comment’s word, not separately traced. Investigation path: RelationBuildRuleLock in utils/cache/relcache.c.

3. Whether subquery pull-up always undoes view expansion, or when the view sub-select survives into the plan. ApplyRetrieveRule leaves an RTE_SUBQUERY; the planner usually flattens it, but security-barrier views and certain constructs prevent pull-up. The exact conditions belong to postgres-planner-overview.md. Investigation path: pull_up_subqueries in optimizer/prep/prepjointree.c.

Beyond PostgreSQL — Comparative Designs & Research Frontiers

Pointers, not analysis. Each bullet is a starting handle for a follow-up doc; depth here is intentionally shallow.

• INGRES query modification — the direct ancestor. Stonebraker, Wong, Kreps & Held, “The Design and Implementation of INGRES” (ACM TODS 1976) introduced query modification as the unifying mechanism for views, protection, and integrity. PostgreSQL’s rewriter is recognizably the same idea, generalized by the POSTGRES rule system (dbms-papers/systemr.md is the System R counterpart; the POSTGRES design papers in the bibliography are the closer lineage). A side-by-side of INGRES tuple-substitution semantics vs. PostgreSQL’s Query-tree splicing would show what three decades of engineering bought.

• The general rule system PostgreSQL retired. POSTGRES (Stonebraker, Rowe & Hirohama 1990) supported a far more ambitious production-rule system, including ON SELECT rules on arbitrary relations and tuple-level rule firing. Modern PostgreSQL deliberately narrowed this: ON SELECT rules are restricted to views, and per-tuple reactivity moved to triggers. The history of why the general rule system was cut back — optimizer interaction, surprising semantics, the rise of triggers — is a worthwhile design retrospective.

• Triggers vs. rules as competing rewrite/react mechanisms. A rule is a compile-time (rewrite-time) query transformation; a trigger is a run-time per-row callback. The two can express overlapping intents (e.g. updatable views via INSTEAD rules vs. INSTEAD OF triggers) with very different performance and semantics. The PostgreSQL documentation’s “Rules versus Triggers” chapter is the canonical comparison; a doc mapping which problems each tool fits would complement this one. Cross-ref: postgres-ddl-execution.md (trigger definition).

• Materialized views and incremental view maintenance. PostgreSQL materialized views are storage-backed and refreshed by re-running the defining query (REFRESH MATERIALIZED VIEW), so fireRIRrules skips them. The research frontier is incremental maintenance — propagating base-table deltas into the MV without full recomputation (Gupta & Mumick’s classic IVM survey; more recent work in systems like DBToaster and the pg_ivm extension). How a delta-propagation rewrite would slot into this pipeline is an open design question.

• Security-barrier views and RLS leak-proofing. The security_barrier flag set in ApplyRetrieveRule and the securityQuals placement in the RLS pass both exist to stop the planner from pushing a cheap-but-leaky user-defined predicate below a security qual (the “leaky view” class of information-disclosure bugs). The planner side — qual_is_pushdown_safe, LEAKPROOF function marking — is where the guarantee is actually enforced. Cross-ref: postgres-row-level-security.md and postgres-planner-overview.md.

• Recursive query SEARCH/CYCLE as a rewrite vs. a runtime feature. PostgreSQL implements SQL:2016 SEARCH/CYCLE purely as a rewrite into a plain recursive union with bookkeeping columns (rewriteSearchAndCycle), so the executor never learns the feature exists. Other engines push cycle detection into the recursive-CTE operator at run time. The trade-off — rewrite simplicity vs. run-time efficiency of array-append cycle tracking — is a concrete comparison point.

Sources

Textbook chapters (under knowledge/research/dbms-general/)

• Database System Concepts (Silberschatz, Korth & Sudarshan, 7e) — §4.2 “Views”: view definition as a stored query, view expansion as recursive substitution (“replacing the view relation by … its definition,” “repeating … until no more view relations are present”), and §4.2.3 on materialized views (physical storage vs. substitution).

Papers (lineage; see .omc/plans/postgres-paper-bibliography.md)

• Stonebraker, Wong, Kreps & Held, “The Design and Implementation of INGRES” (ACM TODS 1976) — query modification as the unifying mechanism for views, protection, and integrity. The rewriter’s direct intellectual ancestor.
• Stonebraker & Rowe, “The Design of POSTGRES” (SIGMOD 1986) and Stonebraker, Rowe & Hirohama, “The Implementation of POSTGRES” (IEEE TKDE 1990) — the production rule system (PRS/PRS2), views-as-rules, and the query-rewrite vs. tuple-level rule strategies. These are the design origin of rewriteHandler.c.
• (Comparative, not captured) Gupta & Mumick, “Maintenance of Materialized Views” (IEEE Data Eng. Bull. 1995) — incremental view maintenance, for the materialized-view follow-up.

PostgreSQL source (under /data/hgryoo/references/postgres/, REL_18 273fe94)

• src/backend/rewrite/rewriteHandler.c — QueryRewrite, RewriteQuery, fireRIRrules, fireRules, ApplyRetrieveRule, rewriteRuleAction, rewriteTargetView, matchLocks, AcquireRewriteLocks, CopyAndAddInvertedQual, get_view_query, view_has_instead_trigger, error_view_not_updatable, rewriteTargetListIU.
• src/backend/rewrite/rewriteDefine.c — DefineRule, DefineQueryRewrite, checkRuleResultList, InsertRule; the ON SELECT = view invariant and the relation-to-view conversion.
• src/backend/rewrite/rewriteSearchCycle.c — rewriteSearchAndCycle, make_path_rowexpr; SEARCH/CYCLE clause expansion.
• src/backend/rewrite/rowsecurity.c — get_row_security_policies; the RLS entry the rewriter calls (mechanics owned by the cross-ref doc).
• src/backend/rewrite/rewriteManip.c — ChangeVarNodes, OffsetVarNodes, ReplaceVarsFromTargetList, AddInvertedQual; the var-manipulation primitives.
• src/backend/tcop/postgres.c — pg_rewrite_query, the caller of QueryRewrite.

Cross-references (sibling docs that own adjacent mechanism)

• postgres-analyze-transform.md — parse analysis that produces the Query tree the rewriter consumes; OLD/NEW PRS2_*_VARNO assignment for rule actions.
• postgres-row-level-security.md — the RLS subsystem behind get_row_security_policies: policy selection, USING vs. WITH CHECK, permissive/restrictive combination, FORCE ROW LEVEL SECURITY.
• postgres-planner-overview.md — consumes the rewriter’s output list of Query trees; subquery pull-up that usually undoes view expansion; security-barrier qual pushdown safety.
• postgres-ddl-execution.md — CREATE VIEW / CREATE RULE / trigger definition that populate pg_rewrite and pg_trigger.
• postgres-executor.md — runs the planned, post-rewrite queries; enforces WITH CHECK OPTION and RLS write checks at run time.