CUBRID SHOW Commands — System-Introspection Virtual Scans Over Server Runtime State

Contents:

• Theoretical Background
• Common DBMS Design
• CUBRID’s Approach
• Source Walkthrough
• Cross-check Notes
• Open Questions
• Sources

Theoretical Background

The catalog tells the system what exists — classes, attributes, indexes, domains. It does not tell the system what is happening — which threads are running, which critical sections are held, how full the page buffer is, where the log tail sits, what each transaction is doing. The runtime answer to those questions is private to the engine: it lives in worker-thread structs, in latch state, in the LRU queue, in log_Gl.hdr. None of it is on disk in a heap file, and none of it is reachable through ordinary access methods.

Three textbook ideas frame how a relational engine surfaces that runtime state to SQL.

The system catalog as self-description. Selinger 1979 (Access Path Selection in a Relational DBMS, System R) and Hellerstein and Stonebraker (Anatomy of a Database System, Red Book Ch. 4) treat the catalog as a uniform first-class table tree the engine can SELECT from. The strict reading is that only static schema lives there. The looser reading — accepted by every modern engine — is that the catalog can also expose dynamic state via tables whose rows are computed on demand. CUBRID picks the looser reading: alongside _db_class/_db_attribute there is a parallel surface of “show pseudo-tables” materialised from server memory on each query.

Dynamic management views (DMVs). A DMV is a relation whose rows are not stored anywhere — they are generated by a function the engine invokes when the query touches the view. The operator phrases questions in SQL (“which threads are blocked?”, “how many pages are dirty?”) and the optimiser applies predicates, joins, aggregates without knowing the mix in advance. The trade-off is operational: the values are read with various levels of consistency (snapshot, dirty read, latched), and the act of reading must not perturb the system being measured. Petrov’s Database Internals (Ch. 1) frames it as a thin layer over what the engine already does for EXPLAIN ANALYZE.

The Volcano leaf for synthetic relations. Graefe’s iterator model (Volcano, TKDE 1994; Query Evaluation Techniques, ACM CS 1993) makes every operator open/next/close. Any leaf — heap, B+Tree, list file, set, JSON-table, virtual catalog — implements the same three methods. SHOW commands are exactly such a leaf: a per-type start builds a private snapshot, a next synthesises one tuple at a time, an end frees it. The rest of the executor — predicates, projections, sorts, joins — works against the synthetic source identically to a heap. That uniformity is what makes SHOW THREADS WHERE Status = 'TS_WAIT' ORDER BY Conn_index typecheck and execute without any code outside the scan manager learning that “threads” is not a real table.

The three combine into one design: rewrite SHOW X as SELECT * FROM (virtual-source-X), encode the virtual source as one more entry in the access-method catalogue (S_SHOWSTMT_SCAN next to S_HEAP_SCAN and S_LIST_SCAN), and let per-type start/next/end functions own the synthesis. show_scan.c is the dispatch table that ties them together; show_meta.c plus pt_make_query_showstmt are the parser-side facade that performs the rewrite.

Common DBMS Design

The four major engines reach for SHOW/DMV functionality at slightly different layers.

PostgreSQL splits the surface in two: pg_stat_* views over the statistics collector (a background process aggregates per-backend counters into shared memory, and ordinary heap-style scans read them) and set-returning functions (pg_locks, pg_blocking_pids, pg_get_indexdef) wrapped by FunctionScan. The first is for durable accumulated counts, the second for instantaneous state.

MySQL offers three doors over the same data: SHOW <thing> as syntactic sugar, INFORMATION_SCHEMA as ANSI-aligned views, and performance_schema as ring-buffer event tables. Internally SHOW PROCESSLIST rewrites to a SELECT over a synthetic information_schema table; the access method is a custom storage engine (ha_perfschema, ha_information_schema) routed through the standard handler API.

Oracle uses V$/GV$ dictionary views over X$ fixed tables — C arrays inside the kernel projected as relations by a hard-coded “fixed table” access driver (kqfta). Every V$ is SELECT … FROM x$…; the rest of the SQL stack treats X$ as normal source. AWR/ASH persist V$ snapshots into real tables for retention.

SQL Server uses DMVs (sys.dm_exec_*, sys.dm_os_*) — catalog views resolved by the engine to internal “system-internal table” sources, mechanically analogous to Oracle’s X$.

