PgComment makes PostgreSQL schema comments easier to maintain

Database object names rarely explain every rule attached to the data. A column name can identify an amount without saying which currency it uses, or identify a timestamp without explaining the event it records. PostgreSQL can store that explanation with the object, but entering it one command at a time makes a large annotation task tedious. Yohz Software's April 7 PgComment announcement addresses that editing process with a free Windows application.

PgComment loads a schema into a hierarchical tree together with existing comments. Users edit those comments in the tree, and the application caches the edits locally before sending them to the server in a batch when applied. The useful change is the interaction between editing and database requests. Every correction to a sentence no longer requires waiting for an individual COMMENT ON command to finish.

I like database documentation that remains attached to the objects it explains. PostgreSQL is my default for relational work, and a schema is easier to understand when its names come with precise descriptions. An editor can make that documentation less costly to maintain without introducing another storage format for the comments themselves.

The database already has a comment model

The PostgreSQL 9.6 COMMENT reference defines comments as strings attached to database objects. Each object has one comment string. Issuing another COMMENT command for that object replaces the string, while supplying NULL removes it. Dropping the object also drops its comment automatically.

Those semantics explain what an editor is changing. A comment is not an append-only discussion or a collection of separately attributed notes. It is the current description for a particular object. Editing it means choosing the text that subsequent readers should see, with the old description replaced when the command succeeds.

For a table and column that already exist, ordinary SQL can express the same kind of annotation:

COMMENT ON TABLE public.invoice
IS 'Invoices issued to customers.';

COMMENT ON COLUMN public.invoice.issued_at
IS 'Time at which the invoice was issued.';

These examples add descriptions, not enforcement. A sentence describing the meaning of a timestamp does not constrain the values an application may store there. The comment helps a reader interpret the schema, while an actual data restriction needs the corresponding database definition. Keeping that distinction explicit prevents documentation from being mistaken for a validated rule.

PgComment's announced coverage includes tables, columns, indexes, views, domains, sequences, and functions. That range matters because a table-level description alone cannot explain every object involved in an application. A function can need a description of its meaning, and an index can need an explanation of why it exists independently of the table it indexes.

Local editing changes when work reaches the server

The application's local cache separates writing a description from applying it. This suits a task in which the author moves among related objects, revises terminology, and only then sends the edits. The announcement explicitly contrasts batch application with waiting after every individual comment command.

That is a statement about request timing. It does not establish that the batch has any particular transactional behavior, nor does it describe how concurrent edits by separate users are resolved. Neither guarantee appears in the announcement. The documented benefit is that the user can continue editing locally before applying the accumulated changes.

A tree view also makes the relationship between an object and its description visible during editing. Existing comments appear with the schema, so the author can inspect neighboring descriptions without separately issuing retrieval queries. The tool supports a task that otherwise involves both navigating database objects and writing SQL commands.

I consider that a reasonable use for a graphical utility. Writing COMMENT ON is straightforward, but the syntax is only a small part of documenting a schema consistently. Keeping related objects visible can help the author choose terminology that means the same thing across several descriptions.

Documentation authors still need database authority

Yohz suggests using PgComment with its DB Doc schema documentation generator to let the people most familiar with database objects document them. That describes a useful division of subject knowledge. It does not change PostgreSQL's authorization rules for setting comments.

For most object types, PostgreSQL requires the object's owner to set the comment. Superusers can comment on any object, with separate documented rules for objects such as roles that do not have an ordinary owner. Installing an editor therefore does not by itself grant a domain expert permission to modify every description in a database.

The publication of comments has its own access boundary. The 9.6 reference says there is no security mechanism for viewing comments. Any user connected to a database can see its object comments. Comments on shared objects, including databases and roles, are visible across the cluster's databases. Descriptions must consequently exclude credentials and other security-critical information.

That visibility is compatible with documentation meant to help database users understand a schema. It is incompatible with treating the comment field as a private administrative notebook. A tool that makes annotations convenient still writes into the database's existing visibility model.

Other readers can use the same descriptions

PostgreSQL exposes comments through the psql family of \d commands. The reference also identifies obj_description, col_description, and shobj_description as the built-in functions on which other comment interfaces can be built. PgComment therefore edits information that can be useful outside its own interface.

The important persistence boundary remains the database object. After applying a description, another interface can retrieve the stored comment without needing the editor's local cache. Removing the object removes its annotation as well. PgComment's contribution is a more convenient authoring workflow around those existing PostgreSQL facilities, with batch application reducing the interruption between writing successive descriptions.