Skip to content

Commit f9a726a

Browse files
bmomjianbmomjian
committed
I've created a new shared catalog table pg_shdescription to store
comments on cluster global objects like databases, tablespaces, and roles. It touches a lot of places, but not much in the way of big changes. The only design decision I made was to duplicate the query and manipulation functions rather than to try and have them handle both shared and local comments. I believe this is simpler for the code and not an issue for callers because they know what type of object they are dealing with. This has resulted in a shobj_description function analagous to obj_description and backend functions [Create/Delete]SharedComments mirroring the existing [Create/Delete]Comments functions. pg_shdescription.h goes into src/include/catalog/ Kris Jurka
1 parent 95dbf9c commit f9a726a

File tree

25 files changed

+616
-81
lines changed

25 files changed

+616
-81
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 79 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
<!--
22
Documentation of the system catalogs, directed toward PostgreSQL developers
3-
$PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.119 2006/01/21 19:05:59 tgl Exp $
3+
$PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.120 2006/02/12 03:22:16 momjian Exp $
44
-->
55

66
<chapter id="catalogs">
@@ -188,6 +188,11 @@
188188
<entry>dependencies on shared objects</entry>
189189
</row>
190190

191+
<row>
192+
<entry><link linkend="catalog-pg-shdescription"><structname>pg_shdescription</structname></link></entry>
193+
<entry>comments on shared objects</entry>
194+
</row>
195+
191196
<row>
192197
<entry><link linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link></entry>
193198
<entry>planner statistics</entry>
@@ -2230,6 +2235,12 @@
22302235
contents of <structname>pg_description</structname>.
22312236
</para>
22322237

2238+
<para>
2239+
See also <link linkend="catalog-pg-shdescription"><structname>pg_shdescription</structname></link>,
2240+
which performs a similar function for descriptions involving objects that
2241+
are shared across a database cluster.
2242+
</para>
2243+
22332244
<table>
22342245
<title><structname>pg_description</> Columns</title>
22352246

@@ -3612,6 +3623,73 @@
36123623

36133624
</sect1>
36143625

3626+
<sect1 id="catalog-pg-shdescription">
3627+
<title><structname>pg_shdescription</structname></title>
3628+
3629+
<indexterm zone="catalog-pg-shdescription">
3630+
<primary>pg_shdescription</primary>
3631+
</indexterm>
3632+
3633+
<para>
3634+
The catalog <structname>pg_shdescription</structname> stores optional
3635+
descriptions (comments) for shared database objects. Descriptions can
3636+
be manipulated with the <command>COMMENT</command> command and viewed
3637+
with <application>psql</application>'s <literal>\d</literal> commands.
3638+
</para>
3639+
3640+
<para>
3641+
See also <link linkend="catalog-pg-description"><structname>pg_description</structname></link>,
3642+
which performs a similar function for descriptions involving objects
3643+
within a single database.
3644+
</para>
3645+
3646+
<para>
3647+
Unlike most system catalogs, <structname>pg_shdescription</structname>
3648+
is shared across all databases of a cluster: there is only one
3649+
copy of <structname>pg_shdescription</structname> per cluster, not
3650+
one per database.
3651+
</para>
3652+
3653+
<table>
3654+
<title><structname>pg_shdescription</> Columns</title>
3655+
3656+
<tgroup cols=4>
3657+
<thead>
3658+
<row>
3659+
<entry>Name</entry>
3660+
<entry>Type</entry>
3661+
<entry>References</entry>
3662+
<entry>Description</entry>
3663+
</row>
3664+
</thead>
3665+
3666+
<tbody>
3667+
<row>
3668+
<entry><structfield>objoid</structfield></entry>
3669+
<entry><type>oid</type></entry>
3670+
<entry>any OID column</entry>
3671+
<entry>The OID of the object this description pertains to</entry>
3672+
</row>
3673+
3674+
<row>
3675+
<entry><structfield>classoid</structfield></entry>
3676+
<entry><type>oid</type></entry>
3677+
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
3678+
<entry>The OID of the system catalog this object appears in</entry>
3679+
</row>
3680+
3681+
<row>
3682+
<entry><structfield>description</structfield></entry>
3683+
<entry><type>text</type></entry>
3684+
<entry></entry>
3685+
<entry>Arbitrary text that servers as the description of this object.</entry>
3686+
</row>
3687+
</tbody>
3688+
</tgroup>
3689+
</table>
3690+
3691+
</sect1>
3692+
36153693

