#Execution Contract

This document defines the behavioral contract for query execution in JustyBaseLite-netezza. It serves as the single source of truth for how queries flow through the system, what the result shapes look like, and how cancellation, retries, and streaming work.

#Shared lifecycle owner

@justybase/database-runtime/execution is the canonical orchestration layer. It receives an ExecutionRequest plus an injected backend and owns execution identity, statement attempts, timeout/cancellation, reconnect policy, event sequence, resource cleanup and the terminal summary. Editor state, history, authorization, credentials and transport messages remain in product adapters.

The ordered lifecycle is:

execution-started
  → statement-started
  → columns / rows / progress (zero or more)
  → statement-completed | statement-failed
  → retrying + statement-started ... (at most once when eligible)
  → execution-terminal
  → batch-completed

Every event has a sequence number increasing within one execution. Exactly one execution-terminal and one compatibility batch-completed event are emitted, both with the same immutable summary. A detached observer receives no later callbacks but does not cancel execution or release its results. A failed or cancelled statement may carry partial row/limit progress.

#Execution Modes

#1. Single Query (singleQueryExecutor.ts)

Entry points:

• runQueryRaw(options) — returns structured QueryResult
• runQuery(...) — legacy JSON wrapper around runQueryRaw
• runExplainQuery(...) — captures NOTICE messages for Netezza EXPLAIN output
• runQueryWithCatalog(...) — temporarily switches database catalog for cross-db queries

Flow:

runQueryRaw
  → resolveQueryVariables (${var..} substitution)
  → resolveConnectionName
  → clear stale document cancellation flag (document-bound execution only)
  → shared ExecutionOrchestrator
    → desktop backend acquires a persistent or transient connection
    → streamingManager.executeAndFetch
  → log to history
  → return QueryResult