CUBRID picks the most direct of these designs. The grammar accepts SHOW <name>, the parser rewrites it into SELECT * FROM (PT_SHOWSTMT) whose derived table is a PT_SHOWSTMT node carrying type and arguments, the optimiser emits an access spec with TARGET_SHOWSTMT, and the executor opens an S_SHOWSTMT_SCAN. No separate INFORMATION_SCHEMA engine, no X$ fixed-table driver, no statistics collector. The whole story is a single new SCAN_TYPE, a parser-side metadata table naming columns and arguments per show type, and per-type start/next/end function pointers attached at server-init time. The trade-off: lighter plumbing than MySQL or Oracle, but no third-party plugin point — extending CUBRID with a new SHOW means modifying SHOWSTMT_TYPE, show_meta.c, the grammar, and showstmt_scan_init together.

CUBRID’s Approach

The full path from SHOW THREADS typed at csql to a result set involves four layers. The grammar maps SHOW syntax to a SHOWSTMT_TYPE enum value. The parser rewrites the SHOW into a SELECT over a synthetic source. The optimiser/XASL stage emits an access spec with TARGET_SHOWSTMT. The executor opens a virtual scan whose next calls a per-type tuple generator from a function-pointer dispatch table.

flowchart LR
  GR["csql_grammar.y\nSHOW THREADS"]
  PT["parser_support.c\npt_make_query_showstmt\n→ SELECT ∗ FROM PT_SHOWSTMT"]
  MD["show_meta.c\nshowstmt_get_metadata\n(columns, args, orderby)"]
  XA["xasl_generation.c\nTARGET_SHOWSTMT spec\nshow_type, arg_list"]
  EX["query_executor.c\nqexec_open_scan\n→ scan_open_showstmt_scan"]
  SM["scan_manager.c\nS_SHOWSTMT_SCAN\nstart / next / end"]
  SS["show_scan.c\nshow_Requests[show_type]\n.start_func/.next_func/.end_func"]
  TG["per-subsystem generators\ncsect_start_scan\nthread_start_scan\nlog_active_log_header_start_scan\n…"]
  GR --> PT --> MD
  PT --> XA --> EX --> SM --> SS --> TG

SHOWSTMT_TYPE — the dispatch discriminator

The whole system rotates around a flat enum declared in storage_common.h. Members include SHOWSTMT_ACCESS_STATUS, SHOWSTMT_VOLUME_HEADER, SHOWSTMT_ACTIVE_LOG_HEADER, SHOWSTMT_ARCHIVE_LOG_HEADER, SHOWSTMT_SLOTTED_PAGE_HEADER/_SLOTS, SHOWSTMT_HEAP_HEADER/SHOWSTMT_ALL_HEAP_HEADER, SHOWSTMT_HEAP_CAPACITY/SHOWSTMT_ALL_HEAP_CAPACITY, SHOWSTMT_INDEX_HEADER/SHOWSTMT_INDEX_CAPACITY and _ALL_INDEXES_* variants, SHOWSTMT_GLOBAL_CRITICAL_SECTIONS, SHOWSTMT_JOB_QUEUES, SHOWSTMT_TIMEZONES/_FULL_TIMEZONES, SHOWSTMT_TRAN_TABLES, SHOWSTMT_THREADS, SHOWSTMT_PAGE_BUFFER_STATUS, terminated by SHOWSTMT_END.

SHOWSTMT_END sizes the dispatch table — adding a new SHOW means appending an enum value before it, and the dispatch arrays in show_meta.c and show_scan.c get one more slot for free. The enum’s home in storage_common.h (rather than a header local to show_scan.c) is what lets the parser, executor, and per-subsystem code share identifiers without a circular include — storage_common.h is at the bottom of CUBRID’s include DAG.

The grammar is a thin map from SQL words to enum values. Because some show types take arguments and some do not, and because LIKE/WHERE is admissible for only a subset, the grammar splits the names into a family of nonterminals (show_type, show_type_of_like, show_type_of_where, show_type_arg1, show_type_arg1_opt, show_type_arg_named, show_type_id, show_type_id_dot_id). The SHOW production fans out into matching actions; every one of them converges on a single call to pt_make_query_showstmt (this_parser, type, args, like_where_syntax, like_or_where_expr), which is the rewrite point.

Parser-side: pt_make_query_showstmt rewrites SHOW into SELECT

