On Thu, Aug 20, 2020 at 08:16:19AM +0000, Georgios wrote: > Please find version 7 attached which hopefully addresses the error along with > a proper > expansion of the test coverage and removal of recently introduced > whitespace warnings.
+CREATE ROLE conditional_tableam_display_role; As a convention, regression tests need to have roles prefixed with "regress_" or this would cause some buildfarm members to turn red. Please see -DENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS (you could use that in your environment for example). So, as of the tests.. The role gets added to make sure that when using \d+ on the full schema as well as the various \d*+ variants we have a consistent owner. The addition of the relation size for the sequence and the btree index in the output generated is a problem though, because that's not really portable when compiling with other page sizes. It is true that there are other tests failing in this case, but I think that we should try to limit that if we can. In short, I agree that having some tests is better than nothing, but I would suggest to reduce their scope, as per the attached. Adding \dE as there are no foreign tables does not make much sense, and also I wondered why \dt+ was not added. Does the attached look correct to you? -- Michael
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index d81f1575bf..cdfec34389 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3678,7 +3678,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
PGresult *res;
printQueryOpt myopt = pset.popt;
int cols_so_far;
- bool translate_columns[] = {false, false, true, false, false, false, false, false};
+ bool translate_columns[] = {false, false, true, false, false, false, false, false, false};
/* If tabtypes is empty, we default to \dtvmsE (but see also command.c) */
if (!(showTables || showIndexes || showViews || showMatViews || showSeq || showForeign))
@@ -3751,6 +3751,16 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
* to; this might change with future additions to the output columns.
*/
+ /*
+ * Access methods exist for tables, materialized views and indexes.
+ * This has been introduced in PostgreSQL 12.
+ */
+ if (pset.sversion >= 120000 && !pset.hide_tableam &&
+ (showTables || showMatViews || showIndexes))
+ appendPQExpBuffer(&buf,
+ ",\n am.amname as \"%s\"",
+ gettext_noop("Access Method"));
+
/*
* As of PostgreSQL 9.0, use pg_table_size() to show a more accurate
* size of a table, including FSM, VM and TOAST tables.
@@ -3772,6 +3782,12 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
appendPQExpBufferStr(&buf,
"\nFROM pg_catalog.pg_class c"
"\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
+
+ if (pset.sversion >= 120000 && !pset.hide_tableam &&
+ (showTables || showMatViews || showIndexes))
+ appendPQExpBufferStr(&buf,
+ "\n LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam");
+
if (showIndexes)
appendPQExpBufferStr(&buf,
"\n LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 555d464f91..daac0ff49d 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -2795,20 +2795,28 @@ Argument data types | numeric
Type | func
\pset tuples_only false
--- check conditional tableam display
--- Create a heap2 table am handler with heapam handler
+-- check conditional am display
+\pset expanded off
+CREATE SCHEMA tableam_display;
+CREATE ROLE regress_display_role;
+ALTER SCHEMA tableam_display OWNER TO regress_display_role;
+SET search_path TO tableam_display;
CREATE ACCESS METHOD heap_psql TYPE TABLE HANDLER heap_tableam_handler;
+SET ROLE TO regress_display_role;
+-- Use only relations with a physical size of zero.
CREATE TABLE tbl_heap_psql(f1 int, f2 char(100)) using heap_psql;
CREATE TABLE tbl_heap(f1 int, f2 char(100)) using heap;
+CREATE VIEW view_heap_psql AS SELECT f1 from tbl_heap_psql;
+CREATE MATERIALIZED VIEW mat_view_heap_psql USING heap_psql AS SELECT f1 from tbl_heap_psql;
\d+ tbl_heap_psql
- Table "public.tbl_heap_psql"
+ Table "tableam_display.tbl_heap_psql"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------------+-----------+----------+---------+----------+--------------+-------------
f1 | integer | | | | plain | |
f2 | character(100) | | | | extended | |
\d+ tbl_heap
- Table "public.tbl_heap"
+ Table "tableam_display.tbl_heap"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------------+-----------+----------+---------+----------+--------------+-------------
f1 | integer | | | | plain | |
@@ -2816,7 +2824,7 @@ CREATE TABLE tbl_heap(f1 int, f2 char(100)) using heap;
\set HIDE_TABLEAM off
\d+ tbl_heap_psql
- Table "public.tbl_heap_psql"
+ Table "tableam_display.tbl_heap_psql"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------------+-----------+----------+---------+----------+--------------+-------------
f1 | integer | | | | plain | |
@@ -2824,16 +2832,68 @@ CREATE TABLE tbl_heap(f1 int, f2 char(100)) using heap;
Access method: heap_psql
\d+ tbl_heap
- Table "public.tbl_heap"
+ Table "tableam_display.tbl_heap"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------------+-----------+----------+---------+----------+--------------+-------------
f1 | integer | | | | plain | |
f2 | character(100) | | | | extended | |
Access method: heap
+-- AM is displayed for tables, indexes and materialized views.
+\d+
+ List of relations
+ Schema | Name | Type | Owner | Persistence | Access Method | Size | Description
+-----------------+--------------------+-------------------+----------------------+-------------+---------------+---------+-------------
+ tableam_display | mat_view_heap_psql | materialized view | regress_display_role | permanent | heap_psql | 0 bytes |
+ tableam_display | tbl_heap | table | regress_display_role | permanent | heap | 0 bytes |
+ tableam_display | tbl_heap_psql | table | regress_display_role | permanent | heap_psql | 0 bytes |
+ tableam_display | view_heap_psql | view | regress_display_role | permanent | | 0 bytes |
+(4 rows)
+
+\dt+
+ List of relations
+ Schema | Name | Type | Owner | Persistence | Access Method | Size | Description
+-----------------+---------------+-------+----------------------+-------------+---------------+---------+-------------
+ tableam_display | tbl_heap | table | regress_display_role | permanent | heap | 0 bytes |
+ tableam_display | tbl_heap_psql | table | regress_display_role | permanent | heap_psql | 0 bytes |
+(2 rows)
+
+\dm+
+ List of relations
+ Schema | Name | Type | Owner | Persistence | Access Method | Size | Description
+-----------------+--------------------+-------------------+----------------------+-------------+---------------+---------+-------------
+ tableam_display | mat_view_heap_psql | materialized view | regress_display_role | permanent | heap_psql | 0 bytes |
+(1 row)
+
+-- But not for views and sequences.
+\dv+
+ List of relations
+ Schema | Name | Type | Owner | Persistence | Size | Description
+-----------------+----------------+------+----------------------+-------------+---------+-------------
+ tableam_display | view_heap_psql | view | regress_display_role | permanent | 0 bytes |
+(1 row)
+
\set HIDE_TABLEAM on
-DROP TABLE tbl_heap, tbl_heap_psql;
+\d+
+ List of relations
+ Schema | Name | Type | Owner | Persistence | Size | Description
+-----------------+--------------------+-------------------+----------------------+-------------+---------+-------------
+ tableam_display | mat_view_heap_psql | materialized view | regress_display_role | permanent | 0 bytes |
+ tableam_display | tbl_heap | table | regress_display_role | permanent | 0 bytes |
+ tableam_display | tbl_heap_psql | table | regress_display_role | permanent | 0 bytes |
+ tableam_display | view_heap_psql | view | regress_display_role | permanent | 0 bytes |
+(4 rows)
+
+RESET ROLE;
+RESET search_path;
+DROP SCHEMA tableam_display CASCADE;
+NOTICE: drop cascades to 4 other objects
+DETAIL: drop cascades to table tableam_display.tbl_heap_psql
+drop cascades to table tableam_display.tbl_heap
+drop cascades to view tableam_display.view_heap_psql
+drop cascades to materialized view tableam_display.mat_view_heap_psql
DROP ACCESS METHOD heap_psql;
+DROP ROLE regress_display_role;
-- test numericlocale (as best we can without control of psql's locale)
\pset format aligned
\pset expanded off
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 5a16080980..47b28d2a07 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -455,20 +455,38 @@ select 1 where false;
\df exp
\pset tuples_only false
--- check conditional tableam display
+-- check conditional am display
+\pset expanded off
--- Create a heap2 table am handler with heapam handler
+CREATE SCHEMA tableam_display;
+CREATE ROLE regress_display_role;
+ALTER SCHEMA tableam_display OWNER TO regress_display_role;
+SET search_path TO tableam_display;
CREATE ACCESS METHOD heap_psql TYPE TABLE HANDLER heap_tableam_handler;
+SET ROLE TO regress_display_role;
+-- Use only relations with a physical size of zero.
CREATE TABLE tbl_heap_psql(f1 int, f2 char(100)) using heap_psql;
CREATE TABLE tbl_heap(f1 int, f2 char(100)) using heap;
+CREATE VIEW view_heap_psql AS SELECT f1 from tbl_heap_psql;
+CREATE MATERIALIZED VIEW mat_view_heap_psql USING heap_psql AS SELECT f1 from tbl_heap_psql;
\d+ tbl_heap_psql
\d+ tbl_heap
\set HIDE_TABLEAM off
\d+ tbl_heap_psql
\d+ tbl_heap
+-- AM is displayed for tables, indexes and materialized views.
+\d+
+\dt+
+\dm+
+-- But not for views and sequences.
+\dv+
\set HIDE_TABLEAM on
-DROP TABLE tbl_heap, tbl_heap_psql;
+\d+
+RESET ROLE;
+RESET search_path;
+DROP SCHEMA tableam_display CASCADE;
DROP ACCESS METHOD heap_psql;
+DROP ROLE regress_display_role;
-- test numericlocale (as best we can without control of psql's locale)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 201946990f..e1e2236a71 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1183,7 +1183,9 @@ testdb=>
columns of the table are shown, as is the presence of OIDs in the
table, the view definition if the relation is a view, a non-default
<link linkend="sql-createtable-replica-identity">replica
- identity</link> setting.
+ identity</link> setting and the
+ <link linkend="sql-create-access-method">access method</link> name
+ if the relation has an access method.
</para>
<para>
signature.asc
Description: PGP signature
