The Reference Desk: Glossary and Diagnostic Guides

52.1.1 PLAIN — in simple words#

1. A useful definition connects an everyday idea to a precise technical meaning. It also marks where that meaning changes with context or implementation.
2. Similar words do not always mean the same thing. A database, database engine, database server and database file refer to different parts of the system.
3. Begin with the plain meaning, then use the linked section to inspect assumptions and mechanisms. Memorising a term without its boundary can make a mistaken explanation sound more convincing.
4. The A–Z register collects the vocabulary blocks from the manuscript. Where a term appears in several contexts, the relevant definitions and locations remain visible rather than being silently merged into one misleading sentence.

52.1.2 PLAIN — a picture in your head#

1. A city map tells you where a street is; it does not describe every room in every building. The glossary locates an idea, and the chapter supplies the detailed route.
2. Two streets can share a familiar name in different towns. In the same way, “consistency” can refer to several distinct properties unless the surrounding discussion names the model.
3. Where the comparison breaks: technical vocabulary is not governed by one universal naming authority. Standards, product documentation and informal usage can use related words differently.

52.1.3 PLAIN — a worked example#

1. Look up transaction. The everyday meaning is a group of related changes treated as one controlled unit. The technical discussion adds commit, rollback, isolation, durability and the boundary around external effects.
2. Look up idempotency. The plain idea is that repeating the same intended operation need not produce another effect. Chapter 26 and the capstone add scoped identity, payload comparison, transaction placement and retention.
3. Look up consistency before using it in an argument. A business invariant, a replica freshness requirement and linearizability are different claims. A system can satisfy one and fail another.
4. A glossary answer becomes useful when it leads to the relevant question: Which changes? Which identity? Which observation order? Which failure? Which version of the product?

52.1.4 PLAIN — what is really happening inside#

1. The glossary builder extracts named terms and their definitions from the WORDS blocks and associates them with chapter/section identifiers.
2. The website groups entries alphabetically and offers text filtering. Chapter search uses titles, section terms and chapter text rather than pretending a keyword match establishes technical correctness.
3. A repeated term can have several entries or locations. That is preferable to discarding context simply to make a smaller count look tidier.

52.1.5 TECHNICAL — the engineer’s version#

1. Use the source register when a definition is standard- or product-specific. The glossary’s role is navigation; it does not supersede the cited specification.
2. Treat identifiers in a reference system as stable keys. A title can be edited while a chapter number and section identity preserve links across formats.
3. Generated vocabulary is checked for resolvable source locations. Mechanical extraction does not independently verify every definition; factual accuracy still depends on the manuscript and its cited evidence.

52.1.6 WORDS — remember these#

1. Glossary: a vocabulary reference — definitions connected to their contexts and teaching locations. Cross-reference: a route to related material — a stable chapter, section or source identifier rather than a guessed page number. Implementation boundary: where a claim stops generalising — the product, version and configuration for which a detail is established.

52.2.1 PLAIN — in simple words#

1. Read a query as a precise question about a chosen population. First understand what one input row means, then what joins, filters and grouping do to that population.
2. Choose a type by the value’s meaning. Counts, money, names, dates, instants and missing values should not become interchangeable strings merely because strings are easy to store.
3. Use explicit ordering when order matters and bound values when inputs come from outside the SQL text.
4. A correct query still needs appropriate authority, transaction context and an honest interpretation of its result.

52.2.2 PLAIN — a picture in your head#

1. Mira asks Dev to take particular folders, match related pages, discard ineligible entries, group the remaining facts and sort the answer.
2. Each instruction changes the question. Accidentally matching each order page to every packing slip can multiply the answer even when Dev adds the numbers correctly.
3. Where the comparison breaks: a database optimiser can execute a logically equivalent plan in a different physical order. The readable conceptual sequence is not a claim about the engine’s exact execution schedule.

52.2.3 PLAIN — a worked example#

1. The canonical agreed-line calculation is quantity multiplied by agreed unit price in paise, summed over the intended four lines. Its result is 28,650 paise. It is not a sum of today’s catalogue prices.
2. This small reference table connects common intentions to important cautions. It is not a complete SQL grammar.

3. COUNT(*) and COUNT(nullable_column) can differ because they count different things. Preserve the denominator required by the question rather than choosing whichever expression gives the more attractive number.
4. SUM(DISTINCT amount) does not generally repair a multiplied join. Two legitimate lines can have equal amounts and both should count. Fix the grain and relationship, not the coincidence of values.

