Views, Procedures & Server-Side Logic

A materialized view stores the actual result rows of its query on disk, like a table. An ordinary view stores only the query text and recomputes it on every read. You trade freshness and storage for cheap reads of expensive queries.

A sequence is a standalone database object that hands out unique increasing numbers on request, independent of any table. An IDENTITY or serial column is a column wired to such a generator so inserts get a value automatically. Same machinery, different packaging.

A function returns a value and is called inside an expression or query. A procedure is invoked by a CALL statement, returns data through output parameters or result sets, and in most engines may commit or roll back. Functions compute values; procedures perform units of work.

A temporary table holds intermediate rows for one session (or one transaction). Its data is private to the creating session, it lives in a special temporary namespace, it is dropped automatically at session or transaction end, and it is typically unlogged and never crash-recovered.

A BEFORE trigger runs before the row change is applied and can still modify the incoming row or reject the statement. An AFTER trigger runs once the change is applied, so it sees final values including generated keys, but changes it makes to the row image are ignored.

Yes, if the view is updatable. A view stores no rows of its own, so the engine rewrites the write into a write on the underlying base table. Simple views that only project columns and filter rows are usually updatable; views with aggregation or DISTINCT are not.

A view is a named query stored in the database. Selecting from it runs the underlying query against the live tables — it holds no data of its own. Teams use views to name a complex query once, hide columns or rows for access control, and give applications a stable shape while the tables underneath change.

For: fewer network round trips, set-based processing next to the data, one enforcement point for every client, naturally atomic multi-statement work. Against: harder to test and version, deployment is replace-in-place, it burns your least scalable CPU, it fights ORMs, and it ties you to one vendor's language.

Complete refresh re-runs the whole defining query and replaces all stored rows. Incremental (fast) refresh applies only the changes since the last refresh, using a change log of the base tables. It needs recorded deltas and a query shape the engine can maintain incrementally.

It is normal, not a bug. Sequence allocation is non-transactional: a value handed out is consumed even if the transaction rolls back, a crash discards cached values, and concurrent sessions interleave allocations. Sequences guarantee uniqueness, never contiguity.

It is a promise about how stable the result is: deterministic/IMMUTABLE means same inputs always give the same output, STABLE means constant within one statement, VOLATILE means anything. The optimizer uses the promise to cache results, fold constants, push predicates, and allow use in indexes. A false promise silently corrupts results.

Stage in a temporary table when the intermediate is read more than once, when the optimizer needs real statistics on it, or when it should be indexed. Otherwise keep it inline so the optimizer can see and reshape the whole query. Table variables sit in between: real storage, but historically poor cardinality estimates.

A row-level trigger fires once per affected row: zero times for a zero-row UPDATE, a thousand times for a thousand rows. A statement-level trigger fires once per statement regardless, including when zero rows match. Row-level sees individual old and new values; statement-level sees the change as a set, if at all.

Everything a trigger writes commits or rolls back with the caller's transaction, and an error in the trigger aborts the caller's statement. Trigger work extends the transaction, so locks are held longer and the caller waits for it. External calls cannot be rolled back, so they must not be made from a trigger.

Aggregates, GROUP BY/HAVING, DISTINCT, set operations like UNION, window functions, LIMIT, and most multi-table joins. All of them break the one-to-one mapping from a view row back to a single base-table row and column, so the engine cannot decide which row to modify.

The optimizer expands (inlines) the view definition into the calling query and plans the combined statement, so predicates from outside can be pushed inside and unused work can sometimes be eliminated. The view itself adds no runtime cost. Constructs like aggregation, DISTINCT or window functions act as optimization fences that block that merging.

SECURITY INVOKER runs the body with the caller's privileges; SECURITY DEFINER runs it with the owner's. Definer routines let a low-privileged caller perform a narrow privileged action, but they are a privilege-escalation surface: pin the name-resolution search path, revoke PUBLIC execute, own them by a limited role, and validate every input.

A row-level trigger runs once per affected row, so a set-based update becomes five million procedure invocations, often each with its own query. It also multiplies written bytes, log volume and locks, all inside one long transaction. Fix it with statement-level triggers over transition tables, batching, or doing the derived work in the statement itself.

Each trigger's own DML fires more triggers, so A to B to A can loop. Engines stop it with a nesting-depth cap or by disabling self-recursion by default, and the loop aborts the whole transaction. Fix it with change guards, idempotent writes, or by moving the second hop into application code.

Triggers run invisibly: the application code shows one INSERT, but rows change in tables nobody named, and the logic lives outside the application repository. That breaks the trail from symptom to cause. Keeping them manageable means versioning them like code, testing them, and making them discoverable.

Because the stored bytes, any index on them, and any replica or restored dump must all agree. If the expression could return different values at different times, recomputation would disagree with what is on disk. So clock, random, session, and cross-row expressions are rejected.

Add a STORED generated column holding lower(email) or the extracted JSON field, then index that column. Queries filter on the column name and get an index seek. An expression index stores the same computed keys without a visible column, and the planner matches it when the query repeats the expression literally.

Each statement pays a network round trip plus parse and plan cost, so per-row work is latency-bound: a million rows at half a millisecond each is minutes of pure waiting. Fix it by expressing the work as set-based SQL, or batching, before considering a server-side procedure.

Get it from the insert itself — a RETURNING/OUTPUT clause or the driver's generated-keys API — or from the session-scoped last-value call for your own session. Never query MAX(id) or read shared sequence state: those see other sessions' rows.

Scalars come back through OUT or INOUT parameters. Row sets come back either as a result set the procedure emits to the client, as a returned cursor handle the caller iterates, or by writing to a temporary table the caller then queries. Functions instead return a value or a table directly.

PRESERVE ROWS keeps the table and its data until the session ends (the usual default). DELETE ROWS keeps the table definition but empties it at every COMMIT. DROP removes the whole table at COMMIT. Choose DROP for one-transaction scratch space, DELETE ROWS to reuse a definition across many transactions, PRESERVE for session-long staging.

Order is engine-defined, not portable: some engines order by trigger name, some by creation time, some let you pin only a first and last trigger. Renames, re-creations and dump restores silently change it, so keep triggers order-independent.

BEFORE and AFTER triggers run around an operation the engine still performs. An INSTEAD OF trigger replaces it: the original INSERT, UPDATE or DELETE never executes, and whatever the trigger body writes is the entire effect. It is normally defined on a view.

WITH CHECK OPTION makes the engine reject writes that would produce rows the view itself cannot see. LOCAL enforces only the predicates of the view where the clause is written; CASCADED also enforces the predicates of every underlying view in the chain.