Subject: Re: ALTER TABLE SET ACCESS METHOD on partitioned tables

From: Justin Pryzby <[email-masked]>
Date: Tue, 21 May 2024 08:33:51 -0500

It occurred to me that psql \dP+ should show the AM of partitioned
tables (and other partitioned rels).
Arguably, this could've been done when \dP was introduced in v12, but
at that point would've shown the AM only for partitioned indexes.
But it makes a lot of sense to do it now that partitioned tables support
AMs.  I suggest to consider this for v17.

regression=# \dP+
                                              List of partitioned relations
 Schema |         Name         |  Owner  |       Type        |     Table      | Access method | Total size | Description
--------+----------------------+---------+-------------------+----------------+---------------+------------+-------------
 public | mlparted             | pryzbyj | partitioned table |                | heap2         | 104 kB     |
 public | tableam_parted_heap2 | pryzbyj | partitioned table |                |               | 32 kB      |
 public | trigger_parted       | pryzbyj | partitioned table |                |               | 0 bytes    |
 public | upsert_test          | pryzbyj | partitioned table |                |               | 8192 bytes |
 public | trigger_parted_pkey  | pryzbyj | partitioned index | trigger_parted | btree         | 16 kB      |
 public | upsert_test_pkey     | pryzbyj | partitioned index | upsert_test    | btree         | 8192 bytes |
---
 src/bin/psql/describe.c | 13 ++++++++++++-
 1 file changed, 12 insertions(+), 1 deletion(-)

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index f67bf0b8925..22a668409e7 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4113,7 +4113,7 @@ listPartitionedTables(const char *reltypes, const char *pattern, bool verbose)
 	PQExpBufferData title;
 	PGresult   *res;
 	printQueryOpt myopt = pset.popt;
-	bool		translate_columns[] = {false, false, false, false, false, false, false, false, false};
+	bool		translate_columns[] = {false, false, false, false, false, false, false, false, false, false};
 	const char *tabletitle;
 	bool		mixed_output = false;
 
@@ -4181,6 +4181,14 @@ listPartitionedTables(const char *reltypes, const char *pattern, bool verbose)
 
 	if (verbose)
 	{
+		/*
+		 * Table access methods were introduced in v12, and can be set on
+		 * partitioned tables since v17.
+		 */
+		appendPQExpBuffer(&buf,
+						  ",\n  am.amname as \"%s\"",
+						  gettext_noop("Access method"));
+
 		if (showNested)
 		{
 			appendPQExpBuffer(&buf,
@@ -4216,6 +4224,9 @@ listPartitionedTables(const char *reltypes, const char *pattern, bool verbose)
 
 	if (verbose)
 	{
+		appendPQExpBufferStr(&buf,
+							 "\n     LEFT JOIN pg_catalog.pg_am am ON c.relam = am.oid");
+
 		if (pset.sversion < 120000)
 		{
 			appendPQExpBufferStr(&buf,
-- 
2.42.0