SQLite makes PRAGMA results available to SQL queries

Database introspection becomes awkward when metadata arrives through commands that cannot participate in an ordinary query. An application can inspect a table, but then it must filter the result itself or issue another command for each object it discovers. SQLite 3.16.0, released on 2 January, adds experimental PRAGMA functions. They make eligible PRAGMA results available through table-valued functions, so the query language can do more of that work.

The change interests me because SQLite keeps useful database machinery inside a library. Schema inspection belongs close to the SQL engine that understands the schema. Making that information queryable reduces the amount of procedural code required around it, while retaining the existing PRAGMA implementation underneath.

A virtual table around an existing command

The 3.16.0 implementation in pragma.c shows how the feature works. SQLite recognizes names beginning with pragma_, looks up the corresponding PRAGMA, and registers an eponymous virtual table for eligible commands. The table exists through its module name, without requiring the application to create a persistent table containing metadata.

Each virtual-table cursor owns a prepared PRAGMA statement. When SQLite starts a scan, the implementation constructs that statement using the supplied argument and optional schema. Advancing the cursor steps the statement, while reading a visible column returns the corresponding value from its current result row. Closing the cursor finalizes the statement and frees its argument strings.

That design matters because the new query form does not require a separately maintained copy of the metadata. It adapts the established result-producing code to the virtual-table interface. Ordinary SQL processing can then consume those rows. The internal statement remains a PRAGMA, which explains both the correspondence between the interfaces and the implementation boundary.

For example, a small schema can be inspected through the new function form.

CREATE TABLE contacts(
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT
);

SELECT name, type
FROM pragma_table_info('contacts')
WHERE name = 'email';

The function argument is the name of the table as a string value. It does not require substituting an identifier into a command. The important distinction is that the outer statement is now a SELECT. Projection and filtering apply to the metadata rows through the usual query machinery.

Arguments become hidden columns

The source declares visible columns using the PRAGMA result-column definitions. It then adds hidden columns where the command takes an argument or accepts a schema. This is how the virtual-table mechanism represents function arguments. Those values configure the scan without becoming ordinary output columns in a SELECT * over the metadata.

The planning callback searches for usable equality constraints on the hidden parameters. It assigns a very high estimated cost and row count when the first required parameter is unconstrained. Once the argument is available, the cursor can construct the underlying PRAGMA. This gives the planner information about when the table-valued operation can sensibly run in relation to the rest of a query.

That dependency is relevant when composing metadata queries. A function whose argument comes from another row cannot produce the intended metadata before that row supplies its value. The implementation makes the argument relationship visible to the virtual-table planner interface. It does not claim that arbitrary filters on visible result columns become special metadata indexes.

The statement construction also quotes argument and schema values through SQLite formatting. The source uses %Q for those strings. An application passing a table name as a function argument therefore need not build a separate PRAGMA command by concatenating SQL text. The wrapper still constructs SQL internally, but the quoting belongs to a specific implementation with a defined parameter path.

Experimental has a practical meaning

The release notes call PRAGMA function support experimental. That qualification belongs in any account of the release, especially for schema tools that might otherwise treat the interface as settled. The implementation also makes eligibility explicit. Registration requires a recognized PRAGMA with result flags, and the module has no update method for writing rows through the virtual table.

A table-valued interface therefore does not imply that every configuration command becomes an ordinary writable SQL table. The new machinery exposes results through a cursor. Its usefulness comes from composing those results with SQL operations, while the underlying PRAGMA remains responsible for producing them.

I find that scope appropriate. Metadata queries gain expressiveness without introducing a second schema-description format for applications to parse. A tool can request only the columns it needs and apply a predicate in the same statement. That is a concrete reduction in application-side result processing, even when the engine must still generate the underlying PRAGMA rows.

CPU work and memory allocation also change

The same release reports nine percent fewer CPU cycles in its measured workload. That is a claim about the project measurement, not a guarantee that every application query runs nine percent faster. The release also changes the default lookaside configuration from 500 slots of 128 bytes to 125 slots of 512 bytes. The stated slot counts and sizes show a redistribution toward larger individual allocations, rather than a simple increase in the configured byte total.

Those defaults illustrate a different kind of library improvement. Applications benefit through existing calls when their allocation patterns match the new layout. They do not need to rewrite queries to use a new SQL construct. The PRAGMA function addition is explicit and visible in application text, whereas the CPU and allocation changes affect work underneath established interfaces.

Other changes address bounded pieces of query execution and maintenance. Partial indexes with a WHERE x NOT NULL condition become usable when the column participates in LIKE or GLOB. Matching patterns with multiple wildcards becomes faster, according to the release notes. sqlite3_interrupt() can now interrupt an in-progress checkpoint, and a new database configuration option controls checkpoint behaviour on close.

These changes should retain their separate meanings. A planner improvement changes which existing index can be used. An interrupt improvement changes the reach of a cancellation request. The metadata addition changes what an SQL statement can express. For tools that inspect SQLite schemas, the last of these is the substantial interface change in 3.16.0. The released source shows that the engine implements it by preparing and stepping the existing PRAGMA commands behind a read-only virtual-table cursor.