Chapter 15 — Quiz

13 questions. Answers at the bottom.

Multiple choice

Q1. A (plain) view: - A) Stores its own copy of the data - B) Is a named query (virtual table) that runs each time you select from it - C) Is always faster than the underlying query - D) Cannot be queried

Q2. A plain view's data is: - A) Always current (reflects base tables) - B) Stale until refreshed - C) Read-only forever - D) Stored on disk

Q3. Three classic reasons to use views are: - A) Speed, storage, backup - B) Simplification, security, abstraction - C) Indexing, partitioning, caching - D) Logging, locking, linking

Q4. A materialized view: - A) Re-runs its query every time - B) Stores the query's results for fast reads (stale until refreshed) - C) Cannot be indexed - D) Updates automatically on base-table changes

Q5. You should use a materialized view when: - A) The data must always be perfectly current - B) The query is expensive, data changes slowly, and slight staleness is OK - C) You need an updatable view - D) The table is tiny

Q6. REFRESH MATERIALIZED VIEW CONCURRENTLY requires: - A) A primary key on a base table - B) A unique index on the materialized view - C) Superuser access - D) An empty matview

Q7. A function differs from a procedure in that a function: - A) Can manage transactions - B) Returns a value and is used in queries; a procedure performs actions via CALL - C) Cannot take parameters - D) Is always written in SQL

Q8. Inside an UPDATE trigger, OLD and NEW refer to: - A) Two unrelated tables - B) The row before and after the change - C) The first and last rows - D) Nothing

Q9. A trigger fires: - A) Only when you call it - B) Automatically on the specified data change (BEFORE/AFTER INSERT/UPDATE/DELETE) - C) Once per query always - D) Only on SELECT

Q10. Querying a plain view vs. running its query directly: - A) The view is always faster - B) Roughly the same — PostgreSQL inlines and optimizes the view - C) The view is always slower - D) The view caches results

True/False

Q11. Triggers run invisibly, so they should be documented and used sparingly, preferring constraints where possible. (True / False)

Q12. A materialized view automatically stays in sync with its base tables. (True / False)

Short answer

Q13. A team caches an expensive aggregate in a materialized view and the dashboard gets fast — but then shows wrong numbers. Diagnose the likely cause and the fix.

---

Answer key

Q1 — B. A view is a stored query; selecting from it runs that query.

Q2 — A. Plain views are always current (no stored data).

Q3 — B. Simplification, security, abstraction.

Q4 — B. A matview stores results; it's stale until refreshed.

Q5 — B. Expensive query + slowly-changing data + tolerable staleness.

Q6 — B. Concurrent refresh needs a unique index (to diff rows without a read lock).

Q7 — B. Functions return values/usable in queries; procedures perform actions and can control transactions.

Q8 — B. OLD = pre-change row, NEW = post-change row.

Q9 — B. Automatically, on the configured event/timing.

Q10 — B. Roughly equal — the view is inlined and optimized with your query; no magic, no cache.

Q11 — True. Hidden, automatic logic is powerful but surprising; document it and prefer constraints for rules they can express.

Q12 — False. It's stale until you REFRESH it.

Q13. The materialized view isn't being refreshed (or not often enough), so it shows old data. Fix: schedule REFRESH MATERIALIZED VIEW [CONCURRENTLY] ... at a cadence matching how fresh the dashboard must be (cron/pg_cron/a job), and choose CONCURRENTLY (with a unique index) if reads must not be blocked during refresh.

Scoring: 11–13 you can package logic well; 8–10 review view vs. matview and triggers; below 8, redo Exercises A, B, D.