On 2026-08-08 Sa 9:17 AM, Bruce Momjian wrote:
I think SQL object comments are very useful for users, and even more
useful now that many people are using MCP:
https://momjian.us/main/blogs/pgblog/2026.html#March_16_2026
Would someone please volunteer to check all externally-developed
extensions to see if they have the appropriate object comments, and if
not, either create a patch to add them or contact the author suggesting
they add them. I have done this already for pgvector:
https://momjian.us/main/blogs/pgblog/2026.html#March_16_2026
I think we need to be more specific about what might help here. I asked
Claude what sort of objects commenting on in this way would help. Here's
its answer:
*Always worth it: *|TABLE|, |FOREIGN TABLE|, partitioned tables,
|COLUMN|, |VIEW|, |MATERIALIZED VIEW| Why: Read by essentially every
server; named directly in every query
*Worth it if your server reads them*|: SCHEMA|, |FUNCTION|/|PROCEDURE|,
|TYPE|, |DOMAIN|, |SERVER| Why: Read by better servers, and the model
can name all of them
*Usually not worth it: *|INDEX|, |CONSTRAINT|, |TRIGGER|, |POLICY|,
|SEQUENCE|, |OPERATOR|, |RULE|, |ROLE|, |DATABASE|, |TABLESPACE| Why:
Rarely fetched, and mostly not nameable in generated SQL
So, if your extension creates a Foreign Data Wrapper, as several of mine
do, there's really nothing for you to do. By all means comment on it,
but it is very unlikely to help an MCP server. On the other hand, if it
creates functions and types, commenting on those is probably a good
idea, and if it creates tables it's a very good idea.
I also asked claude for a template. It gave me one with a worked
example. It's attached.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
--------------------------------------------------------------------------
-- COMMENT ON template for MCP / LLM-readable schemas
--
-- Guiding rule: comment what the schema cannot say for itself.
--
-- An MCP client already sees names, data types, nullability, defaults,
-- foreign keys, and CHECK constraints. Restating those wastes context and
-- teaches the model nothing. Comment the things that are invisible from
-- the catalog: grain, units, encodings, NULL semantics, which of several
-- similar objects to use, and which columns are dead.
--
-- Format: first line is a plain sentence (this is what shows, truncated,
-- in \d+ and in most MCP schema dumps). Optional tagged lines follow,
-- drawn from this fixed vocabulary so they stay greppable:
--
-- Grain: what exactly one row represents
-- Scope: what is included / deliberately excluded
-- Units: unit and scale of a numeric value
-- Values: meaning of opaque codes not enforced by a type or CHECK
-- NULL: what NULL means here (absent? unknown? not-applicable?)
-- Join: relationships the FKs don't declare
-- Use: when to prefer this object over a similar one
-- Caveat: traps that produce wrong-but-plausible queries
-- Deprecated: do not use, and what to use instead
--
-- Budget: an MCP client may inject every comment in the schema into the
-- context window at once. Keep table comments under ~5 lines and column
-- comments to 1-2. Terse and true beats complete.
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-- 1. WORKED EXAMPLE
--------------------------------------------------------------------------
-- TABLE: lead with grain. Ambiguous grain is the single largest cause of
-- silently wrong aggregates in generated SQL.
COMMENT ON TABLE shipment_leg IS
$$A single point-to-point movement within a shipment; multi-stop shipments have
several.
Grain: one row per (shipment_id, leg_seq).
Scope: completed legs only; legs still in transit are in shipment_leg_active.
Volume: ~40M rows, ~1M/month. Unfiltered scans are slow; filter on departed_at.
Join: shipment_id -> shipment.id, carrier_id -> carrier.id.$$;
-- Disambiguation between near-identical tables. Worth more than any other
-- single comment when the schema has grown organically.
COMMENT ON TABLE shipment_leg_active IS
$$Legs currently in transit. Rows move to shipment_leg on arrival.
Use: for "where is X now" questions. For historical or volume analysis use
shipment_leg.
Caveat: a leg exists in exactly one of the two tables, never both. Answering
"all legs ever" requires a UNION ALL of the two.$$;
-- COLUMN: units and scale. A model that assumes dollars will be off by 100x
-- and the result will look entirely reasonable.
COMMENT ON COLUMN shipment_leg.freight_charge IS
$$Amount billed to the shipper, in USD cents. Divide by 100 to report
dollars.$$;
COMMENT ON COLUMN shipment_leg.distance IS
$$Road distance actually driven, in kilometres (not the great-circle
distance).$$;
-- COLUMN: which timestamp means what. Schemas routinely carry three or four
-- and the names rarely distinguish event time from record time.
COMMENT ON COLUMN shipment_leg.departed_at IS
$$When the vehicle actually left, in UTC. This is the event time; use it for
time-series and date-range questions.$$;
COMMENT ON COLUMN shipment_leg.created_at IS
$$When our system recorded the row, in UTC. Lags departed_at by minutes to days
after a connectivity outage. Do not use for date-range questions.$$;
-- COLUMN: opaque codes with no enum type or CHECK to reveal them.
COMMENT ON COLUMN shipment_leg.status IS
$$Values: D=delivered, R=refused at destination, X=cancelled before departure,
P=partially delivered. Only D and P represent revenue.$$;
-- COLUMN: NULL semantics. "Unknown" and "not applicable" and "none yet" all
-- look identical in the catalog and lead to different correct queries.
COMMENT ON COLUMN shipment_leg.pod_signed_at IS
$$When proof of delivery was signed, in UTC.
NULL: no signature was collected, which is normal for contactless drops. It does
not mean the leg is undelivered; check status for that.$$;
-- COLUMN: the natural key humans actually use, versus the surrogate PK.
COMMENT ON COLUMN shipment.reference IS
$$Customer-facing shipment reference, e.g. 'SHP-2026-0041823'. This is what
users
mean when they quote "the shipment number"; shipment.id is internal only.
Caveat: unique per customer, not globally. Filter with customer_id.$$;
-- COLUMN: free text. Tells the model not to GROUP BY it or trust exact
matches.
COMMENT ON COLUMN shipment_leg.delay_reason IS
$$Free text typed by the driver, unvalidated and inconsistently capitalised.
Caveat: not a controlled vocabulary. Use ILIKE for matching; do not GROUP BY.$$;
-- COLUMN: deprecation. A single line here removes a whole class of wrong
query.
COMMENT ON COLUMN shipment_leg.zone_code IS
$$Deprecated: frozen since 2024-03 and no longer maintained. Use
destination_region_id instead.$$;
-- VIEW: say when to prefer it, or the model will keep rebuilding it by hand.
COMMENT ON VIEW shipment_daily_revenue IS
$$Revenue per customer per calendar day, already excluding cancelled and
refused legs.
Use: for any revenue-over-time question. Prefer this over aggregating
shipment_leg
directly, which requires the status filter to be correct.
Caveat: bucketed in UTC, not customer-local time.$$;
-- FUNCTION: signature is visible; behaviour and cost are not.
COMMENT ON FUNCTION current_transit_estimate(bigint) IS
$$Estimated remaining transit time for an in-flight shipment.
Caveat: calls an external routing service; slow and unsuitable for use in a
query over many rows. Call for a single shipment at a time.$$;
--------------------------------------------------------------------------
-- 2. BLANK SKELETON
--------------------------------------------------------------------------
COMMENT ON TABLE <table> IS
$$<what one row is, in a plain sentence>
Grain: one row per <key>.
Scope: <what's in, what's deliberately out>.
Join: <relationships the FKs don't declare>.$$;
COMMENT ON COLUMN <table>.<column> IS
$$<what it holds, and its unit or encoding if not obvious from the type>$$;
--------------------------------------------------------------------------
-- 3. ANTI-PATTERNS
--------------------------------------------------------------------------
--
-- 'The customer id' on customer_id -- restates the name
-- 'Foreign key to customer' on customer_id -- already in the catalog
-- 'varchar(50), not null' on anything -- already in the catalog
-- 'Added by Dave, ticket 4471, see the design doc on Confluence'
-- -- history, not semantics;
-- belongs in the migration
-- A three-paragraph narrative -- burns context, buries
-- the one useful sentence
--
-- Comments are dumped by pg_dump and restored with the schema, so they belong
-- in version-controlled migrations alongside the DDL, not applied by hand to
-- production. Applying COMMENT ON requires ownership of the object.
--------------------------------------------------------------------------
-- 4. COVERAGE: find what is still undocumented
--------------------------------------------------------------------------
-- Tables and views with no comment
SELECT c.relnamespace::regnamespace AS schema, c.relname, c.relkind
FROM pg_class c
WHERE c.relkind IN ('r','p','v','m','f')
AND c.relnamespace::regnamespace::text NOT IN
('pg_catalog','information_schema')
AND obj_description(c.oid, 'pg_class') IS NULL
ORDER BY 1, 2;
-- Columns with no comment, worst-covered tables first
SELECT c.relnamespace::regnamespace AS schema, c.relname, a.attname
FROM pg_class c
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum > 0 AND NOT
a.attisdropped
WHERE c.relkind IN ('r','p','v','m','f')
AND c.relnamespace::regnamespace::text NOT IN
('pg_catalog','information_schema')
AND col_description(c.oid, a.attnum) IS NULL
ORDER BY 1, 2, a.attnum;
-- Read comments back the way an MCP client sees them
-- \d+ shipment_leg
-- SELECT obj_description('shipment_leg'::regclass, 'pg_class');