The rewrite is the architectural lever that lets the rest of the engine treat SHOW commands as ordinary queries. Rather than carrying SHOW through the optimiser as a special statement type, the parser fabricates a PT_SELECT whose FROM clause is a derived table backed by a PT_SHOWSTMT node. pt_make_query_showstmt (in parser_support.c) follows a fixed shape: look up meta = showstmt_get_metadata (type), check meta->only_for_dba and reject with ER_AU_DBA_ONLY if needed, allocate a PT_SELECT with PT_TYPE_STAR projection, allocate a PT_SHOWSTMT node carrying show_type and resolved show_args (via pt_resolve_showstmt_args_named/_unnamed), attach as derived table with derived_table_type = PT_IS_SHOWSTMT, fold any LIKE/WHERE shortcut into the SELECT’s WHERE, and synthesise ORDER BY from meta->orderby.

Three structural choices show through. Authorization is checked at parse time: SHOW types whose only_for_dba flag is set fail with ER_AU_DBA_ONLY before any plan is built — consistent with CUBRID’s general “fail early on grammar-level errors” stance. The argument grammar is data-driven: the meta->args table from show_meta.c declares whether the type expects positional or named arguments and the resolver picks the matching helper. The default ordering is metadata-attached: each metadata block can carry an orderby array, and the rewrite synthesises an ORDER BY clause from it so that, e.g., SHOW HEAP HEADER OF c always returns rows in the same logical order even though the underlying generator’s enumeration order is implementation-defined.

The metadata that drives this rewrite lives in show_meta.c as a per-type function returning a static SHOWSTMT_METADATA carrying the show type, only_for_dba, alias_print, the cols[] column-name/type table, an optional orderby[], an optional args[] (positional or named), an optional semantic_check_func, and a synthesised showstmt_attrs (DB_ATTRIBUTE *).

showstmt_metadata_init, called once at startup, walks every show type and turns the cols[] declaration into a real DB_ATTRIBUTE * chain so name resolution can treat the derived table as if it had real columns. From the parser’s perspective after init, SHOW THREADS looks identical to SELECT * FROM SOME_RELATION_WITH_26_COLUMNS — column-name lookup, type checking, predicate pushdown all just work.

Executor side: S_SHOWSTMT_SCAN and the access-method dispatch

XASL generation translates a PT_SHOWSTMT-backed FROM clause into an access spec with access = TARGET_SHOWSTMT, carrying show_type and the resolved arg_list. qexec_open_scan switches on the target type and calls scan_open_showstmt_scan (thread_p, s_id, grouped, curr_spec->single_fetch, curr_spec->s_dbval, val_list, vd, curr_spec->where_pred, curr_spec->s.showstmt_node.show_type, curr_spec->s.showstmt_node.arg_list).

scan_open_showstmt_scan fills the union arm scan_id->s.stsid (a SHOWSTMT_SCAN_ID): set scan_id->type = S_SHOWSTMT_SCAN, run scan_init_scan_id with readonly_scan = true and fixed = true, peek every argument DB_VALUE out of the regu_variable_list via fetch_peek_dbval, point out_values[] at the val_list slots, copy show_type into the struct, set cursor = 0 and ctx = NULL (the start_func fills ctx), and scan_init_scan_pred against the WHERE predicate.

The SHOWSTMT_SCAN_ID itself is intentionally minimal: show_type (the dispatch discriminator), arg_values/arg_cnt (peeked at open time), out_values/out_cnt (pointing into val_list slots), cursor (starts at zero), void *ctx (per-show-type opaque snapshot), and scan_pred (the WHERE filter). The single void *ctx is the key polymorphism: every show type stores its private snapshot behind that pointer, and only its own next/end functions know what shape it has. The scan manager never dereferences it.

Four switch (scan_id->type) arms in scan_manager.c route SHOW scans to show_scan.c — case S_SHOWSTMT_SCAN: in scan_start_scan, scan_next_scan_local, scan_end_scan, and scan_close_scan. The split between end (drop the snapshot) and close (drop the pointer arrays) parallels heap and list scan teardown — every scan kind in CUBRID separates iterator-state release from request-state release.

show_scan.c: function-pointer dispatch table

