Issue Overview: PRAGMA Functions and Side Effect Restrictions

SQLite provides two distinct mechanisms for interacting with database configuration parameters and internal states: the traditional PRAGMA command and the newer pragma_* table-valued functions. While many PRAGMAs are accessible through both methods, certain PRAGMAs—such as wal_checkpoint and wal_autocheckpoint—are unavailable as table-valued functions. This discrepancy arises from SQLite’s design philosophy and technical constraints, particularly around side effects.

Key Observations

1. Behavioral Differences:

   • The PRAGMA wal_checkpoint command initiates a write-ahead log (WAL) checkpoint, modifying database state.
   • The PRAGMA wal_autocheckpoint command configures or queries the automatic checkpoint threshold.
   • Both PRAGMAs are unavailable as pragma_wal_checkpoint or pragma_wal_autocheckpoint functions.

2. Functional vs. Procedural Operations:

   • PRAGMA functions (e.g., SELECT * FROM pragma_trusted_schema) are designed for pure read operations—they retrieve values without altering database state.
   • PRAGMA commands (e.g., PRAGMA wal_autocheckpoint = 1000) can modify state, even when they appear to perform a read.

3. Documentation vs. Implementation:

   • The SQLite documentation states that table-valued functions exist only for PRAGMAs that return results without side effects.
   • However, some PRAGMAs (e.g., trusted_schema) are accessible via functions despite having a setter, leading to confusion.

Underlying Conflict
The confusion stems from interpreting “side effects” in the context of PRAGMAs:

• Pure Read PRAGMAs: Return data without modifying connection state (e.g., pragma_trusted_schema).
• State-Modifying PRAGMAs: Alter database or connection state when invoked (e.g., wal_checkpoint).

The critical distinction lies in whether a PRAGMA’s read operation is idempotent (no state change) or non-idempotent (state change).

Possible Causes: Why Specific PRAGMAs Lack Function Equivalents

1. Implicit Side Effects in Read Operations
   Some PRAGMAs modify state even during read operations. For example:

   • PRAGMA wal_checkpoint: Executing this without parameters triggers a passive checkpoint, altering the WAL file.
   • PRAGMA integrity_check: Validates database structure, which may lock the database or generate I/O.

   Such PRAGMAs cannot be safely exposed as functions because their invocation—even for reading—has side effects.

2. Setter Capability Exclusion
   PRAGMAs that allow configuration changes (e.g., wal_autocheckpoint) are excluded from function forms to prevent ambiguity. For example:

   • PRAGMA wal_autocheckpoint = 2000 modifies the automatic checkpoint threshold.
   • Exposing this as a function would imply read-only behavior, but the underlying PRAGMA is inherently dual-purpose (read/write).

   This exclusion avoids misuse scenarios where users might attempt to write via function syntax (e.g., INSERT INTO pragma_wal_autocheckpoint VALUES (2000)).

3. Source Code Implementation Flags
   SQLite’s pragma.c source code uses flags like PragFlg_Result0 and PragFlg_Result1 to determine which PRAGMAs are eligible for function forms. These flags indicate:

   • Whether the PRAGMA returns results.
   • Whether the PRAGMA is read-only.

   For instance, trusted_schema is flagged with PragFlg_Result0, allowing it to function as a read-only table. In contrast, wal_autocheckpoint lacks these flags because its dual read/write nature violates the “no side effects” rule.

4. Documentation Ambiguity
   The documentation does not explicitly list which PRAGMAs have side effects. Users must infer this from descriptions like “queries or sets.” This ambiguity leads to trial-and-error discovery, as seen in the original discussion.

Troubleshooting Steps, Solutions & Fixes

Step 1: Identify PRAGMA Side Effects
Consult the PRAGMA documentation to determine if a PRAGMA modifies state:

• Look for verbs like “sets,” “enables,” or “triggers” in the description.
• Example: wal_checkpoint is documented to “run a checkpoint operation,” confirming its side effects.

Step 2: Verify PRAGMA Function Availability
Use .headers on followed by SELECT * FROM pragma_list; to list all PRAGMAs with function equivalents. If a PRAGMA is absent from this list, it lacks a function form.

Step 3: Use PRAGMA Commands for State Changes
For PRAGMAs excluded from function forms, use traditional syntax:

-- Set wal_autocheckpoint threshold
PRAGMA wal_autocheckpoint = 2000;
-- Trigger a checkpoint
PRAGMA wal_checkpoint;

Step 4: Workaround for Function-Like Queries
To query PRAGMAs without function equivalents, use temporary tables or scripting:

-- Store wal_autocheckpoint value in a temporary table
CREATE TEMP TABLE temp_pragma AS
  SELECT * FROM pragma_trusted_schema;  -- Example for an allowed pragma

Step 5: Review Source Code Flags
For advanced users, inspect SQLite’s pragma.c to check flags like PragFlg_Result0:

// Example from SQLite source code:
{ /* zName:     */ "trusted_schema",
  /* ePragTyp:  */ PragTyp_TRUSTED_SCHEMA,
  /* ePragFlg:  */ PragFlg_Result0 | PragFlg_NoColumns1,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ SQLITE_TrustedSchema },

PRAGMAs lacking such flags are excluded from function forms.

Step 6: Submit Documentation Feedback
If ambiguity persists, propose clarifications to the SQLite documentation. For example, explicitly labeling PRAGMAs with “has side effects” could reduce confusion.

Final Solutions

1. Avoid Function Syntax for Dual-Purpose PRAGMAs: Use PRAGMA commands for PRAGMAs that configure settings or trigger operations.
2. Leverage Function Syntax for Pure Reads: Use pragma_* functions only for PRAGMAs explicitly listed in pragma_list.
3. Custom Wrapper Functions: For advanced use cases, create user-defined functions (UDFs) in a host language (e.g., Python, C) to safely wrap side-effecting PRAGMAs.

By adhering to these guidelines, users can avoid errors like Parse error: no such table: pragma_wal_checkpoint and ensure correct interaction with SQLite’s configuration mechanisms.