52.2.4 PLAIN — what is really happening inside#

1. Parsing establishes a valid statement; name/type resolution connects it to objects and operations; planning chooses access paths; execution produces the result under the database’s rules.
2. Representation decisions affect correctness. An integer amount needs a unit and range; a decimal needs scale and rounding policy; a timestamp needs a meaning and time basis.
3. A constraint can reject selected invalid values. It cannot determine whether a supplied measurement, currency or identity matches the outside world without a trusted observation or policy.

52.2.5 TECHNICAL — the engineer’s version#

1. PostgreSQL and SQLite are not interchangeable implementations of every type, function or transaction behaviour. Use the product/version source attached to the example, especially around NULL, typing, locking and schema changes. [S58] [S80] [S86]
2. A compact representation checklist follows. It describes design questions rather than claiming one universally best physical type.

3. Keep SQL syntax examples and executable files aligned. A code block labelled as a sketch should not be treated as a complete application merely because it resembles runnable SQL.

52.2.6 WORDS — remember these#

1. Query grain: what one result row means — the level created by joins, projection and grouping. Tie-breaker: an additional ordering key — a rule making otherwise tied results deterministic where required. Representation contract: meaning attached to stored values — units, range, missingness and interpretation beyond the physical type.

52.3.1 PLAIN — in simple words#

1. A symptom is the starting observation, not the conclusion. “The total is wrong” and “the database is broken” are different statements.
2. Choose a small next observation that can distinguish plausible explanations. Broad changes made before preserving evidence can destroy the information needed to understand the failure.
3. Keep read-only inspection separate from repair. A diagnostic query does not authorise deleting the rows it happens to find.
4. Record the database, tenant, time, revision and exact query or operation involved. A result from the wrong environment can be perfectly accurate and irrelevant.

52.3.2 PLAIN — a picture in your head#

1. A shop light does not turn on. Mira first checks whether one bulb, one room or the whole building is affected. She does not immediately replace every wire.
2. The scope of the symptom determines the next useful check. A systematic route reduces guessing and avoids damaging evidence.
3. Where the comparison breaks: data-system failures can be partial and concurrent. The same query can legitimately observe different states under different snapshots or replicas.

52.3.3 PLAIN — a worked example#

1. Use this wrong-total route on a disposable copy or through authorised read-only inspection:

Unexpected total
  -> confirm metric definition, unit and population
  -> confirm tenant, time basis and source version
  -> compare contributing primary keys
  -> inspect join multiplicity before aggregation
  -> inspect NULL, filters and unmatched rows
  -> compare agreed values with mutable catalogue values
  -> reconcile raw contributions and declared exclusions
  -> preserve evidence; propose a bounded correction

2. Use this slow-query route without assuming an index is the answer:

Slow request
  -> locate where the time is spent
  -> separate queueing, locks, database work and network
  -> capture query, parameters, plan and observation scope
  -> compare estimates with observed work where safe
  -> check workload, selectivity, memory and storage waits
  -> test one justified change on representative data
  -> compare equal results and full latency distributions

3. Use this timeout route before retrying a write:

Caller did not receive a result
  -> preserve the original request identity
  -> ask whether the operation's status can be queried
  -> replay only under its documented same-command policy
  -> distinguish committed, rejected and still-unknown outcomes
  -> do not create a new operation merely to make uncertainty disappear

4. Each route chooses an observation. None says that a symptom alone proves data corruption, failed commit or insufficient hardware.

52.3.4 PLAIN — what is really happening inside#

1. Diagnosis compares hypotheses with observations. A good next check eliminates or narrows explanations while changing as little relevant state as possible.
2. Evidence can be time-sensitive. Query plans, lock waits and replica lag can change; record their observation window rather than presenting them as permanent properties.
3. A repair should have its own preconditions, scope, validation and recovery plan. The urgency of a symptom does not remove those responsibilities.

52.3.5 TECHNICAL — the engineer’s version#

1. For stale reads, check the requested consistency contract, routing, snapshot and observed replication position. A follower being behind is not automatically corruption.
2. For missing records, check authorisation filters, tenant scope, transaction outcome, lifecycle action and environment before attempting restoration. A correctly hidden row and a lost row require different responses.
3. For suspected corruption, preserve the original evidence and use engine-specific checks on an appropriate copy. Structural checks, checksums and business reconciliation establish different properties; consult Chapters 31–32 before repair.

52.3.6 WORDS — remember these#