Inside show_scan.c, three function-pointer typedefs and a parallel array indexed by SHOWSTMT_TYPE form the entire dispatch. START_SCAN_FUNC takes (THREAD_ENTRY*, int show_type, DB_VALUE **arg_values, int arg_cnt, void **ctx) and returns an error code; NEXT_SCAN_FUNC takes (THREAD_ENTRY*, int cursor, DB_VALUE **out_values, int out_cnt, void *ctx) and returns SCAN_CODE; END_SCAN_FUNC takes (THREAD_ENTRY*, void **ctx) and returns an error code. A SHOW_REQUEST struct bundles (show_type, start_func, next_func, end_func), and static SHOW_REQUEST show_Requests[SHOWSTMT_END] is the dispatch array.

showstmt_scan_init populates the table at server startup. The shape is uniform: every entry sets all four fields. For example, SHOWSTMT_VOLUME_HEADER registers disk_volume_header_{start,next,end}_scan; SHOWSTMT_GLOBAL_CRITICAL_SECTIONS registers csect_start_scan plus the generic showstmt_array_next_scan/showstmt_array_end_scan; SHOWSTMT_THREADS registers thread_start_scan plus the same generic array walker/freer.

A pattern is visible at a glance. Some show types stream tuples — disk_volume_header_*, heap_header_capacity_*, btree_index_*, log_active_log_header_* — meaning their start_func builds a small private context and their next_func synthesises tuples on demand by walking the underlying object. Others materialise an array up-front — csect_start_scan, thread_start_scan, tz_timezones_start_scan, pgbuf_start_scan — meaning start_func allocates a SHOWSTMT_ARRAY_CONTEXT, fills it row-by-row in one shot, and the generic showstmt_array_next_scan simply re-emits one row per next call. The choice is per-type and is essentially a consistency policy: array materialisation captures a snapshot of state at scan open; streaming reflects state as it changes during the scan.

The shared array-walking infrastructure

The “array” path is the most reused machinery in show_scan.c. The context is small — DB_VALUE **tuples, num_cols, num_used, num_total. showstmt_alloc_tuple_in_context 1.5×-geometric-reallocs tuples when full (amortised O(1) insertion), allocates a DB_VALUE row of num_cols, and initialises every cell to db_make_null so a partial fill plus error abort can pr_clear_value everything safely. Both tuples and per-row vals are db_private_* allocations, so they live on the connection’s private heap and disappear with it on error.

The generic walker showstmt_array_next_scan is a six-line loop: bounds-check the cursor against num_used (return S_END if out of range), pr_clone_value every column from ctx->tuples[cursor][i] into out_values[i], return S_SUCCESS. The deep clone (rather than a borrow) is what lets showstmt_next_scan safely call pr_clear_value (stsidp->out_values[i]) between rows — every row is a fresh deep copy into the executor’s val_list slots.

Streaming generators: log header example

For show types whose source is a single record (e.g. SHOWSTMT_ACTIVE_LOG_HEADER over log_Gl.hdr), the start function captures one snapshot under the appropriate latch and stores it in a private context; the next function decodes it on demand. log_active_log_header_start_scan shows the recurring two-mode argument pattern: when arg_values[0] is DB_TYPE_NULL, read log_Gl.hdr under LOG_CS_ENTER_READ_MODE/LOG_CS_EXIT; when it is a path, fileio_open the file, read the first page as a LOG_PAGE, copy the embedded LOG_HEADER, and validate magic + db_creation against the running DB (else ER_IO_MOUNT_FAIL). The corresponding next_func is a single-tuple emitter: cursor 0 → decode one row from ctx->header, cursor ≥ 1 → S_END. Cursor-bounded emission is the contract the dispatcher relies on; it is what makes SHOWSTMT_TYPE orthogonal to “how many rows”.

Two array-style examples: critical sections and threads

csect_start_scan (in critical_section.c) is the canonical fixed-cardinality case: 12 columns, CRITICAL_SECTION_COUNT rows, copy every csectgl_Critical_sections[i] field into a DB_VALUE row. There is no per-tuple latching — SHOW CRITICAL SECTIONS is a diagnostic, and torn reads of single aligned ints are acceptable. The shape: db_make_int (&vals[idx++], csect->cs_index), db_make_string (&vals[idx++], csect_name (csect)), then a small switch over csect->rwlock rendering “%d readers” / “1 writer” / “none”, then waiting-reader/-writer counts and owner-thread info.

