Re: pg_comments

Robert,

I noticed a problem at the definition of the view.

:
+UNION ALL
+SELECT
+ d.objoid, d.classoid, d.objsubid,
+ 'large object'::text AS objtype,
+ NULL::oid AS objnamespace,
+ d.objoid::text AS objname,
+ d.description
+FROM
+ pg_description d
+ JOIN pg_largeobject_metadata lom ON d.objoid = lom.oid
+WHERE
+ d.classoid = (SELECT oid FROM pg_class WHERE relname = 'pg_largeobject')
+ AND d.objsubid = 0
+UNION ALL
:

If and when user create a table named 'pg_largeobject' on anywhere except
for the 'pg_catalog' schema, the (SELECT oid FROM pg_class WHERE relname =
'pg_largeobject') may not return 2613.

It seems to me the query should be fixed up as follows:

:
WHERE
d.classoid = (SELECT oid FROM pg_class WHERE relname = 'pg_largeobject'
AND relnamespace = (SELECT oid FROM pg_namespace
WHERE nspname = 'pg_catalog'))
:

Thanks,

(2010/09/20 13:53), Robert Haas wrote:
> 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
>
>
>
>

--
KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>