36163694
<sect1 id="catalog-pg-statistic">
36173695
<title><structname>pg_statistic</structname></title>

‎doc/src/sgml/func.sgml

Lines changed: 18 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.304 2006/02/11 03:32:38 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.305 2006/02/12 03:22:16 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -9463,6 +9463,10 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
94639463
<primary>col_description</primary>
94649464
</indexterm>
94659465

9466+
<indexterm zone="functions-info">
9467+
<primary>shobj_description</primary>
9468+
</indexterm>
9469+
94669470
<indexterm zone="functions-info">
94679471
<primary>comment</primary>
94689472
<secondary sortas="database objects">about database objects</secondary>
@@ -9499,6 +9503,11 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
94999503
<entry><type>text</type></entry>
95009504
<entry>get comment for a table column</entry>
95019505
</row>
9506+
<row>
9507+
<entry><literal><function>shobj_description</function>(<parameter>object_oid</parameter>, <parameter>catalog_name</parameter>)</literal></entry>
9508+
<entry><type>text</type></entry>
9509+
<entry>get comment for a shared database object</entry>
9510+
</row>
95029511
</tbody>
95039512
</tgroup>
95049513
</table>
@@ -9521,6 +9530,14 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
95219530
<function>obj_description</function> cannot be used for table columns since
95229531
columns do not have OIDs of their own.
95239532
</para>
9533+
9534+
<para>
9535+
<function>shobj_description</function> is used just like
9536+
<function>obj_description</function> only that it is used for retrieving
9537+
comments on shared objects. Some system catalogs are global to all
9538+
databases within each cluster and their descriptions are stored globally
9539+
as well.
9540+
</para>
95249541
</sect1>
95259542

95269543
<sect1 id="functions-admin">

‎doc/src/sgml/ref/comment.sgml

Lines changed: 12 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/comment.sgml,v 1.29 2005/06/08 21:15:27 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/comment.sgml,v 1.30 2006/02/12 03:22:17 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -36,9 +36,11 @@ COMMENT ON
3636
OPERATOR <replaceable class="PARAMETER">op</replaceable> (<replaceable class="PARAMETER">leftoperand_type</replaceable>, <replaceable class="PARAMETER">rightoperand_type</replaceable>) |
3737
OPERATOR CLASS <replaceable class="PARAMETER">object_name</replaceable> USING <replaceable class="parameter">index_method</replaceable> |
3838
[ PROCEDURAL ] LANGUAGE <replaceable class="PARAMETER">object_name</replaceable> |
39+
ROLE <replaceable class="PARAMETER">object_name</replaceable> |
3940
RULE <replaceable class="PARAMETER">rule_name</replaceable> ON <replaceable class="PARAMETER">table_name</replaceable> |
4041
SCHEMA <replaceable class="PARAMETER">object_name</replaceable> |
4142
SEQUENCE <replaceable class="PARAMETER">object_name</replaceable> |
43+
TABLESPACE <replaceable class="PARAMETER">object_name</replaceable> |
4244
TRIGGER <replaceable class="PARAMETER">trigger_name</replaceable> ON <replaceable class="PARAMETER">table_name</replaceable> |
4345
TYPE <replaceable class="PARAMETER">object_name</replaceable> |
4446
VIEW <replaceable class="PARAMETER">object_name</replaceable>
@@ -67,7 +69,8 @@ COMMENT ON
6769
<command>\dd</command>, <command>\d+</command>, and <command>\l+</command>.
6870
Other user interfaces to retrieve comments can be built atop
6971
the same built-in functions that <application>psql</application> uses, namely
70-
<function>obj_description</> and <function>col_description</>
72+
<function>obj_description</>, <function>col_description</>,
73+
and <function>shobj_description</>
7174
(see <xref linkend="functions-info-comment-table">).
7275
</para>
7376
</refsect1>
@@ -197,17 +200,15 @@ COMMENT ON
197200
<refsect1>
198201
<title>Notes</title>
199202