thread_start_scan (in show_scan.c itself, gated by SERVER_MODE) demonstrates composition with a subsystem-specific iterator. Instead of a fixed loop, it allocates a SHOWSTMT_ARRAY_CONTEXT sized for thread_num_total_threads () × THREAD_SCAN_COLUMN_COUNT and dispatches through thread_get_manager ()->map_entries (thread_scan_mapfunc, thread_p, ctx, error). The mapper thread_scan_mapfunc produces one tuple per non-TS_DEAD thread, populating THREAD_SCAN_COLUMN_COUNT = 26 columns. For threads blocked on a lock the lockwait columns are filled from thrd->lockwait; otherwise they become DB_NULL. The per-row branch is what makes SHOW THREADS more than a scaled-up top — it integrates state from the lock manager, connection table, and worker pool into a single relational view.

End of life

showstmt_end_scan calls the per-type end_func. Array-style types use showstmt_array_end_scan (which showstmt_free_array_contexts the snapshot and nulls the pointer); streaming-style types free their own context type. After _end_scan, scan_close_scan frees the per-scan arg_values and out_values arrays. The two-step teardown mirrors how heap and list scans separate iterator-state release from request-state release — the manager stays uniform regardless of which leaf is underneath.

Predicate evaluation around the synthetic source

scan_next_showstmt_scan (in scan_manager.c) wraps the per-type next_func with the same predicate-evaluation loop the other scan kinds use: drive showstmt_next_scan until it returns S_SUCCESS, evaluate stsidp->scan_pred.pr_eval_fnc (pred_expr, vd), continue on V_FALSE/V_UNKNOWN when qualification == QPROC_QUALIFIED, return on success. The predicate is the one the parser pushed down. SHOW commands compose exactly like heap scans: WHERE filters rows; ORDER BY sorts through the regular sort operator; JOIN against _db_class glues them to user-class metadata; aggregates produce summary rows. The synthetic source is only a leaf substitute.

Why one void*ctx and not a typed union

Keeping ctx as void * rather than another tagged union (like SCAN_ID::s) is deliberate. Every subsystem that exposes state owns its own context shape (ACTIVE_LOG_HEADER_SCAN_CTX, the disk-manager’s volume-header context, SHOWSTMT_ARRAY_CONTEXT, the heap-capacity context, …) and show_scan.c should not need to know any of them. A typed union would force show_scan.c to depend on every subsystem’s headers — inverting the project’s dependency direction (subsystems push generators into show_Requests; show_scan.c does not pull their internals). The void * is the architecturally correct place to lose type information.

What the operator can ask

Because the rewrite produces a regular SELECT, the operator’s vocabulary is SQL: SHOW THREADS WHERE Status = 'TS_WAIT', SHOW CRITICAL SECTIONS, SHOW VOLUME HEADER OF 0, SHOW LOG HEADER [OF '/path/to/active_log'], SHOW HEAP HEADER OF c, SHOW ALL HEAP CAPACITY, SHOW INDEX HEADER OF c.idx, SHOW PAGE BUFFER STATUS, SHOW TRAN TABLES, SHOW TIMEZONES. The grammar nonterminal a name belongs to determines which filters it accepts directly after SHOW: show_type_of_like/show_type_of_where enumerate names that admit LIKE/WHERE as a syntactic shortcut; the rewrite folds the shortcut into the SELECT’s WHERE clause, and the surrounding SELECT … WHERE of course works for any name.

Complete extension sequence

Adding a new SHOW (say SHOW REPLICATION STATUS) requires touching exactly five places: (1) append SHOWSTMT_REPLICATION_STATUS before SHOWSTMT_END in storage_common.h; (2) write metadata_of_replication_status returning a SHOWSTMT_METADATA and register it in showstmt_metadata_init; (3) add the REPLICATION STATUS → SHOWSTMT_REPLICATION_STATUS arm to whichever show_type* nonterminal matches the argument shape; (4) write repl_status_{start,next,end}_scan in the owning subsystem; (5) populate show_Requests[SHOWSTMT_REPLICATION_STATUS] in showstmt_scan_init. No XASL change, no optimiser change, no new authentication path beyond only_for_dba. The whole pipeline composes via the rewrite-into-SELECT trick.

Source Walkthrough

The symbols below are stable anchors. Line numbers go in the position-hint table at the end of the section.

Public dispatch (executor → show_scan). showstmt_scan_init populates show_Requests[] once at startup (idempotent via show_scan_Inited). showstmt_start_scan/_next_scan/_end_scan look up the per-type function pointer and call it; _next_scan clears prior out_values before each call. scan_open_showstmt_scan and scan_next_showstmt_scan (both in scan_manager.c) sit between the executor and show_scan.c, holding the SHOWSTMT_SCAN_ID and the WHERE predicate.