Retry logic: On isConnectionBrokenError (including Netezza's Connection protocol is invalid; reconnect is required), the shared owner retries a persistent execution once only when both the original and fully expanded SQL contain one allow-listed, call-free read-only statement. Writes, executable macros, function/sequence expressions, multi-statement payloads, and ambiguous SQL fail without replay and report that the database outcome may be unknown.

Cancellation note: single-query execution now clears stale StreamingManager cancellation state at the start of a new document-bound run so a previously cancelled execution does not poison the next single-statement run.

#2. Batch Sequential (batchQueryExecutor.ts → runQueriesSequentially)

Purpose: Execute multiple SQL statements one-at-a-time over a single connection.

Flow:

for each query:
  → shared statement-started → queryStartCallback
  → desktop backend → streamingManager.executeAndFetch
  → shared statement-completed/failed → queryEndCallback
  → logQueryToHistoryAsync
  → resultCallback (partial results to UI)

Key behaviors:

• Cancellation is checked before each statement and after each execution
• On a broken connection, the shared orchestrator reconnects and repeats only the failed statement, and only for one proven safe read-only statement
• Cancellation observed during reconnect cleanup is terminal: the persistent connection is not replayed, and the execution emits one cancelled status.
• yieldAfterStatement pauses briefly after every 5th fast statement to prevent UI starvation
• A single statement can emit multiple QueryResult objects when executeAndFetch(...) returns multiple internal result sets
• A reconnect keeps the same execution ID and emits retrying before exactly one terminal success or error; it never emits a pre-retry terminal error

#3. Batch Streaming (batchQueryExecutor.ts → runQueriesWithStreaming)

Purpose: Execute multiple statements with progressive chunk delivery for real-time UI updates.

Flow: Same as sequential, but uses streamingManager.executeWithStreaming which delivers data via onChunk callback.

#StreamingManager (StreamingManager.ts)

An instance is created during extension activation and exposed through the compatibility facade in queryCancellation.ts. It manages driver command lifecycle, low-level cancellation and row delivery; it does not own the logical execution lifecycle. Deactivation disposes command maps, pending-abort state and cleanup timers.

#Command Registration

All URIs are normalized via normalizeUriKey for Windows drive-letter case insensitivity.

#Cancellation Protocol

User clicks "Cancel"
  → markCancelled(documentUri)
    → cancelledUris.add(normalizedKey)
    → state.isCancelled = true

During execution:
  → isCancelled(documentUri) checked on each row read
  → if cancelled: consumeRestAndCancel(reader, cmd, ...)
    → cancelFirst=true: cmd.cancel() + reader.close()
    → if close fails: fall back to drain loop
    → if drain times out (5s): prompt user for DROP SESSION
    → if user picks "Keep Waiting": extended drain (15s)
    → if extended drain fails: log warning, give up
  → cmd.cancel() called as final step

Important: clearCancelled(uri) must be called at the start of each new document-bound execution sequence to reset stale cancellation flags.

#Row Limits

finalRowLimit = maxRows ?? limit (from getQueryConfig)

When reached:

• executeAndFetch: stops reading, calls consumeRestAndCancel(cancelFirst=true), sets limitReached=true
• executeWithStreaming: same behavior, sends partial chunk before stopping

#Long Query Alert

If netezza.longQueryAlertThreshold is > 0 (default: 10 minutes), a setTimeout warning is registered at execution start and cleared in the finally block.

#Result Shapes

#QueryResult (single query)

interface QueryResult {
  columns: { name: string; type?: string; scale?: number }[];
  data: unknown[][];
  rowsAffected?: number;
  limitReached?: boolean;
  message?: string;      // For DDL/DML with no result set
  sql?: string;          // The executed SQL
}

Invariants:

• columns.length === 0 means DDL/DML (no result set) → message is set
• data.length === 0 is valid (zero-row SELECT) → columns is still populated
• limitReached === true means the server had more rows than finalRowLimit
• rowsAffected comes from cmd._recordsAffected, -1 treated as "unknown"

#StreamingChunk (progressive delivery)

interface StreamingChunk {
  columns: { name: string; type?: string; scale?: number }[];
  rows: unknown[][];
  isFirstChunk: boolean;
  isLastChunk: boolean;
  totalRowsSoFar: number;
  limitReached: boolean;
}

Invariants:

• columns is non-empty only on isFirstChunk === true
• Final chunk always has isLastChunk === true (even if rows is empty)
• Zero-row results produce exactly 1 chunk: {isFirstChunk: true, isLastChunk: true, rows: []}
• totalRowsSoFar is cumulative across all chunks
• Streaming only covers the first result set for a statement; additional result sets are not progressively streamed

#InternalResultSet (multi-result-set)

interface InternalResultSet {
  columns: ColumnDefinition[];
  rows: unknown[][];
  limitReached: boolean;
}

• executeAndFetch returns InternalResultSet[] — one per result set
• executeWithStreaming only handles the first result set (Netezza convention)

#Type Detection (resultColumnMetadata.ts)

Column type resolution follows this priority:

1. getDeclaredTypeName (if available) → normalize → return
2. getColumnMetadata → check declaredTypeName → normalize → return
3. Character type enrichment (VARCHAR/NVARCHAR/CHAR/NCHAR):
   • Extract base type from getTypeName
   • Find length from: metadata → getTypeLength → schemaTable.ColumnSize → typeMod → declared name
   • Format as TYPE(length)
4. Fallback: getTypeName → normalize

Numeric scale is only returned for numeric/decimal/integer/float types (via NUMERIC_SCALE_TYPE_ALIASES set). Sources: column metadata → schemaTable.NumericScale.

#Retry Protocol

Conditions for retry:

• Error matches isConnectionBrokenError (TCP reset, ECONNRESET, or Netezza's Connection protocol is invalid; reconnect is required, etc.)
• No prior reconnect attempt in the logical execution
• Document has a persistent connection (keepConnectionOpen)
• Original and expanded SQL resolve to one conservatively allow-listed read-only statement
• Streaming has not delivered a chunk to its consumer

Retry flow:

1. Close the persistent connection (closeDocumentPersistentConnection)
2. Re-execute only the failed statement (which is the sole statement for single)
3. Emit retrying using the original execution ID, then a new statement-started attempt
4. The logical execution emits exactly one terminal status; cancellation before replay produces cancelled and no second database execution

Writes, DDL, calls, executable macros, function/sequence expressions, multi-statement/ambiguous SQL, and streams with already delivered data are not replayed. Partial streamed rows stay visible; the terminal error explains why retry was suppressed. Cancellation and rejected safe-execution confirmation invoke the statement-failure hook so transaction-scoped metadata synchronization cannot retain stale state.

The dialect-neutral allow-list treats PostgreSQL positional parameters ($n), JSON path operators (#>/#>>), and compact leading line comments as inert syntax. A standalone # and an inline compact --text remain ambiguous and therefore suppress automatic retry.

#Resource and error ownership

• Each execution owns a resource scope. Readers, commands, timers or other adapter resources registered there are disposed once in reverse order.
• Backend cleanup runs after the scope on success, error, cancellation and timeout. Repeated cancellation/disposal is idempotent.
• Cleanup failures are retained in cleanupErrors. They turn an otherwise successful execution into an error but do not replace an earlier database failure or its cause.
• Late driver callbacks are ignored after cancellation, terminal transition or observer detachment; late command handles are cancelled best-effort.
• Persistent reconnect closes only the target connection. Transient execution closes only the connection lease it acquired; there is no execution-ID based fallback that can close an unrelated connection.
• Mutable registries are instance-owned. Two orchestrators, extension activations or API server instances cannot cancel or dispose one another.

Desktop Result Panel append messages carry the stable result-set identity, an authoritative row offset, and a monotonic chunk sequence. Duplicate or delayed chunks are ignored. A gap, out-of-order delivery, or premature completion causes one source-scoped requestResultSync; incremental delivery remains blocked until an authoritative hydrate replaces the partial webview state. Cancellation, source replacement, hydrate, and disposal reset the transport cursor. Legacy unsequenced messages remain accepted for protocol compatibility.

#UI States

#Result Panel Hydration and Export Contract

#Active-source streaming behavior

1. First chunk for the active source causes a full hydrate so the webview receives complete result-set metadata.
2. Subsequent chunks for the same active source are sent incrementally via appendRows.
3. Last chunk for the active source is followed by streamingComplete, including totalRows and limitReached.
4. Zero-row streamed results still hydrate as a real result set and still end with streamingComplete.

#Inactive-source streaming behavior

1. Background sources buffer streamed rows in ResultStateManager.
2. Incremental appendRows and streamingComplete messages are not sent for inactive sources.
3. Buffered background results become visible on the next hydrate when the source becomes active.

#Cancellation and partial-result behavior

1. ResultStateManager.cancelExecution(sourceUri, currentRowCounts) marks result sets as cancelled and truncates buffered rows to the counts reported by the webview when provided.
2. Partial results kept after cancellation remain exportable.
3. Export hydration must ignore stale row indices that no longer exist after truncation rather than throwing.

#Multi-result export behavior

1. Excel multi-sheet export uses ExportManager.hydrateExportData(...) as the authoritative hydration step.
2. Empty result sets are skipped.
3. Result-set order, sheet names, and isActive flags are preserved for hydrated export items.
4. Column filtering and requested row ordering are preserved per sheet, while stale row indices are ignored.

#Hydration observability

1. Full result-panel hydrate sends emit a structured result_panel.hydrate perf event.
2. Event metadata includes hydrate reason, active source, result-set count, total row count, and executing-source count.
3. Payload size is bucketed as xs, s, m, l, or xl based on serialized MessagePack bytes.
4. After the webview completes a hydrate render pass, it reports result_panel.first_paint back to the host with duration, payload size, active source, row counts, and execution state.
5. The host keeps a rolling local sample window for result_panel.first_paint so dogfooding sessions can be summarized without parsing raw logs.
6. netezza.showResultPanelPerformanceStats is the maintainer-facing entry point for reviewing that rolling first-paint baseline.
7. netezza.clearResultPanelPerformanceStats resets that local baseline before a fresh profiling session.
8. Large-payload regressions should be treated as a Phase 4 concern even if correctness tests still pass.

#Result-panel execution state UX

1. The loading overlay remains the blocking affordance while the active source is executing.
2. A lightweight status banner communicates non-blocking execution state for the active source when extra attention is needed: retrying, error, or cancelled. Successful completion should not add a redundant line above the grid.
3. Cancelled executions should explicitly tell the user that partial results may still be available.
4. Error state should remain visible even when the active result set is not the Logs tab.
5. When a source is active but has no buffered results yet, the grid surface should render an explicit empty-state card instead of a synthetic placeholder log.
6. Non-tabular completion states such as DDL success, empty result sets, and invalid render data should render recovery-oriented state cards with short next-step guidance.
7. Banner copy should include the active source label where possible and distinguish between zero-row success, partial-error success, and cancellation with or without retained rows.
8. Result-set tabs should expose lightweight status cues for error, cancelled-partial, and empty-result states so users can navigate to the right surface without relying on the grid banner.
9. Error views should offer a direct recovery path back to Logs in addition to Copilot/error details, so diagnostic flow does not depend on manual tab hunting.
10. Row View is a supported record/details surface and should stay reactive to selection changes plus active-result switches, rather than behaving like a static side panel.
11. A dedicated Value Viewer should be available for individual cells with long or structured content, instead of forcing users to inspect everything through Row View or truncated grid cells.

#Disk-backed results (SQLite spill)

See SQL_RESULTS_FILTERING.md for how Loaded rows, All rows + LIMIT, and disk-backed filtering relate.

1. Host spill triggers at min(memoryRowThreshold, rowThreshold) (defaults: 25 000 / 500 000). This is independent of the webview stream cap (DISK_BACKED_WEBVIEW_STREAM_CAP = 250 000).
2. When spill activates during streaming, the host clears ResultSet.data, subsequent chunks insert directly into SQLite, and the webview receives diskBackedActivate with the stable resultSetId, a first page, and a ~600-row scroll window.
3. While still streaming above the webview cap but before/after spill, the webview may receive rowCountUpdate instead of full appendRows payloads. The message carries the optional stable resultSetId when available; a mismatched identity is ignored and requests an authoritative sync, while legacy messages without an identity remain compatible.
4. After streaming completes, disk-backed sources must not be fully re-hydrated into the webview when node:sqlite is available.
5. Filters, sort, global search, aggregations, and export operate on the full SQLite store via DiskQuerySpec (not the visible window only).
6. Disk-backed grouping uses lazy SQL GROUP BY tree expansion (queryDiskGroups); group/leaf pages load in 600-row windows with scroll-triggered pagination.
7. Optional idle spill (idleSpillMinutes, default 0 = disabled) moves eligible in-memory result sets to SQLite after inactivity; hiding the panel can spill inactive sources immediately when enabled.

#Key Configuration