On Wed, Jun 1, 2016 at 8:25 PM, Teodor Sigaev <[email protected]> wrote:
>>>> As far as I can see, COMMENT ON has no support for access methods.
>>>> Wouldn't we want to add it as it is created by a command? On top of
>>>> that, perhaps we could have a backslash command in psql to list the
>>>> supported access methods, like \dam[S]? The system methods would be in
>>>> this case all the in-core ones.
>>>
>>>
>>> +1.
>>
>>
>> Are there other opinions? That's not a 9.6 blocker IMO, so I could get
>> patches out for 10.0 if needed.
>
>
> I missed that on review process, but I'm agree it should be implemented.
So, I have taken the time to hack that, and finished with the patch
attached, that is less intrusive than I thought first:
- COMMENT support is added for access methods
- Added meta-command \dA in psql to list the available access methods:
=# \dA
List of access methods
Name | Type
--------+-------
bloom | Index
brin | Index
btree | Index
gin | Index
gist | Index
hash | Index
spgist | Index
(7 rows)
=# \dA+
List of access methods
Name | Type | Handler | Description
--------+-------+-------------+----------------------------------------
bloom | Index | blhandler | bloom index access method
brin | Index | brinhandler | block range index (BRIN) access method
btree | Index | bthandler | b-tree index access method
gin | Index | ginhandler | GIN index access method
gist | Index | gisthandler | GiST index access method
hash | Index | hashhandler | hash index access method
spgist | Index | spghandler | SP-GiST index access method
(7 rows)
- Fixed a missing tab completion for DROP ACCESS METHOD.
I have added an open item for 9.6 regarding this patch, that would be
good to complete this work in this release for consistency with the
other objects.
Regards,
--
Michael
From 12c5d77c7d66cd8b33c697fc389cf9915d32765b Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email protected]>
Date: Thu, 2 Jun 2016 12:47:46 +0900
Subject: [PATCH] Add support for COMMENT ON and psql meta-command for access
methods
473b932 has visibly forgotten that access methods, being database objects
could have a description associated with them. Having a psql-level meta
command that allows to list all the access commands available on the
server was missing as well.
At the same time, I have noticed that tab completion of psql could be
more verbose regarding access methods, those things are fixed at the
same time.
---
contrib/bloom/bloom--1.0.sql | 1 +
doc/src/sgml/ref/comment.sgml | 1 +
doc/src/sgml/ref/psql-ref.sgml | 13 +++++++++
src/backend/parser/gram.y | 5 ++--
src/bin/psql/command.c | 3 +++
src/bin/psql/describe.c | 61 ++++++++++++++++++++++++++++++++++++++++++
src/bin/psql/describe.h | 3 +++
src/bin/psql/tab-complete.c | 30 ++++++++++++++++-----
8 files changed, 108 insertions(+), 9 deletions(-)
diff --git a/contrib/bloom/bloom--1.0.sql b/contrib/bloom/bloom--1.0.sql
index 7fa7513..87b5442 100644
--- a/contrib/bloom/bloom--1.0.sql
+++ b/contrib/bloom/bloom--1.0.sql
@@ -5,6 +5,7 @@ LANGUAGE C;
-- Access method
CREATE ACCESS METHOD bloom TYPE INDEX HANDLER blhandler;
+COMMENT ON ACCESS METHOD bloom IS 'bloom index access method';
-- Opclasses
diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml
index 3321d4b..9582de8 100644
--- a/doc/src/sgml/ref/comment.sgml
+++ b/doc/src/sgml/ref/comment.sgml
@@ -23,6 +23,7 @@ PostgreSQL documentation
<synopsis>
COMMENT ON
{
+ ACCESS METHOD <replaceable class="PARAMETER">object_name</replaceable> |
AGGREGATE <replaceable class="PARAMETER">aggregate_name</replaceable> ( <replaceable>aggregate_signature</replaceable> ) |
CAST (<replaceable>source_type</replaceable> AS <replaceable>target_type</replaceable>) |
COLLATION <replaceable class="PARAMETER">object_name</replaceable> |
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index df79a37..4079ac6 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1130,6 +1130,19 @@ testdb=>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>\dA[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+
+ <listitem>
+ <para>
+ Lists access methods. If <replaceable
+ class="parameter">pattern</replaceable> is specified, only access
+ methods whose names match the pattern are shown. If
+ <literal>+</literal> is appended to the command name, each access
+ method is listed with its associated handler function and description.
+ </para>
+ </listitem>
+ </varlistentry>
<varlistentry>
<term><literal>\db[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 20384db..5d646e7 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -5693,8 +5693,8 @@ opt_restart_seqs:
* The COMMENT ON statement can take different forms based upon the type of
* the object associated with the comment. The form of the statement is:
*
- * COMMENT ON [ [ CONVERSION | COLLATION | DATABASE | DOMAIN |
- * EXTENSION | EVENT TRIGGER | FOREIGN DATA WRAPPER |
+ * COMMENT ON [ [ ACCESS METHOD | CONVERSION | COLLATION | DATABASE |
+ * DOMAIN | EXTENSION | EVENT TRIGGER | FOREIGN DATA WRAPPER |
* FOREIGN TABLE | INDEX | [PROCEDURAL] LANGUAGE |
* MATERIALIZED VIEW | POLICY | ROLE | SCHEMA | SEQUENCE |
* SERVER | TABLE | TABLESPACE |
@@ -5896,6 +5896,7 @@ comment_type:
| TABLE { $$ = OBJECT_TABLE; }
| VIEW { $$ = OBJECT_VIEW; }
| MATERIALIZED VIEW { $$ = OBJECT_MATVIEW; }
+ | ACCESS METHOD { $$ = OBJECT_ACCESS_METHOD; }
| COLLATION { $$ = OBJECT_COLLATION; }
| CONVERSION_P { $$ = OBJECT_CONVERSION; }
| TABLESPACE { $$ = OBJECT_TABLESPACE; }
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 693b531..543fe5f 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -402,6 +402,9 @@ exec_command(const char *cmd,
/* standard listing of interesting things */
success = listTables("tvmsE", NULL, show_verbose, show_system);
break;
+ case 'A':
+ success = describeAccessMethods(pattern, show_verbose);
+ break;
case 'a':
success = describeAggregates(pattern, show_verbose, show_system);
break;
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 0a771bd..4e9e6ac 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -129,6 +129,67 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem)
return true;
}
+/* \dA
+ * Takes an optional regexp to select particular access methods
+ */
+bool
+describeAccessMethods(const char *pattern, bool verbose)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ if (pset.sversion < 90600)
+ {
+ psql_error("The server (version %d.%d) does not support access methods.\n",
+ pset.sversion / 10000, (pset.sversion / 100) % 100);
+ return true;
+ }
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT amname AS \"%s\",\n"
+ " CASE amtype"
+ " WHEN 'i' THEN '%s'"
+ " END AS \"%s\"",
+ gettext_noop("Name"),
+ gettext_noop("Index"),
+ gettext_noop("Type"));
+
+ if (verbose)
+ {
+ appendPQExpBuffer(&buf,
+ ",\n amhandler AS \"%s\",\n"
+ " pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"",
+ gettext_noop("Handler"),
+ gettext_noop("Description"));
+ }
+
+ appendPQExpBufferStr(&buf,
+ "\nFROM pg_catalog.pg_am\n");
+
+ processSQLNamePattern(pset.db, &buf, pattern, false, false,
+ NULL, "amname", NULL,
+ NULL);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("List of access methods");
+ myopt.translate_header = true;
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
/* \db
* Takes an optional regexp to select particular tablespaces
*/
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 9672275..20a6508 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -12,6 +12,9 @@
/* \da */
extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem);
+/* \dA */
+extern bool describeAccessMethods(const char *pattern, bool verbose);
+
/* \db */
extern bool describeTablespaces(const char *pattern, bool verbose);
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index a62ffe6..7cc4090 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -830,6 +830,11 @@ static const SchemaQuery Query_for_list_of_matviews = {
" FROM pg_catalog.pg_event_trigger "\
" WHERE substring(pg_catalog.quote_ident(evtname),1,%d)='%s'"
+#define Query__for_list_of_access_methods \
+" SELECT pg_catalog.quote_ident(amname) "\
+" FROM pg_catalog.pg_am "\
+" WHERE substring(pg_catalog.quote_ident(evtname),1,%d)='%s'"
+
#define Query_for_list_of_tablesample_methods \
" SELECT pg_catalog.quote_ident(proname) "\
" FROM pg_catalog.pg_proc "\
@@ -1276,7 +1281,7 @@ psql_completion(const char *text, int start, int end)
static const char *const backslash_commands[] = {
"\\a", "\\connect", "\\conninfo", "\\C", "\\cd", "\\copy",
"\\copyright", "\\crosstabview",
- "\\d", "\\da", "\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD",
+ "\\d", "\\da", "\\dA", "\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD",
"\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df",
"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL",
"\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\drds", "\\ds", "\\dS",
@@ -1910,15 +1915,18 @@ psql_completion(const char *text, int start, int end)
else if (Matches2("COMMENT", "ON"))
{
static const char *const list_COMMENT[] =
- {"CAST", "COLLATION", "CONVERSION", "DATABASE", "EVENT TRIGGER", "EXTENSION",
- "FOREIGN DATA WRAPPER", "FOREIGN TABLE",
- "SERVER", "INDEX", "LANGUAGE", "POLICY", "RULE", "SCHEMA", "SEQUENCE",
- "TABLE", "TYPE", "VIEW", "MATERIALIZED VIEW", "COLUMN", "AGGREGATE", "FUNCTION",
- "OPERATOR", "TRIGGER", "CONSTRAINT", "DOMAIN", "LARGE OBJECT",
- "TABLESPACE", "TEXT SEARCH", "ROLE", NULL};
+ {"ACCESS METHOD", "CAST", "COLLATION", "CONVERSION", "DATABASE",
+ "EVENT TRIGGER", "EXTENSION","FOREIGN DATA WRAPPER",
+ "FOREIGN TABLE", "SERVER", "INDEX", "LANGUAGE", "POLICY", "RULE",
+ "SCHEMA", "SEQUENCE", "TABLE", "TYPE", "VIEW", "MATERIALIZED VIEW",
+ "COLUMN", "AGGREGATE", "FUNCTION", "OPERATOR", "TRIGGER",
+ "CONSTRAINT", "DOMAIN", "LARGE OBJECT", "TABLESPACE",
+ "TEXT SEARCH", "ROLE", NULL};
COMPLETE_WITH_LIST(list_COMMENT);
}
+ else if (Matches4("COMMENT", "ON", "ACCESS", "METHOD"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_access_methods);
else if (Matches3("COMMENT", "ON", "FOREIGN"))
COMPLETE_WITH_LIST2("DATA WRAPPER", "TABLE");
else if (Matches4("COMMENT", "ON", "TEXT", "SEARCH"))
@@ -2331,6 +2339,12 @@ psql_completion(const char *text, int start, int end)
else if (Matches5("DROP", "TRIGGER", MatchAny, "ON", MatchAny))
COMPLETE_WITH_LIST2("CASCADE", "RESTRICT");
+ /* DROP ACCESS METHOD */
+ else if (Matches2("DROP", "ACCESS"))
+ COMPLETE_WITH_CONST("METHOD");
+ else if (Matches3("DROP", "ACCESS", "METHOD"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_access_methods);
+
/* DROP EVENT TRIGGER */
else if (Matches2("DROP", "EVENT"))
COMPLETE_WITH_CONST("TRIGGER");
@@ -2931,6 +2945,8 @@ psql_completion(const char *text, int start, int end)
}
else if (TailMatchesCS1("\\da*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_aggregates, NULL);
+ else if (TailMatchesCS1("\\dA*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_access_methods);
else if (TailMatchesCS1("\\db*"))
COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
else if (TailMatchesCS1("\\dD*"))
--
2.8.3
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