Function-pointer types and dispatch table. START_SCAN_FUNC, NEXT_SCAN_FUNC, END_SCAN_FUNC (typedefs), SHOW_REQUEST (one row), show_Requests[SHOWSTMT_END] (file-scope dispatch array), SHOWSTMT_TYPE in storage_common.h as the discriminator.

Shared array-walking infrastructure. SHOWSTMT_ARRAY_CONTEXT, showstmt_alloc_array_context, showstmt_alloc_tuple_in_context (geometric realloc), showstmt_free_array_context, showstmt_array_next_scan (generic walker), showstmt_array_end_scan (generic teardown).

Per-type generators by subsystem. disk_volume_header_* (VOL_HEADER), log_active_log_header_* and log_archive_log_header_* (LOG_HEADER, possibly from offline file), spage_header_* and spage_slots_* (slotted page), heap_header_capacity_start_scan shared by heap_header_next_scan/heap_capacity_next_scan, btree_index_* (B+Tree header/capacity, single or all), css_user_access_status_start_scan, css_job_queues_start_scan, tz_timezones_start_scan/tz_full_timezones_start_scan, logtb_descriptors_start_scan (tran tables), thread_start_scan plus mapper thread_scan_mapfunc (26 columns), csect_start_scan in critical_section.c (12 columns), pgbuf_start_scan (page buffer LRU/dirty counters).

Parser-side facade. pt_make_query_showstmt (the rewrite), showstmt_metadata_init/_final (synthesise DB_ATTRIBUTE * from static cols[]), showstmt_get_metadata/_attributes, the metadata_of_<name> family (one function per show type), pt_check_show_index/pt_check_show_heap semantic hooks.

Grammar entry points (csql_grammar.y). show_type (no args), show_type_of_like and show_type_of_where (admit LIKE/WHERE shortcut), show_type_arg1/_arg1_opt (positional arg), show_type_arg_named (named args), show_type_id/show_type_id_dot_id (OF class_name [DOT identifier]).

Position hints as of 2026-05-01

Symbol	File	Line
SHOWSTMT_TYPE enum / SHOWSTMT_END	src/storage/storage_common.h	933 / 961
SHOWSTMT_ARRAY_CONTEXT	src/query/show_scan.h	32
START_SCAN_FUNC/NEXT_SCAN_FUNC typedefs	src/query/show_scan.c	66
SHOW_REQUEST / show_Requests[]	src/query/show_scan.c	72 / 94
THREAD_SCAN_COLUMN_COUNT	src/query/show_scan.c	81
showstmt_scan_init	src/query/show_scan.c	102
showstmt_next_scan/_start_scan/_end_scan	src/query/show_scan.c	252 / 285 / 311
showstmt_alloc_array_context/_free_/_alloc_tuple_	src/query/show_scan.c	338 / 375 / 404
showstmt_array_next_scan/_end_scan	src/query/show_scan.c	449 / 479
thread_scan_mapfunc / thread_start_scan	src/query/show_scan.c	500 / 820
SHOWSTMT_SCAN_ID / S_SHOWSTMT_SCAN	src/query/scan_manager.h	302 / 86
scan_open_showstmt_scan	src/query/scan_manager.c	3849
S_SHOWSTMT_SCAN start/end/close/next dispatches	src/query/scan_manager.c	4380 / 4845 / 5056 / 5261
scan_next_showstmt_scan	src/query/scan_manager.c	6820
qexec_open_scan TARGET_SHOWSTMT arm	src/query/query_executor.c	7553
SHOWSTMT_METADATA	src/parser/show_meta.h	65
showstmt_metadata_init / showstmt_get_metadata	src/parser/show_meta.c	946 / 743
pt_make_query_showstmt	src/parser/parser_support.c	6828
SHOW production family	src/parser/csql_grammar.y	7222
show_type nonterminal	src/parser/csql_grammar.y	7335
show_type_arg1 / _arg1_opt	src/parser/csql_grammar.y	7416
show_type_id / _id_dot_id	src/parser/csql_grammar.y	7445
csect_start_scan	src/thread/critical_section.c	1625
log_active_log_header_start_scan	src/transaction/log_manager.c	9208

Line numbers will drift on the next reformat or insertion; the symbol names are the canonical anchors.

Cross-check Notes

