Faking pg_catalog

In part one I claimed that a meaningful fraction of “implement the Postgres wire protocol” is actually “implement enough of the catalog to survive introspection.” This post is that fraction, expanded. I maintain pg_catalog_rs, a PostgreSQL system-catalog compatibility layer for Apache DataFusion, which exists because I learned everything below the hard way.

The premise sounds absurd until you watch the wire: clients barely trust your SQL. What they trust is the catalog. Connect DBeaver to anything claiming to be Postgres and before you type a single query it has already fired a volley of metadata queries at pg_catalog, and if those fail, the tool doesn’t show a degraded schema browser. It shows an error dialog, and your “compatible” database has failed the interview in the first second.

The catalog is the real API

Postgres exposes its own internals as queryable tables in a schema called pg_catalog: every table is a row in pg_class, every column a row in pg_attribute, every type in pg_type, every function in pg_proc, every schema in pg_namespace. This is self-description as a first-class feature, and over thirty years the entire ecosystem has built on it.

There is a standards-blessed alternative, information_schema, and almost nothing uses it. psql’s \d goes straight to pg_catalog. So do JDBC’s DatabaseMetaData, DBeaver, DataGrip, pgAdmin, every BI connector, and every ORM’s introspection step. When psql describes a table, it sends something in the family of:

SELECT c.relname, n.nspname, c.relkind
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','v','m','S','f','')
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1;

Note what’s load-bearing in there: a join across two catalog tables on oid, a filter over single-character relkind codes, and a call to a catalog function. You don’t get to serve this with a hardcoded list of table names. You need tables, joins, OIDs, and functions, which is to say: you need to actually implement the thing.

The five tables that carry the load

The full catalog is over sixty tables. In practice five of them answer almost everything clients ask, and they’re the core of what pg_catalog_rs emulates:

1. pg_class: every relation (table, view, index, sequence), keyed by OID, tagged with a relkind code and a relnamespace.
2. pg_namespace: schemas. Tiny, but nearly every query joins through it.
3. pg_attribute: columns, with attnum ordering, type OIDs, and the surprisingly important attisdropped flag.
4. pg_type: the type system, where the OIDs must be the real ones.
5. pg_proc: functions, which GUI tools enumerate enthusiastically.

That last point about pg_type deserves its own paragraph, because it’s the trap I’d warn past-me about. Type OIDs are not an implementation detail; they’re a public constant set that drivers hardcode. int4 is 23. text is 25. bool is 16. Your RowDescription messages from part one reference these same OIDs, so the wire protocol and the catalog must tell one consistent story. Mint your own creative OIDs for base types and drivers will either misparse values or refuse them. You are not designing a type system; you are impersonating one, and impersonation is graded on fidelity.

The same goes for the shape of relationships. Columns join to tables by OID, and clients assume attnum behaves like Postgres: ordinal, gap-tolerant, with dropped columns leaving tombstones. The catalog is a web of foreign keys by convention, and clients navigate it with confident, unhedged joins.

Functions are half the battle

Tables alone don’t survive contact with psql, because catalog queries are shot through with function calls: current_database(), current_schema(), pg_table_is_visible(oid), format_type(oid, typmod), pg_get_constraintdef(oid), has_schema_privilege(...), version(). GUI tools lean on these even harder than psql does.

Some are trivial constants. Some, like format_type, encode real logic that must round-trip with your type table. And version() is pure theatre with real consequences: clients parse the string with regexes and gate features on what they find, so you return a plausible Postgres version string and keep it consistent with the server_version parameter you announced at startup. This is the part of the job that feels least like engineering and most like method acting.

Build the shim from captured traffic, not from docs

Here’s the methodology that changed the project for me. Early on I did what everyone does: read the psql source, read driver source, guess at what matters. It’s slow and you still get ambushed, because no document lists what clients actually send.

So I stopped guessing. Run a real PostgreSQL with log_statement = 'all', point every client you care about at it (psql, JDBC, DBeaver, BI tools, a couple of ORMs), exercise them properly, then extract every catalog query from the logs into a corpus. That corpus is the true spec. pg_catalog_rs is built around exactly this loop: a schema description of the catalog, a battery of real captured queries to run against the shim, and an explicit file of not-yet-supported queries that doubles as the roadmap.

Two things fall out of this approach. First, coverage becomes measurable: you’re not vaguely “mostly compatible,” you pass some known number of the captured queries, and every failure has a name. Second, regressions become impossible to miss, because the corpus is a test suite. When a new client version starts sending a new introspection query, you capture it, it fails, and it joins the roadmap. The unsupported list shrinking over time is the project’s honest progress bar.

Keep the lie consistent

A catalog shim has one cardinal rule: it must reflect reality, live. If a table is created mid-session, pg_class must know. This pushes you away from static, hand-maintained catalog data toward computed tables: views over your engine’s actual metadata, projected into Postgres’s shapes at query time. The catalog is a lie about your internals, but it must be a fresh lie, never a stale one.

And for the long tail of tables you’ll never meaningfully implement (statistics views, vacuum bookkeeping, replication state), the right answer is usually an empty result with the correct schema. Clients probe these, ignore empty results gracefully, and crash on missing relations. An empty table says “nothing to report.” A missing table says “I am not really Postgres,” and clients believe it.

That asymmetry is the whole game. Every query you answer correctly is invisible; the one you answer wrongly is an error dialog with your product’s name on it. The catalog is where a compatible database earns the accent it claimed on port 5432, one boring, faithfully impersonated system table at a time.

Part three is up: Prepared statements vs connection pools, on the production failure everyone meets eventually.