200-
<para>
201-
A comment for a database can only be created in that database,
202-
and will only be visible in that database, not in other databases.
203-
</para>
204-
205203
<para>
206204
There is presently no security mechanism for comments: any user
207205
connected to a database can see all the comments for objects in
208206
that database (although only superusers can change comments for
209-
objects that they don't own). Therefore, don't put
210-
security-critical information in comments.
207+
objects that they don't own). For shared objects such as
208+
databases, roles, and tablespaces comments are stored gloablly
209+
and any user connected to any database can see all the comments
210+
for shared objects. Therefore, don't put security-critical
211+
information in comments.
211212
</para>
212213
</refsect1>
213214

@@ -245,10 +246,12 @@ COMMENT ON LARGE OBJECT 346344 IS 'Planning document';
245246
COMMENT ON OPERATOR ^ (text, text) IS 'Performs intersection of two texts';
246247
COMMENT ON OPERATOR - (NONE, text) IS 'This is a prefix operator on text';
247248
COMMENT ON OPERATOR CLASS int4ops USING btree IS '4 byte integer operators for btrees';
249+
COMMENT ON ROLE my_role IS 'Administration group for finance tables';
248250
COMMENT ON RULE my_rule ON my_table IS 'Logs updates of employee records';
249251
COMMENT ON SCHEMA my_schema IS 'Departmental data';
250252
COMMENT ON SEQUENCE my_sequence IS 'Used to generate primary keys';
251253
COMMENT ON TABLE my_schema.my_table IS 'Employee Information';
254+
COMMENT ON TABLESPACE my_tablespace IS 'Tablespace for indexes';
252255
COMMENT ON TRIGGER my_trigger ON my_table IS 'Used for RI';
253256
COMMENT ON TYPE complex IS 'Complex number data type';
254257
COMMENT ON VIEW my_view IS 'View of departmental costs';

‎src/backend/catalog/Makefile

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
#
33
# Makefile for backend/catalog
44
#
5-
# $PostgreSQL: pgsql/src/backend/catalog/Makefile,v 1.58 2005/12/09 21:19:35 petere Exp $
5+
# $PostgreSQL: pgsql/src/backend/catalog/Makefile,v 1.59 2006/02/12 03:22:17 momjian Exp $
66
#
77
#-------------------------------------------------------------------------
88

@@ -15,7 +15,7 @@ OBJS = catalog.o dependency.o heap.o index.o indexing.o namespace.o aclchk.o \
1515
pg_largeobject.o pg_namespace.o pg_operator.o pg_proc.o pg_shdepend.o \
1616
pg_type.o
1717

18-
BKIFILES = postgres.bki postgres.description
18+
BKIFILES = postgres.bki postgres.description postgres.shdescription
1919

2020
all: SUBSYS.o $(BKIFILES)
2121

@@ -34,7 +34,7 @@ POSTGRES_BKI_SRCS := $(addprefix $(top_srcdir)/src/include/catalog/,\
3434
pg_rewrite.h pg_trigger.h pg_listener.h pg_description.h pg_cast.h \
3535
pg_namespace.h pg_conversion.h pg_depend.h \
3636
pg_database.h pg_tablespace.h pg_pltemplate.h \
37-
pg_authid.h pg_auth_members.h pg_shdepend.h \
37+
pg_authid.h pg_auth_members.h pg_shdepend.h pg_shdescription.h \
3838
indexing.h \
3939
)
4040

@@ -43,6 +43,8 @@ pg_includes := $(sort -I$(top_srcdir)/src/include -I$(top_builddir)/src/include)
4343
# see explanation in ../parser/Makefile
4444
postgres.description: postgres.bki ;
4545

46+
postgres.shdescription: postgres.bki ;
47+
4648
postgres.bki: genbki.sh $(POSTGRES_BKI_SRCS) \
4749
$(top_srcdir)/src/include/postgres_ext.h $(top_builddir)/src/include/pg_config_manual.h
4850
AWK='$(AWK)' $(SHELL) $< $(pg_includes) --set-version=$(VERSION) -o postgres $(POSTGRES_BKI_SRCS)
@@ -51,6 +53,7 @@ postgres.bki: genbki.sh $(POSTGRES_BKI_SRCS) \
5153
install-data: $(BKIFILES) installdirs
5254
$(INSTALL_DATA) postgres.bki '$(DESTDIR)$(datadir)/postgres.bki'
5355
$(INSTALL_DATA) postgres.description '$(DESTDIR)$(datadir)/postgres.description'
56+
$(INSTALL_DATA) postgres.shdescription '$(DESTDIR)$(datadir)/postgres.shdescription'
5457
$(INSTALL_DATA) $(srcdir)/system_views.sql '$(DESTDIR)$(datadir)/system_views.sql'
5558
$(INSTALL_DATA) $(srcdir)/information_schema.sql '$(DESTDIR)$(datadir)/information_schema.sql'
5659
$(INSTALL_DATA) $(srcdir)/sql_features.txt '$(DESTDIR)$(datadir)/sql_features.txt'

‎src/backend/catalog/genbki.sh

Lines changed: 20 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
#
1212
#
1313
# IDENTIFICATION
14-
# $PostgreSQL: pgsql/src/backend/catalog/genbki.sh,v 1.37 2005/06/28 05:08:52 tgl Exp $
14+
# $PostgreSQL: pgsql/src/backend/catalog/genbki.sh,v 1.38 2006/02/12 03:22:17 momjian Exp $
1515
#
1616
# NOTES
1717
# non-essential whitespace is removed from the generated file.
@@ -103,7 +103,7 @@ fi
103103

104104
TMPFILE="genbkitmp$$.c"
105105

106-
trap "rm -f $TMPFILE ${OUTPUT_PREFIX}.bki.$$ ${OUTPUT_PREFIX}.description.$$" 0 1 2 3 15
106+
trap "rm -f $TMPFILE ${OUTPUT_PREFIX}.bki.$$ ${OUTPUT_PREFIX}.description.$$ ${OUTPUT_PREFIX}.shdescription.$$" 0 1 2 3 15
107107

108108

109109
# Get NAMEDATALEN from postgres_ext.h
@@ -131,6 +131,7 @@ for dir in $INCLUDE_DIRS; do
131131
done
132132

133133
touch ${OUTPUT_PREFIX}.description.$$
134+
touch ${OUTPUT_PREFIX}.shdescription.$$
134135

135136
# ----------------
136137
# Strip comments and other trash from .h
@@ -201,7 +202,7 @@ comment_level > 0 { next; }
201202
# ----------------
202203
# DATA() statements are basically passed right through after
203204
# stripping off the DATA( and the ) on the end.
204-
# Remember the OID for use by DESCR().
205+
# Remember the OID for use by DESCR() and SHDESCR().
205206
# ----------------
206207
/^DATA\(/ {
207208
data = substr($0, 6, length($0) - 6);
@@ -225,6 +226,16 @@ comment_level > 0 { next; }
225226
next;
226227
}
227228
229+
/^SHDESCR\(/ {
230+
if (oid != 0)
231+
{
232+
data = substr($0, 10, length($0) - 11);
233+
if (data != "")
234+
printf "%d\t%s\t%s\n", oid, catalog, data >>shdescriptionfile;
235+
}
236+
next;
237+
}
238+
228239
/^DECLARE_INDEX\(/ {
229240
# ----
230241
# end any prior catalog data insertions before starting a define index
@@ -365,7 +376,7 @@ END {
365376
reln_open = 0;
366377
}
367378
}
368-
' "descriptionfile=${OUTPUT_PREFIX}.description.$$" > $TMPFILE || exit
379+
' "descriptionfile=${OUTPUT_PREFIX}.description.$$" "shdescriptionfile=${OUTPUT_PREFIX}.shdescription.$$" > $TMPFILE || exit
369380

370381
echo "# PostgreSQL $major_version" >${OUTPUT_PREFIX}.bki.$$
371382

@@ -386,10 +397,15 @@ if [ `wc -c < ${OUTPUT_PREFIX}.description.$$` -lt 10000 ]; then
386397
echo "$CMDNAME: something seems to be wrong with the .description file" >&2
387398
exit 1
388399
fi
400+
if [ `wc -c < ${OUTPUT_PREFIX}.shdescription.$$` -lt 10 ]; then
401+
echo "$CMDNAME: something seems to be wrong with the .shdescription file" >&2
402+
exit 1
403+
fi
389404

390405
# Looks good, commit ...
391406

392407
mv ${OUTPUT_PREFIX}.bki.$$ ${OUTPUT_PREFIX}.bki || exit
393408
mv ${OUTPUT_PREFIX}.description.$$ ${OUTPUT_PREFIX}.description || exit
409+
mv ${OUTPUT_PREFIX}.shdescription.$$ ${OUTPUT_PREFIX}.shdescription || exit
394410

395411
exit 0

0 commit comments

Comments
 (0)