• SHOWSTMT_TYPE lives in storage_common.h, not in a header local to show_scan.c or show_meta.h. Deliberate — the parser, executor, and per-subsystem generators share the enum without circular includes — but it does spill some surface into storage_common.h that is not strictly storage. Add new show types there.
• SHOWSTMT_NULL = SHOWSTMT_START = 0 is a placeholder. No metadata or dispatch entry is registered for it; callers that pass it hit the next_func == NULL defensive branch in showstmt_next_scan and get an immediate S_END.
• thread_start_scan’s declaration is in show_scan.h, while every other *_start_scan is declared in its owning subsystem’s header. The historical reason is that thread_scan_mapfunc lives in show_scan.c (gated by SERVER_MODE); moving it would require either exporting thread_get_manager ()->map_entries more widely or duplicating the mapper.
• THREAD_SCAN_COLUMN_COUNT = 26 is duplicated between show_scan.c and the parser-side metadata_of_threads. The assert (idx == THREAD_SCAN_COLUMN_COUNT) at the end of the mapper fires in debug builds when they diverge — cross-modify both whenever a column is added.
• csect_start_scan does not latch each CS while reading. Diagnostic torn reads of single aligned ints are accepted. Do not extend the row with composite fields whose update is non-atomic without revisiting this.
• The only_for_dba check is at parse time only. Once the SELECT is built and dispatched, no second authorization gate fires. A hostile client bypassing pt_make_query_showstmt could in principle send an XASL with TARGET_SHOWSTMT and reach the data; treat the parser-side check as the only gate.
• WHERE predicate evaluation runs after the generator emits the row. SHOW THREADS WHERE Status = 'TS_WAIT' still walks every thread and allocates 26 DB_VALUEs per row. No predicate pushdown into generators today.
• Argument peeking happens once at scan_open_showstmt_scan time. The grammar restricts arguments to literals/identifiers, so this is not observable; anyone broadening the grammar must revisit fetch_peek_dbval use.
• _end_scan frees ctx; scan_close_scan frees arg_values/out_values. Skipping _end_scan leaks ctx. New abort paths in qexec_clear_* should preserve the pairing.

Open Questions

• Predicate pushdown into generators. Streaming generators that iterate over many objects (heap_capacity_next_scan, B+Tree variants) could short-circuit on cheap predicates if they saw the WHERE. Is the engineering cost worth the operator-experience win, or is the current “always full scan, filter at the manager layer” simplicity preferable?
• Pluggable SHOW types. Every show type is hard-coded in SHOWSTMT_TYPE + showstmt_metadata_init + showstmt_scan_init. Should optional subsystems (CDC, replication, broker stats) be able to register dynamically?
• Snapshot isolation per show type. Array-style sees a snapshot; streaming sees partial state across rows. The distinction is implicit in which next_func is registered — should it be documented per type?
• Stats output for show scans. scan_print_stats_* emit a generic "show" stat with no breakdown between generator time and predicate time. Finer-grained timing would help diagnose SHOW ALL HEAP CAPACITY on large catalogues.
• Column versioning. Adding a column to an existing show type silently shifts ordinal positions. Should show types carry a version, or do operators rely on named-column projection?

Sources

Code-only document; no raw/ analyses were consumed. Direct sources: src/query/show_scan.{c,h} (dispatch table, array-walking, thread mapper), src/query/scan_manager.{c,h} (S_SHOWSTMT_SCAN dispatch, SHOWSTMT_SCAN_ID, predicate loop), src/query/query_executor.c (TARGET_SHOWSTMT arm), src/parser/show_meta.{c,h} (metadata, semantic checks), src/parser/parser_support.c (pt_make_query_showstmt), src/parser/csql_grammar.y (SHOW productions), src/storage/storage_common.h (SHOWSTMT_TYPE enum), src/transaction/log_manager.c and src/thread/critical_section.c (generator examples).

Textbook framing draws on Selinger et al. (System R, 1979), Graefe (Volcano, TKDE 1994; Query Evaluation Techniques, ACM CS 1993), Hellerstein and Stonebraker (Anatomy of a Database System, Red Book Ch. 4), and Petrov (Database Internals, Ch. 1, 7). Cross-engine comparisons reference public documentation of PostgreSQL pg_stat_* and SRFs, MySQL INFORMATION_SCHEMA and performance_schema, Oracle V$/X$, and SQL Server sys.dm_* DMVs.