pg_class assigns namespaces by name prefix: hides user tables named pg_*, leaks internal idx_* indexes into \di

[erans]

Split out of #88, where it was scoped out to keep that change off the pg_class path one week after #87 landed there.

Problem

Migration v28 assigns namespaces in the pg_class view with a prefix test (src/migration/registry.rs):

CASE
    WHEN name LIKE 'pg\_%' ESCAPE '\' THEN 11
    WHEN name LIKE 'information\_schema\_%' ESCAPE '\' THEN 13000
    ELSE 2200
END as relnamespace

This is wrong in both directions.

Over-matches: user tables named pg_* are hidden

A user who creates pg_myreport gets it filed under pg_catalog. \dt and every ORM that filters on nspname skip it. The table is invisible with no error.

This is the failure mode #88 called out when rejecting a blanket NOT LIKE 'pg_%' filter — the same objection applies to using the prefix for namespace assignment.

Under-matches: internal indexes leak into \di

Migrations create eight indexes named idx_*, which neither the __pgsqlite_% nor the pg_% test catches:

$ psql -c "\di"
                          List of indexes
 Schema |             Name              | Type  |  Owner   | Table
--------+-------------------------------+-------+----------+-------
 public | idx_array_types_table         | index | postgres |
 public | idx_comments_lookup           | index | postgres |
 public | idx_datetime_cache_table      | index | postgres |
 public | idx_enum_values_label         | index | postgres |
 public | idx_enum_values_type          | index | postgres |
 public | idx_fts_metadata_table        | index | postgres |
 public | idx_numeric_constraints_table | index | postgres |
 public | idx_string_constraints_table  | index | postgres |
(8 rows)

All eight are CREATE INDEX IF NOT EXISTS statements in src/migration/registry.rs. They are indexes on __pgsqlite_* tables but are not themselves named __pgsqlite_*. They pollute \di and ORM index reflection.

Fix

#88 introduces the machinery this needs: an exact-name internal-relation registry (src/catalog/internal_relations.rs) covering all 4 internal tables, 24 views, and 8 indexes, exposed to SQL as __pgsqlite_relnamespace(name) -> 11 | 13000 | 2200, plus a drift test asserting the list matches a migrated-to-head database.

So this reduces to one expression swap in a new migration:

__pgsqlite_relnamespace(name) as relnamespace

By the time this lands, the UDF will already be serving information_schema.tables and .columns in production.

Verification

• A user table named pg_myreport appears in \dt under public. information_schema.tables lists pgsqlite's materialized pg_* and information_schema_* relations #88 ships a test asserting the current (misfiled) behavior to document the divergence — that assertion flips here.
• None of the eight idx_* indexes appear in \di.
• information_schema.tables and pg_class agree on table_schema / nspname for every relation, closing the divergence information_schema.tables lists pgsqlite's materialized pg_* and information_schema_* relations #88 leaves open.
• \dt, \di, \d <table> and the SQLAlchemy suite stay green — this touches the view every ORM introspection query reads.

Context

• \dt reports "Did not find any tables" on a database that has tables #87 — moved pg_class from a Rust handler to SQLite, added the v28 namespacing
• information_schema.tables lists pgsqlite's materialized pg_* and information_schema_* relations #88 — namespace-aware information_schema, introduces the registry and UDF
• Design: docs/superpowers/specs/2026-08-11-information-schema-namespace-design.md

[erans]

Scope addition from planning #88: pg_attribute.atttypid belongs here too.

pg_attribute derives atttypid from the SQLite declared type (src/migration/registry.rs:2604), not from __pgsqlite_schema. Measured:

column	declared	\d / pg_attribute reports	correct
amount	NUMERIC(10,2)	text	numeric
uid	UUID	text	uuid
doc	JSONB	text	jsonb
ts	TIMESTAMPTZ	integer	timestamp with time zone
flag	BOOLEAN	integer	boolean
nick	VARCHAR(50)	text	character varying

ts → integer leaks the INTEGER datetime storage into a client-visible surface.

Fix is a LEFT JOIN __pgsqlite_schema s plus:

COALESCE(__pgsqlite_pg_type_oid(s.pg_type), <existing CASE>) as atttypid

SchemaTypeMapper::pg_type_string_to_oid (src/types/schema_type_mapper.rs:112) already does the mapping; it needs a thin UDF wrapper, the same shape as the __pgsqlite_relnamespace wrapper #88 adds.

It lands here rather than in #88 for the same reason pg_class did — pg_attribute feeds \d and every ORM column reflection query. #88's information_schema_columns reads sqlite_master / pragma_table_info / __pgsqlite_schema directly instead of layering on pg_attribute, so #88 is fully fixed without touching it.

Both changes here share one migration and one round of ORM regression testing.