pg_comments
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
Attachments:
pg_comments.patchapplication/octet-stream; name=pg_comments.patchDownload
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 225685e..7d00152 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -12553,6 +12553,12 @@ SELECT relname FROM pg_class WHERE pg_table_is_visible(oid);
<entry>is operator class visible in search path</entry>
</row>
<row>
+ <entry><literal><function>pg_opfamily_is_visible(<parameter>opclass_oid</parameter>)</function></literal>
+ </entry>
+ <entry><type>boolean</type></entry>
+ <entry>is operator family visible in search path</entry>
+ </row>
+ <row>
<entry><literal><function>pg_table_is_visible(<parameter>table_oid</parameter>)</function></literal>
</entry>
<entry><type>boolean</type></entry>
@@ -12605,6 +12611,9 @@ SELECT relname FROM pg_class WHERE pg_table_is_visible(oid);
<primary>pg_opclass_is_visible</primary>
</indexterm>
<indexterm>
+ <primary>pg_opfamily_is_visible</primary>
+ </indexterm>
+ <indexterm>
<primary>pg_table_is_visible</primary>
</indexterm>
<indexterm>
diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c
index 08e5692..886f96a 100644
--- a/src/backend/catalog/namespace.c
+++ b/src/backend/catalog/namespace.c
@@ -198,6 +198,7 @@ Datum pg_type_is_visible(PG_FUNCTION_ARGS);
Datum pg_function_is_visible(PG_FUNCTION_ARGS);
Datum pg_operator_is_visible(PG_FUNCTION_ARGS);
Datum pg_opclass_is_visible(PG_FUNCTION_ARGS);
+Datum pg_opfamily_is_visible(PG_FUNCTION_ARGS);
Datum pg_conversion_is_visible(PG_FUNCTION_ARGS);
Datum pg_ts_parser_is_visible(PG_FUNCTION_ARGS);
Datum pg_ts_dict_is_visible(PG_FUNCTION_ARGS);
@@ -3533,6 +3534,17 @@ pg_opclass_is_visible(PG_FUNCTION_ARGS)
}
Datum
+pg_opfamily_is_visible(PG_FUNCTION_ARGS)
+{
+ Oid oid = PG_GETARG_OID(0);
+
+ if (!SearchSysCacheExists1(OPFAMILYOID, ObjectIdGetDatum(oid)))
+ PG_RETURN_NULL();
+
+ PG_RETURN_BOOL(OpfamilyIsVisible(oid));
+}
+
+Datum
pg_conversion_is_visible(PG_FUNCTION_ARGS)
{
Oid oid = PG_GETARG_OID(0);
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 9fbe70d..46de4da 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -6,6 +6,365 @@
* $PostgreSQL$
*/
+CREATE VIEW pg_comments AS
+SELECT
+ d.objoid, d.classoid, d.objsubid,
+ CASE WHEN rel.relkind = 'r' THEN 'table'::text
+ WHEN rel.relkind = 'v' THEN 'view'::text
+ WHEN rel.relkind = 'i' THEN 'index'::text
+ WHEN rel.relkind = 'S' THEN 'sequence'::text END AS objtype,
+ rel.relnamespace AS objnamespace,
+ CASE WHEN pg_table_is_visible(rel.oid)
+ THEN quote_ident(rel.relname)
+ ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname)
+ END AS objname,
+ d.description
+FROM
+ pg_description d
+ JOIN pg_class rel ON d.classoid = rel.tableoid AND d.objoid = rel.oid
+ JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid
+WHERE
+ d.objsubid = 0
+UNION ALL
+SELECT
+ d.objoid, d.classoid, d.objsubid,
+ 'column'::text AS objtype,
+ rel.relnamespace AS objnamespace,
+ CASE WHEN pg_table_is_visible(rel.oid)
+ THEN quote_ident(rel.relname)
+ ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname)
+ END || '.' || att.attname AS objname,
+ d.description
+FROM
+ pg_description d
+ JOIN pg_class rel ON d.classoid = rel.tableoid AND d.objoid = rel.oid
+ JOIN pg_attribute att
+ ON rel.oid = att.attrelid AND d.objsubid = att.attnum
+ JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid
+WHERE
+ d.objsubid != 0
+UNION ALL
+SELECT
+ d.objoid, d.classoid, d.objsubid,
+ CASE WHEN pro.proisagg = true THEN 'aggregate'::text
+ WHEN pro.proisagg = false THEN 'function'::text
+ END AS objtype,
+ pro.pronamespace AS objnamespace,
+ CASE WHEN pg_function_is_visible(pro.oid)
+ THEN quote_ident(pro.proname)
+ ELSE quote_ident(nsp.nspname) || '.' || quote_ident(pro.proname)
+ END || '('
+ || pg_catalog.pg_get_function_arguments(pro.oid) || ')' AS objname,
+ d.description
+FROM
+ pg_description d
+ JOIN pg_proc pro ON d.classoid = pro.tableoid AND d.objoid = pro.oid
+ JOIN pg_namespace nsp ON pro.pronamespace = nsp.oid
+WHERE
+ d.objsubid = 0
+UNION ALL
+SELECT
+ d.objoid, d.classoid, d.objsubid,
+ 'cast'::text AS objtype,
+ NULL::oid AS objnamespace,
+ '(' || format_type(cst.castsource, NULL)
+ || ' AS ' || format_type(cst.casttarget, NULL) || ')' AS objname,
+ d.description
+FROM
+ pg_description d
+ JOIN pg_cast cst ON d.classoid = cst.tableoid AND d.objoid = cst.oid
+WHERE
+ d.objsubid = 0
+UNION ALL
+SELECT
+ d.objoid, d.classoid, d.objsubid,
+ 'constraint'::text AS objtype,
+ rel.relnamespace AS objnamespace,
+ con.conname || ' ON ' ||
+ CASE WHEN pg_table_is_visible(rel.oid)
+ THEN quote_ident(rel.relname)
+ ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname)
+ END AS objname,
+ d.description
+FROM
+ pg_description d
+ JOIN pg_constraint con ON d.classoid = con.tableoid AND d.objoid = con.oid
+ JOIN pg_class rel ON con.conrelid = rel.oid
+ JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid
+WHERE
+ d.objsubid = 0
+UNION ALL
+SELECT
+ d.objoid, d.classoid, d.objsubid,
+ 'conversion'::text AS objtype,
+ con.connamespace AS objnamespace,
+ CASE WHEN pg_conversion_is_visible(con.oid)
+ THEN quote_ident(con.conname)
+ ELSE quote_ident(nsp.nspname) || '.' || quote_ident(con.conname)
+ END AS objname,
+ d.description
+FROM
+ pg_description d
+ JOIN pg_conversion con ON d.classoid = con.tableoid AND d.objoid = con.oid
+ JOIN pg_namespace nsp ON con.connamespace = nsp.oid
+WHERE
+ d.objsubid = 0
+UNION ALL
+SELECT
+ d.objoid, d.classoid, d.objsubid,
+ CASE WHEN typ.typtype = 'd' THEN 'domain'::text
+ ELSE 'type'::text END AS objtype,
+ typ.typnamespace AS objnamespace,
+ CASE WHEN pg_type_is_visible(typ.oid)
+ THEN quote_ident(typ.typname)
+ ELSE quote_ident(nsp.nspname) || '.' || quote_ident(typ.typname)
+ END AS objname,
+ d.description
+FROM
+ pg_description d
+ JOIN pg_type typ ON d.classoid = typ.tableoid AND d.objoid = typ.oid
+ JOIN pg_namespace nsp ON typ.typnamespace = nsp.oid
+WHERE
+ d.objsubid = 0
+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
+SELECT
+ d.objoid, d.classoid, d.objsubid,
+ 'operator'::text AS objtype,
+ opr.oprnamespace AS objnamespace,
+ CASE WHEN pg_operator_is_visible(opr.oid)
+ THEN opr.oprname
+ ELSE quote_ident(nsp.nspname) || '.' || opr.oprname
+ END
+ || '('
+ || CASE WHEN opr.oprkind = 'r' THEN 'NONE'
+ ELSE format_type(opr.oprleft, NULL) END
+ || ','
+ || CASE WHEN opr.oprkind = 'l' THEN 'NONE'
+ ELSE format_type(opr.oprright, NULL) END
+ || ')' AS objname,
+ d.description
+FROM
+ pg_description d
+ JOIN pg_operator opr ON d.classoid = opr.tableoid AND d.objoid = opr.oid
+ JOIN pg_namespace nsp ON opr.oprnamespace = nsp.oid
+WHERE
+ d.objsubid = 0
+UNION ALL
+SELECT
+ d.objoid, d.classoid, d.objsubid,
+ 'operator class'::text AS objtype,
+ opc.opcnamespace AS objnamespace,
+ CASE WHEN pg_opclass_is_visible(opc.oid)
+ THEN quote_ident(opc.opcname)
+ ELSE quote_ident(nsp.nspname) || '.' || quote_ident(opc.opcname)
+ END || ' USING ' || am.amname AS objname,
+ d.description
+FROM
+ pg_description d
+ JOIN pg_opclass opc ON d.classoid = opc.tableoid AND d.objoid = opc.oid
+ JOIN pg_am am ON opc.opcmethod = am.oid
+ JOIN pg_namespace nsp ON opc.opcnamespace = nsp.oid
+WHERE
+ d.objsubid = 0
+UNION ALL
+SELECT
+ d.objoid, d.classoid, d.objsubid,
+ 'operator family'::text AS objtype,
+ opf.opfnamespace AS objnamespace,
+ CASE WHEN pg_opfamily_is_visible(opf.oid)
+ THEN quote_ident(opf.opfname)
+ ELSE quote_ident(nsp.nspname) || '.' || quote_ident(opf.opfname)
+ END || ' USING ' || am.amname AS objname,
+ d.description
+FROM
+ pg_description d
+ JOIN pg_opfamily opf ON d.classoid = opf.tableoid AND d.objoid = opf.oid
+ JOIN pg_am am ON opf.opfmethod = am.oid
+ JOIN pg_namespace nsp ON opf.opfnamespace = nsp.oid
+WHERE
+ d.objsubid = 0
+UNION ALL
+SELECT
+ d.objoid, d.classoid, d.objsubid,
+ 'language'::text AS objtype,
+ NULL::oid AS objnamespace,
+ quote_ident(lan.lanname) AS objname,
+ d.description
+FROM
+ pg_description d
+ JOIN pg_language lan ON d.classoid = lan.tableoid AND d.objoid = lan.oid
+WHERE
+ d.objsubid = 0
+UNION ALL
+SELECT
+ d.objoid, d.classoid, d.objsubid,
+ 'rule'::text AS objtype,
+ rel.relnamespace AS objnamespace,
+ quote_ident(rew.rulename) || ' ON ' ||
+ CASE WHEN pg_table_is_visible(rel.oid)
+ THEN quote_ident(rel.relname)
+ ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname)
+ END AS objname,
+ d.description
+FROM
+ pg_description d
+ JOIN pg_rewrite rew ON d.classoid = rew.tableoid AND d.objoid = rew.oid
+ JOIN pg_class rel ON rew.ev_class = rel.oid
+ JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid
+WHERE
+ d.objsubid = 0
+UNION ALL
+SELECT
+ d.objoid, d.classoid, d.objsubid,
+ 'schema'::text AS objtype,
+ nsp.oid AS objnamespace,
+ quote_ident(nsp.nspname) AS objname,
+ d.description
+FROM
+ pg_description d
+ JOIN pg_namespace nsp ON d.classoid = nsp.tableoid AND d.objoid = nsp.oid
+WHERE
+ d.objsubid = 0
+UNION ALL
+SELECT
+ d.objoid, d.classoid, d.objsubid,
+ 'text search configuration'::text AS objtype,
+ cfg.cfgnamespace AS objnamespace,
+ CASE WHEN pg_ts_config_is_visible(cfg.oid)
+ THEN quote_ident(cfg.cfgname)
+ ELSE quote_ident(nsp.nspname) || '.' || quote_ident(cfg.cfgname)
+ END AS objname,
+ d.description
+FROM
+ pg_description d
+ JOIN pg_ts_config cfg ON d.classoid = cfg.tableoid AND d.objoid = cfg.oid
+ JOIN pg_namespace nsp ON cfg.cfgnamespace = nsp.oid
+WHERE
+ d.objsubid = 0
+UNION ALL
+SELECT
+ d.objoid, d.classoid, d.objsubid,
+ 'text search dictionary'::text AS objtype,
+ dict.dictnamespace AS objnamespace,
+ CASE WHEN pg_ts_dict_is_visible(dict.oid)
+ THEN quote_ident(dict.dictname)
+ ELSE quote_ident(nsp.nspname) || '.' || quote_ident(dict.dictname)
+ END AS objname,
+ d.description
+FROM
+ pg_description d
+ JOIN pg_ts_dict dict ON d.classoid = dict.tableoid AND d.objoid = dict.oid
+ JOIN pg_namespace nsp ON dict.dictnamespace = nsp.oid
+WHERE
+ d.objsubid = 0
+UNION ALL
+SELECT
+ d.objoid, d.classoid, d.objsubid,
+ 'text search parser'::text AS objtype,
+ prs.prsnamespace AS objnamespace,
+ CASE WHEN pg_ts_parser_is_visible(prs.oid)
+ THEN quote_ident(prs.prsname)
+ ELSE quote_ident(nsp.nspname) || '.' || quote_ident(prs.prsname)
+ END AS objname,
+ d.description
+FROM
+ pg_description d
+ JOIN pg_ts_parser prs ON d.classoid = prs.tableoid AND d.objoid = prs.oid
+ JOIN pg_namespace nsp ON prs.prsnamespace = nsp.oid
+WHERE
+ d.objsubid = 0
+UNION ALL
+SELECT
+ d.objoid, d.classoid, d.objsubid,
+ 'text search template'::text AS objtype,
+ tmpl.tmplnamespace AS objnamespace,
+ CASE WHEN pg_ts_template_is_visible(tmpl.oid)
+ THEN quote_ident(tmpl.tmplname)
+ ELSE quote_ident(nsp.nspname) || '.' || quote_ident(tmpl.tmplname)
+ END AS objname,
+ d.description
+FROM
+ pg_description d
+ JOIN pg_ts_template tmpl
+ ON d.classoid = tmpl.tableoid AND d.objoid = tmpl.oid
+ JOIN pg_namespace nsp ON tmpl.tmplnamespace = nsp.oid
+WHERE
+ d.objsubid = 0
+UNION ALL
+SELECT
+ d.objoid, d.classoid, d.objsubid,
+ 'trigger'::text AS objtype,
+ rel.relnamespace AS objnamespace,
+ quote_ident(tg.tgname) || ' ON ' ||
+ CASE WHEN pg_table_is_visible(rel.oid)
+ THEN quote_ident(rel.relname)
+ ELSE quote_ident(nsp.nspname) || '.' || quote_ident(rel.relname)
+ END AS objname,
+ d.description
+FROM
+ pg_description d
+ JOIN pg_trigger tg ON d.classoid = tg.tableoid AND d.objoid = tg.oid
+ JOIN pg_class rel ON tg.tgrelid = rel.oid
+ JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid
+WHERE
+ d.objsubid = 0
+UNION ALL
+SELECT
+ d.objoid, d.classoid, d.objsubid,
+ 'access method'::text AS objtype,
+ NULL::oid AS objnamespace,
+ quote_ident(am.amname) AS objname,
+ d.description
+FROM
+ pg_description d
+ JOIN pg_am am ON d.classoid = am.tableoid AND d.objoid = am.oid
+WHERE
+ d.objsubid = 0
+UNION ALL
+SELECT
+ d.objoid, d.classoid, NULL::integer AS objsubid,
+ 'database'::text AS objtype,
+ NULL::oid AS objnamespace,
+ quote_ident(dat.datname) AS objname,
+ d.description
+FROM
+ pg_shdescription d
+ JOIN pg_database dat ON d.classoid = dat.tableoid AND d.objoid = dat.oid
+UNION ALL
+SELECT
+ d.objoid, d.classoid, NULL::integer AS objsubid,
+ 'role'::text AS objtype,
+ NULL::oid AS objnamespace,
+ quote_ident(rol.rolname) AS objname,
+ d.description
+FROM
+ pg_shdescription d
+ JOIN pg_authid rol ON d.classoid = rol.tableoid AND d.objoid = rol.oid
+UNION ALL
+SELECT
+ d.objoid, d.classoid, NULL::integer AS objsubid,
+ 'tablespace'::text AS objtype,
+ NULL::oid AS objnamespace,
+ quote_ident(spc.spcname) AS objname,
+ d.description
+FROM
+ pg_shdescription d
+ JOIN pg_tablespace spc
+ ON d.classoid = spc.tableoid AND d.objoid = spc.oid;
+
CREATE VIEW pg_roles AS
SELECT
rolname,
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 427f5ea..55c63da 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -3338,6 +3338,8 @@ DATA(insert OID = 2082 ( pg_operator_is_visible PGNSP PGUID 12 1 0 0 f f f t f
DESCR("is operator visible in search path?");
DATA(insert OID = 2083 ( pg_opclass_is_visible PGNSP PGUID 12 1 0 0 f f f t f s 1 0 16 "26" _null_ _null_ _null_ _null_ pg_opclass_is_visible _null_ _null_ _null_ ));
DESCR("is opclass visible in search path?");
+DATA(insert OID = 3534 ( pg_opfamily_is_visible PGNSP PGUID 12 1 0 0 f f f t f s 1 0 16 "26" _null_ _null_ _null_ _null_ pg_opfamily_is_visible _null_ _null_ _null_ ));
+DESCR("is opfamily visible in search path?");
DATA(insert OID = 2093 ( pg_conversion_is_visible PGNSP PGUID 12 1 0 0 f f f t f s 1 0 16 "26" _null_ _null_ _null_ _null_ pg_conversion_is_visible _null_ _null_ _null_ ));
DESCR("is conversion visible in search path?");
DATA(insert OID = 3756 ( pg_ts_parser_is_visible PGNSP PGUID 12 1 0 0 f f f t f s 1 0 16 "26" _null_ _null_ _null_ _null_ pg_ts_parser_is_visible _null_ _null_ _null_ ));
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index ee3bd3b..8f8b4f2 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1276,9 +1276,10 @@ drop table cchild;
-- Check that ruleutils are working
--
SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schema' ORDER BY viewname;
- viewname | definition
------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ viewname | definition
+-----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
iexit | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath);
+ pg_comments | (((((((((((((((((((((SELECT d.objoid, d.classoid, d.objsubid, CASE WHEN (rel.relkind = 'r'::"char") THEN 'table'::text WHEN (rel.relkind = 'v'::"char") THEN 'view'::text WHEN (rel.relkind = 'i'::"char") THEN 'index'::text WHEN (rel.relkind = 'S'::"char") THEN 'sequence'::text ELSE NULL::text END AS objtype, rel.relnamespace AS objnamespace, CASE WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) END AS objname, d.description FROM ((pg_description d JOIN pg_class rel ON (((d.classoid = rel.tableoid) AND (d.objoid = rel.oid)))) JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) WHERE (d.objsubid = 0) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'column'::text AS objtype, rel.relnamespace AS objnamespace, ((CASE WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) END || '.'::text) || (att.attname)::text) AS objname, d.description FROM (((pg_description d JOIN pg_class rel ON (((d.classoid = rel.tableoid) AND (d.objoid = rel.oid)))) JOIN pg_attribute att ON (((rel.oid = att.attrelid) AND (d.objsubid = att.attnum)))) JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) WHERE (d.objsubid <> 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, CASE WHEN (pro.proisagg = true) THEN 'aggregate'::text WHEN (pro.proisagg = false) THEN 'function'::text ELSE NULL::text END AS objtype, pro.pronamespace AS objnamespace, (((CASE WHEN pg_function_is_visible(pro.oid) THEN quote_ident((pro.proname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((pro.proname)::text)) END || '('::text) || pg_get_function_arguments(pro.oid)) || ')'::text) AS objname, d.description FROM ((pg_description d JOIN pg_proc pro ON (((d.classoid = pro.tableoid) AND (d.objoid = pro.oid)))) JOIN pg_namespace nsp ON ((pro.pronamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'cast'::text AS objtype, NULL::oid AS objnamespace, (((('('::text || format_type(cst.castsource, NULL::integer)) || ' AS '::text) || format_type(cst.casttarget, NULL::integer)) || ')'::text) AS objname, d.description FROM (pg_description d JOIN pg_cast cst ON (((d.classoid = cst.tableoid) AND (d.objoid = cst.oid)))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'constraint'::text AS objtype, rel.relnamespace AS objnamespace, (((con.conname)::text || ' ON '::text) || CASE WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) END) AS objname, d.description FROM (((pg_description d JOIN pg_constraint con ON (((d.classoid = con.tableoid) AND (d.objoid = con.oid)))) JOIN pg_class rel ON ((con.conrelid = rel.oid))) JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'conversion'::text AS objtype, con.connamespace AS objnamespace, CASE WHEN pg_conversion_is_visible(con.oid) THEN quote_ident((con.conname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((con.conname)::text)) END AS objname, d.description FROM ((pg_description d JOIN pg_conversion con ON (((d.classoid = con.tableoid) AND (d.objoid = con.oid)))) JOIN pg_namespace nsp ON ((con.connamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, CASE WHEN (typ.typtype = 'd'::"char") THEN 'domain'::text ELSE 'type'::text END AS objtype, typ.typnamespace AS objnamespace, CASE WHEN pg_type_is_visible(typ.oid) THEN quote_ident((typ.typname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((typ.typname)::text)) END AS objname, d.description FROM ((pg_description d JOIN pg_type typ ON (((d.classoid = typ.tableoid) AND (d.objoid = typ.oid)))) JOIN pg_namespace nsp ON ((typ.typnamespace = nsp.oid))) WHERE (d.objsubid = 0)) 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 pg_class.oid FROM pg_class WHERE (pg_class.relname = 'pg_largeobject'::name))) AND (d.objsubid = 0))) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'operator'::text AS objtype, opr.oprnamespace AS objnamespace, (((((CASE WHEN pg_operator_is_visible(opr.oid) THEN (opr.oprname)::text ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || (opr.oprname)::text) END || '('::text) || CASE WHEN (opr.oprkind = 'r'::"char") THEN 'NONE'::text ELSE format_type(opr.oprleft, NULL::integer) END) || ','::text) || CASE WHEN (opr.oprkind = 'l'::"char") THEN 'NONE'::text ELSE format_type(opr.oprright, NULL::integer) END) || ')'::text) AS objname, d.description FROM ((pg_description d JOIN pg_operator opr ON (((d.classoid = opr.tableoid) AND (d.objoid = opr.oid)))) JOIN pg_namespace nsp ON ((opr.oprnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'operator class'::text AS objtype, opc.opcnamespace AS objnamespace, ((CASE WHEN pg_opclass_is_visible(opc.oid) THEN quote_ident((opc.opcname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((opc.opcname)::text)) END || ' USING '::text) || (am.amname)::text) AS objname, d.description FROM (((pg_description d JOIN pg_opclass opc ON (((d.classoid = opc.tableoid) AND (d.objoid = opc.oid)))) JOIN pg_am am ON ((opc.opcmethod = am.oid))) JOIN pg_namespace nsp ON ((opc.opcnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'operator family'::text AS objtype, opf.opfnamespace AS objnamespace, ((CASE WHEN pg_opfamily_is_visible(opf.oid) THEN quote_ident((opf.opfname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((opf.opfname)::text)) END || ' USING '::text) || (am.amname)::text) AS objname, d.description FROM (((pg_description d JOIN pg_opfamily opf ON (((d.classoid = opf.tableoid) AND (d.objoid = opf.oid)))) JOIN pg_am am ON ((opf.opfmethod = am.oid))) JOIN pg_namespace nsp ON ((opf.opfnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'language'::text AS objtype, NULL::oid AS objnamespace, quote_ident((lan.lanname)::text) AS objname, d.description FROM (pg_description d JOIN pg_language lan ON (((d.classoid = lan.tableoid) AND (d.objoid = lan.oid)))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'rule'::text AS objtype, rel.relnamespace AS objnamespace, ((quote_ident((rew.rulename)::text) || ' ON '::text) || CASE WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) END) AS objname, d.description FROM (((pg_description d JOIN pg_rewrite rew ON (((d.classoid = rew.tableoid) AND (d.objoid = rew.oid)))) JOIN pg_class rel ON ((rew.ev_class = rel.oid))) JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'schema'::text AS objtype, nsp.oid AS objnamespace, quote_ident((nsp.nspname)::text) AS objname, d.description FROM (pg_description d JOIN pg_namespace nsp ON (((d.classoid = nsp.tableoid) AND (d.objoid = nsp.oid)))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'text search configuration'::text AS objtype, cfg.cfgnamespace AS objnamespace, CASE WHEN pg_ts_config_is_visible(cfg.oid) THEN quote_ident((cfg.cfgname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((cfg.cfgname)::text)) END AS objname, d.description FROM ((pg_description d JOIN pg_ts_config cfg ON (((d.classoid = cfg.tableoid) AND (d.objoid = cfg.oid)))) JOIN pg_namespace nsp ON ((cfg.cfgnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'text search dictionary'::text AS objtype, dict.dictnamespace AS objnamespace, CASE WHEN pg_ts_dict_is_visible(dict.oid) THEN quote_ident((dict.dictname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((dict.dictname)::text)) END AS objname, d.description FROM ((pg_description d JOIN pg_ts_dict dict ON (((d.classoid = dict.tableoid) AND (d.objoid = dict.oid)))) JOIN pg_namespace nsp ON ((dict.dictnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'text search parser'::text AS objtype, prs.prsnamespace AS objnamespace, CASE WHEN pg_ts_parser_is_visible(prs.oid) THEN quote_ident((prs.prsname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((prs.prsname)::text)) END AS objname, d.description FROM ((pg_description d JOIN pg_ts_parser prs ON (((d.classoid = prs.tableoid) AND (d.objoid = prs.oid)))) JOIN pg_namespace nsp ON ((prs.prsnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'text search template'::text AS objtype, tmpl.tmplnamespace AS objnamespace, CASE WHEN pg_ts_template_is_visible(tmpl.oid) THEN quote_ident((tmpl.tmplname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((tmpl.tmplname)::text)) END AS objname, d.description FROM ((pg_description d JOIN pg_ts_template tmpl ON (((d.classoid = tmpl.tableoid) AND (d.objoid = tmpl.oid)))) JOIN pg_namespace nsp ON ((tmpl.tmplnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'trigger'::text AS objtype, rel.relnamespace AS objnamespace, ((quote_ident((tg.tgname)::text) || ' ON '::text) || CASE WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text) ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text)) END) AS objname, d.description FROM (((pg_description d JOIN pg_trigger tg ON (((d.classoid = tg.tableoid) AND (d.objoid = tg.oid)))) JOIN pg_class rel ON ((tg.tgrelid = rel.oid))) JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, d.objsubid, 'access method'::text AS objtype, NULL::oid AS objnamespace, quote_ident((am.amname)::text) AS objname, d.description FROM (pg_description d JOIN pg_am am ON (((d.classoid = am.tableoid) AND (d.objoid = am.oid)))) WHERE (d.objsubid = 0)) UNION ALL SELECT d.objoid, d.classoid, NULL::integer AS objsubid, 'database'::text AS objtype, NULL::oid AS objnamespace, quote_ident((dat.datname)::text) AS objname, d.description FROM (pg_shdescription d JOIN pg_database dat ON (((d.classoid = dat.tableoid) AND (d.objoid = dat.oid))))) UNION ALL SELECT d.objoid, d.classoid, NULL::integer AS objsubid, 'role'::text AS objtype, NULL::oid AS objnamespace, quote_ident((rol.rolname)::text) AS objname, d.description FROM (pg_shdescription d JOIN pg_authid rol ON (((d.classoid = rol.tableoid) AND (d.objoid = rol.oid))))) UNION ALL SELECT d.objoid, d.classoid, NULL::integer AS objsubid, 'tablespace'::text AS objtype, NULL::oid AS objnamespace, quote_ident((spc.spcname)::text) AS objname, d.description FROM (pg_shdescription d JOIN pg_tablespace spc ON (((d.classoid = spc.tableoid) AND (d.objoid = spc.oid))));
pg_cursors | SELECT c.name, c.statement, c.is_holdable, c.is_binary, c.is_scrollable, c.creation_time FROM pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time);
pg_group | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin);
pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS tablespace, pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char"));
@@ -1333,7 +1334,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color))));
street | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath);
toyemp | SELECT emp.name, emp.age, emp.location, (12 * emp.salary) AS annualsal FROM emp;
-(55 rows)
+(56 rows)
SELECT tablename, rulename, definition FROM pg_rules
ORDER BY tablename, rulename;
Robert Haas <robertmhaas@gmail.com> writes:
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.
Unless you propose to break psql's hard-won backwards compatibility,
this isn't going to accomplish anything towards making describe.c
simpler or shorter. Also, it seems to me that what you've mostly done
is to move complexity from describe.c (where the query can be fixed
easily if it's found to be broken) to system_views.sql (where it cannot
be changed without an initdb).
How about improving the query in-place in describe.c instead?
regards, tom lane
On Mon, Sep 20, 2010 at 1:07 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
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.Unless you propose to break psql's hard-won backwards compatibility,
this isn't going to accomplish anything towards making describe.c
simpler or shorter. Also, it seems to me that what you've mostly done
is to move complexity from describe.c (where the query can be fixed
easily if it's found to be broken) to system_views.sql (where it cannot
be changed without an initdb).
Those are legitimate gripes, but...
How about improving the query in-place in describe.c instead?
...I still don't care much for this option. It doesn't do anything to
easy the difficulty of ad-hoc queries, which I think is important (and
seems likely to be even more important for security labels - because
people who use that feature at all are going to label the heck out of
everything, whereas comments are never strictly necessary), and it
isn't useful for clients other than psql. Most of this code hasn't
been touched since 2002, despite numerous, relevant changes since
then. You could take as support for your position that we need the
ability to fix future bugs without initdb, but my reading of it is
that that code is just too awful to be easily maintained and so no one
has bothered.
(It also supports my previous contention that we need a way to make
minor system catalog updates without forcing initdb, but that's a
problem for another day.)
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
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@ak.jp.nec.com>
2010/9/24 KaiGai Kohei <kaigai@ak.jp.nec.com>:
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.
Oh, dear, how embarassing. Perhaps it should be written as:
d.classoid = 'pg_catalog.pg_largeobject'::regclass.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
KaiGai Kohei <kaigai@ak.jp.nec.com> writes:
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'))
:
Actually, the preferred way to spell that sort of thing is
WHERE
d.classoid = 'pg_catalog.pg_largeobject'::regclass
which is not only shorter but orders of magnitude more efficient.
regards, tom lane