pg_comments

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: pg_comments
Date: 2010-09-20 04:53:39
Message-ID: AANLkTikkMvjzJSLv9cVw4-SoBMrJpii+XiqQRdc60trG@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

The psql \dd command has a couple of infelicities.

1. It doesn't actually list comments on all of the object types to
which they can be applied using the COMMENT command.
2. It also doesn't list comments on access methods, which have
comments but are not supported by the COMMENT command.
3. It doesn't even list comments on all of the object types which the
psql documentation claims it does.
4. It chooses to print out both the "name" and "object" columns in a
format which is not 100% compatible with the COMMENT command, so that
you can't necessarily use the output of \dd to construct valid input
to COMMENT.
5. The SQL query used to generate the output it does produce is 75
lines long, meaning that it's really entertaining if you need, for
some reason, to edit that query.

In view of the foregoing problems, I'd like to propose adding a new
system view, tentatively called pg_comments, which lists all of the
comments for everything in the system in such a way that it's
reasonably possible to do further filtering out the output in ways
that you might care about; and which also gives objects the names and
types in a format that matches what the COMMENT command will accept as
input. Patch attached. I haven't yet written the documentation for
the view or adjusted src/bin/psql/describe.c to do anything useful
with it, just so that I won't waste any more time on this if it gets
shot down. But for the record, it took me something like three hours
to write and test this view, which I think is an excellent argument
for why we need it.

Supposing no major objections, there are a few things to think about
if we wish to have psql use this:

A. The obvious thing to do seems to be to retain the existing code for
server versions < 9.1 and to use pg_comments for >= 9.1. I would be
inclined not to bother fixing the code for pre-9.1 servers to display
comments on everything (a 9.1 psql against a 9.0 or prior server will
be no worse than a 9.0 psql against the same server; it just won't be
any better).

B. The existing code localizes the contents of the "object" column.
This is arguably a misfeature if you are about (4), but if we want to
keep the existing behavior I'm not quite sure what the best way to do
that is.

C. It's not so obvious which comments should be displayed with \dd vs.
\ddS. In particular, comments on toast schemas have the same problem
recently discussed with \dn, and there is a similar issue with
tablespaces. Generally, it's not obvious what to do for objects that
don't live in schemas - access methods, for example, are arguably
always system objects. But... that's arguable.

D. Fixing (4) with respect to object names implies listing argument
types for functions and operators, which makes the display output
quite wide when using \ddS. I am inclined to say that's just the cost
of making the output accurate.

There may be other issues I haven't noticed yet, too.

Incidentally, if you're wondering what prompted this patch, I was
reviewing KaiGai Kohei's patch to add security label support and
noticed its complete lack of psql support. I'm actually not really
sure that there's any compelling reason to provide psql support,
considering that we've gotten to the point where any backslash command
is almost bound to be something not terribly mnemonic, and because
there are likely to be either no security labels at all or so many
that a command that just dumps them ALL out in bulk is all but
useless. But we at least need to provide a suitable system view,
because the catalog structure used by these catalogs that can handle
SQL objects of any type is pretty obnoxious for user querying (though,
of course, it's pretty neat as an internal format).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

Attachment Content-Type Size
pg_comments.patch application/octet-stream 52.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2010-09-20 05:07:16 Re: pg_comments
Previous Message Joseph Adams 2010-09-20 04:38:21 Re: Basic JSON support