The whole transaction read model: all twelve *_transaction_view arms,
UNION ALL-ed, unfiltered.
This replaces a UNION that transaction-services assembled per request and
shipped as statement text. Two things follow from moving it server-side.
The statement collapses. A search is now SELECT … FROM transaction_view WHERE … LIMIT … — a few hundred bytes against the ~10.4KB the assembled
union cost, and against the 16,384-byte RDS Proxy parse limit that pinned 471
prod sessions and is the reason the twelve views exist at all. The budget
stops being something new filters have to be measured against.
Arm pruning goes away. The old builder dropped arms an ids / methods /
bankIds filter could not satisfy before the statement was built; a view
takes no parameters, so every search now plans all twelve. That is the
trade this view is here to test — see the pushdown note below.
Pushdown. Nothing here is materialised: a view is a rewrite rule, so
Postgres expands this union in place, flattens it to an appendrel, and pushes
the caller's outer quals into each arm and on through each arm's own
NOT MATERIALIZED CTE to the base tables. The planner therefore sees the same
tree it saw when the union arrived as statement text, and the per-arm plans
are unchanged. What it will NOT do is skip planning an arm no row of which can
match — method is a CASE per arm, not a constant, so there is nothing to
constant-fold — which is exactly the cost the old builder was paying TS to
avoid.
Declaration order is the UNION order and matches the old builder's, so a
regenerated statement stays diffable. It carries no meaning: every arm
projects the same 49 columns and callers sort.
securityInvoker is required, not cosmetic. Every arm is itself a
securityInvoker view over RLS-enabled tables; without it here the union
would run with the owner's row visibility and silently bypass the querying
role's policies.
The whole transaction read model: all twelve
*_transaction_viewarms,UNION ALL-ed, unfiltered.This replaces a UNION that
transaction-servicesassembled per request and shipped as statement text. Two things follow from moving it server-side.The statement collapses. A search is now
SELECT … FROM transaction_view WHERE … LIMIT …— a few hundred bytes against the ~10.4KB the assembled union cost, and against the 16,384-byte RDS Proxy parse limit that pinned 471 prod sessions and is the reason the twelve views exist at all. The budget stops being something new filters have to be measured against.Arm pruning goes away. The old builder dropped arms an
ids/methods/bankIdsfilter could not satisfy before the statement was built; a view takes no parameters, so every search now plans all twelve. That is the trade this view is here to test — see the pushdown note below.Pushdown. Nothing here is materialised: a view is a rewrite rule, so Postgres expands this union in place, flattens it to an appendrel, and pushes the caller's outer quals into each arm and on through each arm's own
NOT MATERIALIZEDCTE to the base tables. The planner therefore sees the same tree it saw when the union arrived as statement text, and the per-arm plans are unchanged. What it will NOT do is skip planning an arm no row of which can match —methodis a CASE per arm, not a constant, so there is nothing to constant-fold — which is exactly the cost the old builder was paying TS to avoid.Declaration order is the UNION order and matches the old builder's, so a regenerated statement stays diffable. It carries no meaning: every arm projects the same 49 columns and callers sort.
securityInvokeris required, not cosmetic. Every arm is itself asecurityInvokerview over RLS-enabled tables; without it here the union would run with the owner's row visibility and silently bypass the querying role's policies.