1. Symptom: what was observed — a result needing explanation, not an already-proven cause. Hypothesis: a candidate explanation — a claim that should be distinguished from alternatives through suitable evidence. Bounded observation: a deliberately limited check — a query or measurement with named scope and minimal unintended change.

52.4.1 PLAIN — in simple words#

1. A design review should make hidden assumptions visible. Ask what each record means, who may act and what happens when requests overlap or answers are lost.
2. Review the whole path, not only the database diagram. Inputs, workers, caches, exports and recovery procedures can invalidate an otherwise careful schema.
3. Require a counterexample to each important guarantee. Asking “How could this fail?” helps turn an attractive description into a testable contract.
4. A reviewer can identify evidence gaps without claiming the design is impossible. The next step is a targeted test or clarification, not an invented green verdict.

52.4.2 PLAIN — a picture in your head#

1. Before building a new storeroom, Mira asks what it must hold, who gets a key, how deliveries enter and what happens if the only door is blocked.
2. A beautiful floor plan is incomplete until those operating questions have answers.
3. Where the comparison breaks: software can change very quickly. A review of one version should not automatically cover a later edit to a constraint, permission or retry policy.

52.4.3 PLAIN — a worked example#

1. Apply these questions to the capstone before extending it:

2. A new payment integration changes the external-effect boundary. The local task’s once-only test does not become proof of once-only charging.
3. A new tenant export changes access coverage. The original detail-query test is not enough; the export’s scope and content need separate tests.
4. A new retention policy changes replay guarantees if it removes request identities. The design must state what happens after that protection window ends.

52.4.4 PLAIN — what is really happening inside#

1. Reviews compare the proposed mechanisms with requirements and counterexamples. They expose which promises rely on unverified assumptions.
2. A small set of well-chosen adversarial cases can be more informative than a large set of near-identical successes. The goal is evidence coverage, not a decorative test count.
3. Review findings should retain severity, scope, proposed response and verification status. Author approval of a plan does not by itself close an implementation defect.

52.4.5 TECHNICAL — the engineer’s version#

1. Separate safety properties, progress properties and operational objectives. “Never create two accepted orders for one scoped request” differs from “eventually deliver every valid notification” and from “respond within 200 ms.”
2. Identify failure assumptions: process crash, device loss, network partition, byzantine behaviour or accidental operator changes. A mechanism designed for one fault model should not inherit another model’s guarantee silently.
3. The review worksheet is an original reference aid. It records questions, not an automatic approval algorithm for every possible data system.

52.4.6 WORDS — remember these#

1. Safety property: a prohibited outcome never occurs — a correctness claim such as no duplicate accepted effect under the stated model. Progress property: required work can eventually advance — a liveness claim dependent on assumptions about failures and resources. Fault model: the failures considered — the boundary within which a mechanism’s reasoning and tests are intended to apply.

52.5.1 PLAIN — in simple words#

1. A test record should let another person repeat the important steps and understand the result. “It worked” omits the input, environment, operation and expected outcome.
2. A recovery record needs a specific source, destination and time boundary. Restoring the wrong backup correctly is still the wrong recovery.
3. Failed and skipped steps should remain visible. They are not automatically covered by the successes around them.
4. Templates organise evidence; filling the boxes does not manufacture observations that were never made.

52.5.2 PLAIN — a picture in your head#

1. A recipe notebook records ingredients, oven setting and actual result. Leaving out the temperature makes a successful trial hard to repeat.
2. A recovery checklist records which box was opened and which pages were compared. Checking only that a box exists cannot fill the result column.
3. Where the comparison breaks: execution environments can differ invisibly through library versions, configuration and concurrent work. Reproducibility requires more than copying visible commands.

52.5.3 PLAIN — a worked example#

1. Use this original bounded-test template for a new exercise:

Claim:
Candidate identity:
Environment and versions:
Input identities and scope:
Assumptions and excluded failures:
Operation and authority:
Expected output and state:
Actual output and state:
Negative or interruption control:
Result: pass / fail / error / skipped / unresolved
Evidence location:
What this result does not establish:

2. Use this recovery template before treating a backup as useful evidence:

Recovery purpose and authorised scope:
Backup identity, time and dependencies:
Required keys/configuration, without embedding secrets:
Fresh isolated destination:
Target recovery point and declared objectives:
Procedure and stop conditions:
Structural checks:
Key/value and application reconciliation:
Post-backup lifecycle decisions to reapply:
Access-boundary checks:
Actual duration and unresolved loss interval:
Approval required before serving restored data:

3. For a replay test, record both attempts with the same scoped identity and compare effects after each. A response that merely repeats the same text is not enough if stock changed twice.
4. For a denied-operation test, inspect state and returned information. The error message is only one observable result.

52.5.4 PLAIN — what is really happening inside#

1. A test harness constructs controlled conditions, invokes the candidate and checks assertions. A recovery rehearsal applies a procedure to a selected historical state and validates the resulting destination.
2. Both need a reproducible identity for the tested material. Digests support byte comparison, while version and environment records explain how those bytes were used.
3. A result can be narrow and still valuable. The discipline is to keep the useful finding without converting it into a wider unsupported guarantee.

52.5.5 TECHNICAL — the engineer’s version#

1. Use machine-readable reports alongside human interpretation. Record failures, errors and skips distinctly rather than treating every completed process as a successful test. [S201]
2. Restore evidence should compare business-level identities and values in addition to engine checks. A structurally valid database can still contain the wrong dataset.
3. Keep credentials out of the evidence bundle. Record how authority was established without copying the secret material needed to exercise it.

52.5.6 WORDS — remember these#

1. Test fixture: the controlled input state — synthetic or appropriately authorised data used for a specified check. Reproducibility record: enough context to repeat a result — candidate, inputs, environment, operations and observations. Skipped check: a check not executed under current conditions — neither a pass nor proof of failure of the underlying feature.

52.6.1 PLAIN — in simple words#

1. Technical behaviour changes across products and versions. The reference beside a claim tells you where its documented meaning came from and which scope was consulted.
2. A standard, a vendor implementation and a teaching model are different sources of authority. An original shop example can demonstrate arithmetic without becoming an industry standard.
3. Read sources when applying a mechanism to real work. The book provides a path to them, not a promise that every future release preserves every detail.
4. The final source register combines the earlier source identifiers with the added references. Old consultation dates remain historical; they are not relabelled as newly rechecked merely because a new PDF was built.

52.6.2 PLAIN — a picture in your head#

1. Mira keeps the instruction manual for the exact machine in the shop. A manual for another model may look similar but describe different buttons and limits.
2. A handwritten experiment note is useful too, provided it says what was tried and does not pretend to be the manufacturer’s specification.
3. Where the comparison breaks: online documentation can change at an unchanged address. A URL alone is weaker evidence than a versioned document plus a consultation date and recorded scope.

52.6.3 PLAIN — a worked example#

1. A paragraph marked [S86] refers to Python’s version-3.13 sqlite3 documentation. It does not establish which exact Python or SQLite build a reader’s computer uses; the executable report records the actual authoring runtime separately.
2. A paragraph about PostgreSQL 17 row policies marked [S187] describes that documented feature and its exceptions. The local SQLite lab does not become a PostgreSQL test because the two appear in one chapter.
3. An invented queue with 120 arrivals/s and 100 completions/s is an original arithmetic model. Its result follows from its assumptions, not from a measurement of a hosting plan.
4. For an applied design, record the selected source version, relevant configuration, local experiment and remaining uncertainty together. No single reference establishes every layer of the final system.

52.6.4 PLAIN — what is really happening inside#

1. The publication builder resolves source identifiers to entries containing author or organisation, title, URL, consultation date and a short description of supported scope.
2. Manuscript references are checked for missing identifiers. That mechanical check prevents dangling citations; it does not prove that every citation is sufficient or that every interpretation is correct.
3. Readers should recheck version-specific details before deployment. When a source changes, preserve the earlier statement’s historical context rather than pretending it always described the new version.

52.6.5 TECHNICAL — the engineer’s version#

1. The final package includes canonical Markdown, structured chapter metadata, glossary/source registers, executable exercises and build/QA records. Derived formats use the same chapter numbering and headings.
2. The cited materials include official database and language documentation, standards, original research and author-hosted technical texts. The synthetic shop, diagrams, chosen protocols and worked arithmetic are original teaching material unless explicitly attributed otherwise.
3. No part of the source register claims independent technical certification of the book. Source support, local testing, visual review and publication approval remain distinct kinds of evidence.
4. The final lesson is the method used throughout: define meaning, name the boundary, inspect the mechanism, test the relevant case and state only what follows. That method is transferable beyond the particular tools in these pages.

52.6.6 WORDS — remember these#

1. Primary source: the originating documentation or research — evidence closer to the specified behaviour than a second-hand summary. Consultation date: when a source was checked — a historical marker that does not turn a moving page into an immutable specification. Version register: the declared implementation/document context — a record distinguishing documented versions from the runtime actually tested.