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

From: Justin Pryzby <[email-masked]>
Date: Mon, 27 Mar 2023 23:34:35 -0500

On Tue, Mar 28, 2023 at 09:13:10AM +0900, Michael Paquier wrote:
> On Mon, Mar 20, 2023 at 09:30:50AM +0900, Michael Paquier wrote:
> > Did you check dump and restore flows with partition
> > trees and --no-table-access-method?  Perhaps there should be
> > some regression tests with partitioned tables?
> 
> I was looking at the patch, and as I suspected the dumps generated
> are forgetting to apply the AM to the partitioned tables.

The patch said:

+       else if (RELKIND_HAS_TABLE_AM(relkind) || relkind == RELKIND_PARTITIONED_TABLE)

pg_dump was missing a similar change that's conditional on
RELKIND_HAS_TABLE_AM().  It looks like those are the only two places
that need be be specially handled.

I dug up my latest patch from 2021 and incorporated the changes from the
0001 patch here, and added a test case.

I realized that one difference with tablespaces is that, as written,
partitioned tables will *always* have an AM specified,  and partitions
will never use default_table_access_method.  Is that what's intended ?

Or do we need logic similar tablespaces, such that the relam of a
partitioned table is set only if it differs from default_table_am ?

-- 
Justin
>From 2c0c5068cf849ad91de8f5276a430b022d1243b0 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email-masked]>
Date: Sun, 7 Mar 2021 00:11:38 -0600
Subject: [PATCH] Allow specifying access method of partitioned tables..

..to be inherited by partitions

See also:
ca4103025dfe26eaaf6a500dec9170fbb176eebc
8586bf7ed8889f39a59dd99b292014b73be85342

ebfe2dbd6b624e2a428e14b7ee9322cc096f63f7 - prevent DROP AM

Authors: Justin Pryzby, Soumyadeep Chakraborty
---
 doc/src/sgml/ref/create_table.sgml      |  9 ++-
 src/backend/catalog/heap.c              |  3 +-
 src/backend/commands/tablecmds.c        | 87 +++++++++++++++++++------
 src/backend/utils/cache/lsyscache.c     | 22 +++++++
 src/backend/utils/cache/relcache.c      |  4 ++
 src/bin/pg_dump/pg_dump.c               |  3 +-
 src/bin/pg_dump/t/002_pg_dump.pl        | 16 ++++-
 src/include/utils/lsyscache.h           |  1 +
 src/test/regress/expected/create_am.out | 39 +++++++----
 src/test/regress/sql/create_am.sql      | 16 +++--
 10 files changed, 158 insertions(+), 42 deletions(-)

diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 10ef699fab9..b45632619e5 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1295,9 +1295,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
       This optional clause specifies the table access method to use to store
       the contents for the new table; the method needs be an access method of
       type <literal>TABLE</literal>. See <xref linkend="tableam"/> for more
-      information.  If this option is not specified, the default table access
-      method is chosen for the new table. See <xref
-      linkend="guc-default-table-access-method"/> for more information.
+      information.  If this option is not specified, a default table access
+      method is chosen for the new table.
+      When creating a partitioned table, the default table access method
+      is the access method of the parent.
+      For other tables, the default is determined by
+      <xref linkend="guc-default-table-access-method"/>.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 4f006820b85..146d8b36b09 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -1452,7 +1452,8 @@ heap_create_with_catalog(const char *relname,
 		 * No need to add an explicit dependency for the toast table, as the
 		 * main table depends on it.
 		 */
-		if (RELKIND_HAS_TABLE_AM(relkind) && relkind != RELKIND_TOASTVALUE)
+		if ((RELKIND_HAS_TABLE_AM(relkind) && relkind != RELKIND_TOASTVALUE) ||
+				relkind == RELKIND_PARTITIONED_TABLE)
 		{
 			ObjectAddressSet(referenced, AccessMethodRelationId, accessmtd);
 			add_exact_object_address(&referenced, addrs);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index c510a01fd8d..de1fe15206a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -571,6 +571,7 @@ static ObjectAddress ATExecClusterOn(Relation rel, const char *indexName,
 									 LOCKMODE lockmode);
 static void ATExecDropCluster(Relation rel, LOCKMODE lockmode);
 static void ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname);
+static void ATExecSetAccessMethodNoStorage(Relation rel, Oid newAccessMethod);
 static bool ATPrepChangePersistence(Relation rel, bool toLogged);
 static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
 								const char *tablespacename, LOCKMODE lockmode);
@@ -680,7 +681,6 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 	Oid			ofTypeId;
 	ObjectAddress address;
 	LOCKMODE	parentLockmode;
-	const char *accessMethod = NULL;
 	Oid			accessMethodId = InvalidOid;
 
 	/*
@@ -945,20 +945,20 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 	 * a type of relation that needs one, use the default.
 	 */
 	if (stmt->accessMethod != NULL)
+		accessMethodId = get_table_am_oid(stmt->accessMethod, false);
+	else if (stmt->partbound && relkind == RELKIND_RELATION)
 	{
-		accessMethod = stmt->accessMethod;
-
-		if (partitioned)
-			ereport(ERROR,
-					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-					 errmsg("specifying a table access method is not supported on a partitioned table")));
+		/*
+		 * For partitions, if no access method is specified, default to the AM
+		 * of the parent table.
+		 */
+		Assert(list_length(inheritOids) == 1);
+		accessMethodId = get_rel_relam(linitial_oid(inheritOids));
+		if (!OidIsValid(accessMethodId))
+			accessMethodId = get_table_am_oid(default_table_access_method, false);
 	}
-	else if (RELKIND_HAS_TABLE_AM(relkind))
-		accessMethod = default_table_access_method;
-
-	/* look up the access method, verify it is for a table */
-	if (accessMethod != NULL)
-		accessMethodId = get_table_am_oid(accessMethod, false);
+	else if (RELKIND_HAS_TABLE_AM(relkind) || relkind == RELKIND_PARTITIONED_TABLE)
+		accessMethodId = get_table_am_oid(default_table_access_method, false);
 
 	/*
 	 * Create the relation.  Inherited defaults and constraints are passed in
@@ -4779,12 +4779,6 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
 		case AT_SetAccessMethod:	/* SET ACCESS METHOD */
 			ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_MATVIEW);
 
-			/* partitioned tables don't have an access method */
-			if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-				ereport(ERROR,
-						(errcode(ERRCODE_WRONG_OBJECT_TYPE),
-						 errmsg("cannot change access method of a partitioned table")));
-
 			/* check if another access method change was already requested */
 			if (OidIsValid(tab->newAccessMethod))
 				ereport(ERROR,
@@ -5138,6 +5132,13 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
 			break;
 		case AT_SetAccessMethod:	/* SET ACCESS METHOD */
 			/* handled specially in Phase 3 */
+
+			/*
+			 * Only do this for partitioned tables, for which this is just a
+			 * catalog change.  Tables with storage are handled by Phase 3.
+			 */
+			if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+				ATExecSetAccessMethodNoStorage(rel, tab->newAccessMethod);
 			break;
 		case AT_SetTableSpace:	/* SET TABLESPACE */
 
@@ -14521,6 +14522,54 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
 	list_free(reltoastidxids);
 }
 
+/*
+ * Special handling of ALTER TABLE SET ACCESS METHOD for relations with no
+ * storage that have an interest in preserving AM.
+ *
+ * Since these have no storage, setting the access method is a catalog only
+ * operation.
+ */
+static void
+ATExecSetAccessMethodNoStorage(Relation rel, Oid newAccessMethod)
+{
+	Relation	pg_class;
+	Oid			relid;
+	Oid			oldrelam;
+	HeapTuple	tuple;
+
+	/*
+	 * Shouldn't be called on relations having storage; these are processed in
+	 * phase 3.
+	 */
+	Assert(!RELKIND_HAS_STORAGE(rel->rd_rel->relkind));
+
+	relid = RelationGetRelid(rel);
+
+	/* Pull the record for this relation and update it */
+	pg_class = table_open(RelationRelationId, RowExclusiveLock);
+
+	tuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid));
+
+	if (!HeapTupleIsValid(tuple))
+		elog(ERROR, "cache lookup failed for relation %u", relid);
+
+	oldrelam = ((Form_pg_class) GETSTRUCT(tuple))->relam;
+	((Form_pg_class) GETSTRUCT(tuple))->relam = newAccessMethod;
+	CatalogTupleUpdate(pg_class, &tuple->t_self, tuple);
+
+	/* Update dependency on new AM */
+	changeDependencyFor(RelationRelationId, relid, AccessMethodRelationId,
+						oldrelam, newAccessMethod);
+
+	InvokeObjectPostAlterHook(RelationRelationId, RelationGetRelid(rel), 0);
+
+	heap_freetuple(tuple);
+	table_close(pg_class, RowExclusiveLock);
+
+	/* Make sure the relam change is visible */
+	CommandCounterIncrement();
+}
+
 /*
  * Special handling of ALTER TABLE SET TABLESPACE for relations with no
  * storage that have an interest in preserving tablespace.
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index c7607895cdd..39a97cf5d4c 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -2073,6 +2073,28 @@ get_rel_persistence(Oid relid)
 	return result;
 }
 
+/*
+ * get_rel_relam
+ *
+ *		Returns the relam associated with a given relation.
+ */
+Oid
+get_rel_relam(Oid relid)
+{
+	HeapTuple	tp;
+	Form_pg_class reltup;
+	Oid			result;
+
+	tp = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+	if (!HeapTupleIsValid(tp))
+		elog(ERROR, "cache lookup failed for relation %u", relid);
+	reltup = (Form_pg_class) GETSTRUCT(tp);
+	result = reltup->relam;
+	ReleaseSysCache(tp);
+
+	return result;
+}
+
 
 /*				---------- TRANSFORM CACHE ----------						 */
 
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 40140de9589..08d5f315c87 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -1207,6 +1207,10 @@ retry:
 	else if (RELKIND_HAS_TABLE_AM(relation->rd_rel->relkind) ||
 			 relation->rd_rel->relkind == RELKIND_SEQUENCE)
 		RelationInitTableAccessMethod(relation);
+	else if (relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+	{
+		/* Do nothing: it's a catalog settings for partitions to inherit */
+	}
 	else
 		Assert(relation->rd_rel->relam == InvalidOid);
 
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index d62780a0880..c0dd029e36d 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -16111,7 +16111,8 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
 		if (RELKIND_HAS_TABLESPACE(tbinfo->relkind))
 			tablespace = tbinfo->reltablespace;
 
-		if (RELKIND_HAS_TABLE_AM(tbinfo->relkind))
+		if (RELKIND_HAS_TABLE_AM(tbinfo->relkind) ||
+			tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
 			tableam = tbinfo->amname;
 
 		ArchiveEntry(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId,
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index a22f27f300f..f5e3544301a 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -4357,13 +4357,25 @@ my %tests = (
 		create_sql   => '
 			CREATE TABLE dump_test.regress_pg_dump_table_am_0() USING heap;
 			CREATE TABLE dump_test.regress_pg_dump_table_am_1 (col1 int) USING regress_table_am;
-			CREATE TABLE dump_test.regress_pg_dump_table_am_2() USING heap;',
+			CREATE TABLE dump_test.regress_pg_dump_table_am_2() USING heap;
+			CREATE TABLE dump_test.regress_pg_dump_table_am_parent (id int) PARTITION BY RANGE (id) USING regress_table_am;
+			CREATE TABLE dump_test.regress_pg_dump_table_am_child PARTITION OF dump_test.regress_pg_dump_table_am_parent DEFAULT',
 		regexp => qr/^
 			\QSET default_table_access_method = regress_table_am;\E
 			(\n(?!SET[^;]+;)[^\n]*)*
 			\n\QCREATE TABLE dump_test.regress_pg_dump_table_am_1 (\E
 			\n\s+\Qcol1 integer\E
-			\n\);/xm,
+			\n\);
+			(.*\n)*
+			\QSET default_table_access_method = heap;\E
+			(\n(?!SET[^;]+;)[^\n]*)*
+			\n\QCREATE TABLE dump_test.regress_pg_dump_table_am_2 (\E
+			(.*\n)*
+			\QSET default_table_access_method = regress_table_am;\E
+			(\n(?!SET[^;]+;)[^\n]*)*
+			\n\QCREATE TABLE dump_test.regress_pg_dump_table_am_parent (\E
+			(\n(?!SET[^;]+;)[^\n]*)*
+			\n\QCREATE TABLE dump_test.regress_pg_dump_table_am_child (\E/xm,
 		like => {
 			%full_runs, %dump_test_schema_runs, section_pre_data => 1,
 		},
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 4f5418b9728..9f26357d373 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -139,6 +139,7 @@ extern char get_rel_relkind(Oid relid);
 extern bool get_rel_relispartition(Oid relid);
 extern Oid	get_rel_tablespace(Oid relid);
 extern char get_rel_persistence(Oid relid);
+extern Oid	get_rel_relam(Oid relid);
 extern Oid	get_transform_fromsql(Oid typid, Oid langid, List *trftypes);
 extern Oid	get_transform_tosql(Oid typid, Oid langid, List *trftypes);
 extern bool get_typisdefined(Oid typid);
diff --git a/src/test/regress/expected/create_am.out b/src/test/regress/expected/create_am.out
index e9a9283d7ab..c91c5abe9f9 100644
--- a/src/test/regress/expected/create_am.out
+++ b/src/test/regress/expected/create_am.out
@@ -176,11 +176,9 @@ SELECT f1 FROM tableam_tblmv_heap2 ORDER BY f1;
   1
 (1 row)
 
--- CREATE TABLE ..  PARTITION BY doesn't not support USING
+-- CREATE TABLE ..  PARTITION BY supports USING
+-- new partitions will inherit the AM from their parent table
 CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2;
-ERROR:  specifying a table access method is not supported on a partitioned table
-CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a);
--- new partitions will inherit from the current default, rather the partition root
 SET default_table_access_method = 'heap';
 CREATE TABLE tableam_parted_a_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('a');
 SET default_table_access_method = 'heap2';
@@ -205,14 +203,17 @@ WHERE pa.oid = pc.relam
 ORDER BY 3, 1, 2;
  relkind | amname |             relname              
 ---------+--------+----------------------------------
+ r       | heap2  | tableam_parted_a_heap2
  r       | heap2  | tableam_parted_b_heap2
  r       | heap2  | tableam_parted_d_heap2
+ p       | heap2  | tableam_parted_heap2
  r       | heap2  | tableam_tbl_heap2
  r       | heap2  | tableam_tblas_heap2
  m       | heap2  | tableam_tblmv_heap2
+ t       | heap2  | toast for tableam_parted_a_heap2
  t       | heap2  | toast for tableam_parted_b_heap2
  t       | heap2  | toast for tableam_parted_d_heap2
-(7 rows)
+(10 rows)
 
 -- Show dependencies onto AM - there shouldn't be any for toast
 SELECT pg_describe_object(classid,objid,objsubid) AS obj
@@ -226,9 +227,11 @@ ORDER BY classid, objid, objsubid;
  table tableam_tbl_heap2
  table tableam_tblas_heap2
  materialized view tableam_tblmv_heap2
+ table tableam_parted_heap2
+ table tableam_parted_a_heap2
  table tableam_parted_b_heap2
  table tableam_parted_d_heap2
-(5 rows)
+(7 rows)
 
 -- ALTER TABLE SET ACCESS METHOD
 CREATE TABLE heaptable USING heap AS
@@ -284,11 +287,26 @@ ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
 ERROR:  cannot have multiple SET ACCESS METHOD subcommands
 DROP MATERIALIZED VIEW heapmv;
 DROP TABLE heaptable;
--- No support for partitioned tables.
+-- partition hierarchies
+-- upon ALTER, new children will inherit the new am, whereas the existing
+-- children will remain untouched
 CREATE TABLE am_partitioned(x INT, y INT)
   PARTITION BY hash (x);
+CREATE TABLE am_partitioned_1 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 3,REMAINDER 0);
+CREATE TABLE am_partitioned_2 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 3,REMAINDER 1);
+ALTER TABLE am_partitioned_1 SET ACCESS METHOD heap2;
 ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
-ERROR:  cannot change access method of a partitioned table
+CREATE TABLE am_partitioned_3 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 3,REMAINDER 2);
+SELECT relname, amname FROM pg_class c, pg_am am
+  WHERE c.relam = am.oid AND c.relname LIKE 'am_partitioned%' ORDER BY 1;
+     relname      | amname 
+------------------+--------
+ am_partitioned   | heap2
+ am_partitioned_1 | heap2
+ am_partitioned_2 | heap
+ am_partitioned_3 | heap2
+(4 rows)
+
 DROP TABLE am_partitioned;
 -- Second, create objects in the new AM by changing the default AM
 BEGIN;
@@ -326,7 +344,7 @@ ORDER BY 3, 1, 2;
  f       |        | tableam_fdw_heapx
  r       | heap2  | tableam_parted_1_heapx
  r       | heap   | tableam_parted_2_heapx
- p       |        | tableam_parted_heapx
+ p       | heap2  | tableam_parted_heapx
  S       |        | tableam_seq_heapx
  r       | heap2  | tableam_tbl_heapx
  r       | heap2  | tableam_tblas_heapx
@@ -355,7 +373,6 @@ ERROR:  cannot drop access method heap2 because other objects depend on it
 DETAIL:  table tableam_tbl_heap2 depends on access method heap2
 table tableam_tblas_heap2 depends on access method heap2
 materialized view tableam_tblmv_heap2 depends on access method heap2
-table tableam_parted_b_heap2 depends on access method heap2
-table tableam_parted_d_heap2 depends on access method heap2
+table tableam_parted_heap2 depends on access method heap2
 HINT:  Use DROP ... CASCADE to drop the dependent objects too.
 -- we intentionally leave the objects created above alive, to verify pg_dump support
diff --git a/src/test/regress/sql/create_am.sql b/src/test/regress/sql/create_am.sql
index 256884c9592..1e017245daf 100644
--- a/src/test/regress/sql/create_am.sql
+++ b/src/test/regress/sql/create_am.sql
@@ -124,11 +124,9 @@ CREATE SEQUENCE tableam_seq_heap2 USING heap2;
 CREATE MATERIALIZED VIEW tableam_tblmv_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2;
 SELECT f1 FROM tableam_tblmv_heap2 ORDER BY f1;
 
--- CREATE TABLE ..  PARTITION BY doesn't not support USING
+-- CREATE TABLE ..  PARTITION BY supports USING
+-- new partitions will inherit the AM from their parent table
 CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2;
-
-CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a);
--- new partitions will inherit from the current default, rather the partition root
 SET default_table_access_method = 'heap';
 CREATE TABLE tableam_parted_a_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('a');
 SET default_table_access_method = 'heap2';
@@ -183,10 +181,18 @@ ALTER TABLE heaptable SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
 ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
 DROP MATERIALIZED VIEW heapmv;
 DROP TABLE heaptable;
--- No support for partitioned tables.
+-- partition hierarchies
+-- upon ALTER, new children will inherit the new am, whereas the existing
+-- children will remain untouched
 CREATE TABLE am_partitioned(x INT, y INT)
   PARTITION BY hash (x);
+CREATE TABLE am_partitioned_1 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 3,REMAINDER 0);
+CREATE TABLE am_partitioned_2 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 3,REMAINDER 1);
+ALTER TABLE am_partitioned_1 SET ACCESS METHOD heap2;
 ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
+CREATE TABLE am_partitioned_3 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 3,REMAINDER 2);
+SELECT relname, amname FROM pg_class c, pg_am am
+  WHERE c.relam = am.oid AND c.relname LIKE 'am_partitioned%' ORDER BY 1;
 DROP TABLE am_partitioned;
 
 -- Second, create objects in the new AM by changing the default AM
-- 
2.34.1

---

From: Michael Paquier <[email-masked]>
Date: Tue, 28 Mar 2023 14:56:28 +0900

> partitioned table is set only if it differs from default_table_am ?

Hmm.  This is a good point.  It is true that the patch feels
incomplete on this side.  I don't see why we could not be flexible,
and allow a value of 0 in a partitioned table's relam to mean that we
would pick up the database default in this case when a partition is
is created on it.  This would have the advantage to be consistent with
older versions where we fallback on the default.  We cannot be
completely consistent with the reltablespace of the leaf partitions
unfortunately, as relam should always be set if a relation has
storage.  And allowing a value of 0 means that there are likely other
tricky cases with dumps?

Another thing: would it make sense to allow an empty string in
default_table_access_method so as we'd always fallback to a database
default?
--
Michael

---

From: Michael Paquier <[email-masked]>
Date: Wed, 29 Mar 2023 09:18:50 +0900

> default_table_access_method so as we'd always fallback to a database

FYI, I am not sure that I will be able to look more at this patch by
the end of the commit fest, and there are quite more points to
consider.  Perhaps at this stage we'd better mark it as returned with
feedback?  I understand that I've arrived late at this party :/
--
Michael

---

From: Justin Pryzby <[email-masked]>
Date: Thu, 30 Mar 2023 00:07:58 -0500

> partitioned table is set only if it differs from default_table_am ?

Actually .. I think it'd be a mistake if the relam needed to be
interpretted differently for partitioned tables than other relkinds.

I updated the patch to allow intermediate partitioned tables to inherit
relam from their parent.

Michael wrote:
> .. and there are quite more points to consider.

What more points ?  There's nothing that's been raised here.  In fact,
your message last week is the first comment since last June ..

-- 
Justin
>From a9f357d98b90df9ee0ec5f9f9f1ac083d653daaa Mon Sep 17 00:00:00 2001

See also:

Authors: Justin Pryzby, Soumyadeep Chakraborty
---
 doc/src/sgml/ref/create_table.sgml      |  9 ++-
 src/backend/catalog/heap.c              |  3 +-
 src/backend/commands/tablecmds.c        | 88 +++++++++++++++++++------
 src/backend/utils/cache/lsyscache.c     | 22 +++++++
 src/backend/utils/cache/relcache.c      |  4 ++
 src/bin/pg_dump/pg_dump.c               |  3 +-
 src/bin/pg_dump/t/002_pg_dump.pl        | 16 ++++-
 src/include/utils/lsyscache.h           |  1 +
 src/test/regress/expected/create_am.out | 45 +++++++++----
 src/test/regress/sql/create_am.sql      | 18 +++--
 10 files changed, 167 insertions(+), 42 deletions(-)

index 10ef699fab9..b20d272b151 100644
+      When creating a partition, the default table access method is the
+      access method of its parent.
index 2a0d82aedd7..bbf8e08618b 100644
index 3147dddf286..71b8b0b2317 100644
 
 	/*
@@ -945,20 +945,21 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
+	else if (stmt->partbound &&
+			(RELKIND_HAS_TABLE_AM(relkind) || relkind == RELKIND_PARTITIONED_TABLE))
 
 	/*
 	 * Create the relation.  Inherited defaults and constraints are passed in
@@ -4787,12 +4788,6 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
 		case AT_SetAccessMethod:	/* SET ACCESS METHOD */
 			ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_MATVIEW);
 
@@ -5146,6 +5141,13 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
 
@@ -14541,6 +14543,54 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
 	list_free(reltoastidxids);
 }
 
+/*
 
+/*
+ * get_rel_relam
+ *

 

index 42215f82f7a..f1efff96d23 100644
@@ -4380,13 +4380,25 @@ my %tests = (
index e9a9283d7ab..34d86eacf65 100644
@@ -176,15 +176,15 @@ SELECT f1 FROM tableam_tblmv_heap2 ORDER BY f1;
   1
 (1 row)
 
--- CREATE TABLE ..  PARTITION BY doesn't not support USING
+-- CREATE TABLE ..  PARTITION BY supports USING
+-- new partitions will inherit the AM from their parent table
 CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2;
-ERROR:  specifying a table access method is not supported on a partitioned table
-CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a);
--- new partitions will inherit from the current default, rather the partition root
 SET default_table_access_method = 'heap';
 CREATE TABLE tableam_parted_a_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('a');
+CREATE TABLE tableam_parted_aa_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('aa') PARTITION BY LIST (a);
 SET default_table_access_method = 'heap2';
 CREATE TABLE tableam_parted_b_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('b');
+CREATE TABLE tableam_parted_bb_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('bb') PARTITION BY LIST (a);
 RESET default_table_access_method;
 -- but the method can be explicitly specified
 CREATE TABLE tableam_parted_c_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('c') USING heap;
@@ -205,14 +205,19 @@ WHERE pa.oid = pc.relam
 ORDER BY 3, 1, 2;
  relkind | amname |             relname              
 ---------+--------+----------------------------------
+ r       | heap2  | tableam_parted_a_heap2
+ p       | heap2  | tableam_parted_aa_heap2
  r       | heap2  | tableam_parted_b_heap2
+ p       | heap2  | tableam_parted_bb_heap2
  r       | heap2  | tableam_parted_d_heap2
+ p       | heap2  | tableam_parted_heap2
  r       | heap2  | tableam_tbl_heap2
  r       | heap2  | tableam_tblas_heap2
  m       | heap2  | tableam_tblmv_heap2
+ t       | heap2  | toast for tableam_parted_a_heap2
  t       | heap2  | toast for tableam_parted_b_heap2
  t       | heap2  | toast for tableam_parted_d_heap2
-(7 rows)
+(12 rows)
 
 -- Show dependencies onto AM - there shouldn't be any for toast
 SELECT pg_describe_object(classid,objid,objsubid) AS obj
@@ -226,9 +231,13 @@ ORDER BY classid, objid, objsubid;
  table tableam_tbl_heap2
  table tableam_tblas_heap2
  materialized view tableam_tblmv_heap2
+ table tableam_parted_heap2
+ table tableam_parted_a_heap2
+ table tableam_parted_aa_heap2
  table tableam_parted_b_heap2
+ table tableam_parted_bb_heap2
  table tableam_parted_d_heap2
-(5 rows)
+(9 rows)
 
@@ -284,11 +293,26 @@ ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
@@ -326,7 +350,7 @@ ORDER BY 3, 1, 2;
@@ -355,7 +379,6 @@ ERROR:  cannot drop access method heap2 because other objects depend on it
index 256884c9592..73cef0da4c8 100644
@@ -124,15 +124,15 @@ CREATE SEQUENCE tableam_seq_heap2 USING heap2;
 
--- CREATE TABLE ..  PARTITION BY doesn't not support USING
+-- CREATE TABLE ..  PARTITION BY supports USING
+-- new partitions will inherit the AM from their parent table
 CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2;
-
-CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a);
--- new partitions will inherit from the current default, rather the partition root
 SET default_table_access_method = 'heap';
 CREATE TABLE tableam_parted_a_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('a');
+CREATE TABLE tableam_parted_aa_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('aa') PARTITION BY LIST (a);
 SET default_table_access_method = 'heap2';
 CREATE TABLE tableam_parted_b_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('b');
+CREATE TABLE tableam_parted_bb_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('bb') PARTITION BY LIST (a);
 RESET default_table_access_method;
 -- but the method can be explicitly specified
 CREATE TABLE tableam_parted_c_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('c') USING heap;
@@ -183,10 +183,18 @@ ALTER TABLE heaptable SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
 ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
 DROP MATERIALIZED VIEW heapmv;
 DROP TABLE heaptable;
--- No support for partitioned tables.
+-- partition hierarchies
+-- upon ALTER, new children will inherit the new am, whereas the existing
+-- children will remain untouched
 CREATE TABLE am_partitioned(x INT, y INT)
   PARTITION BY hash (x);
+CREATE TABLE am_partitioned_1 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 3,REMAINDER 0);
+CREATE TABLE am_partitioned_2 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 3,REMAINDER 1);
+ALTER TABLE am_partitioned_1 SET ACCESS METHOD heap2;
 ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
+CREATE TABLE am_partitioned_3 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 3,REMAINDER 2);
+SELECT relname, amname FROM pg_class c, pg_am am
+  WHERE c.relam = am.oid AND c.relname LIKE 'am_partitioned%' ORDER BY 1;
 DROP TABLE am_partitioned;

---

From: Justin Pryzby <[email-masked]>
Date: Mon, 24 Apr 2023 19:18:54 -0500

> your message last week is the first comment since last June ..

Michael ?

---

From: Michael Paquier <[email-masked]>
Date: Tue, 25 Apr 2023 14:17:58 +0900

> your message last week is the first comment since last June ..

When I wrote this message, I felt like this may still be missing
something in the area of dump/restore.  Perhaps my feeling on the
matter is wrong, so consider this as a self-reminder not to be taken
seriously until I can have a closer look at what's proposed here for
v17.  :p
--
Michael

---

From: Michael Paquier <[email-masked]>
Date: Thu, 25 May 2023 15:49:12 +0900

>> I updated the patch to allow intermediate partitioned tables to inherit

Sorry for dropping the subject for so long.  I have spent some time
looking at the patch.  Here are a few comments.

pg_class.h includes the following:

/*
 * Relation kinds that support tablespaces: All relation kinds with storage
 * support tablespaces, except that we don't support moving sequences around
 * into different tablespaces.  Partitioned tables and indexes don't have
 * physical storage, but they have a tablespace settings so that their
 * children can inherit it.
 */
#define RELKIND_HAS_TABLESPACE(relkind) \
    ((RELKIND_HAS_STORAGE(relkind) || RELKIND_HAS_PARTITIONS(relkind)) \
     && (relkind) != RELKIND_SEQUENCE)
[...]
/*
 * Relation kinds with a table access method (rd_tableam).  Although sequences
 * use the heap table AM, they are enough of a special case in most uses that
 * they are not included here.
 */
#define RELKIND_HAS_TABLE_AM(relkind) \
    ((relkind) == RELKIND_RELATION || \
     (relkind) == RELKIND_TOASTVALUE || \
     (relkind) == RELKIND_MATVIEW)

It would look much more consistent with the tablespace case if we
included partitioned tables in this case, but this refers to
rd_tableam for the relcache which we surely don't want to fill for
partitioned tables.  I guess that at minimum a comment is in order?
RELKIND_HAS_TABLE_AM() is much more spread than
RELKIND_HAS_TABLESPACE().

The comment at the top of this code block needs an update.

    else if (stmt->partbound &&

This structure seems a bit weird.  Could it be cleaner to group the
second and third blocks together?  I would imagine:
if (accessMethod != NULL)
{
    //Extract the AM defined in the statement
}
else
{
    //This is a relkind that can use a default table AM.
    if (RELKIND_HAS_TABLE_AM(relkind) || relkind == RELKIND_PARTITIONED_TABLE)
    {
        if (stmt->partbound)
	{
	    //This is a partition, so look at what its partitioned
	    //table holds.
	}
	else
	{
	    //No partition, grab the default.
	}
    }
}

+           /*

Okay, there is a parallel with tablespaces in this logic.

Speaking of which, ATExecSetAccessMethodNoStorage() does a catalog
update even if ALTER TABLE is defined to use the same table AM as what
is currently set.  There is no need to update the relation's pg_class
entry in this case.  Be careful that InvokeObjectPostAlterHook() still
needs to be checked in this case.  Perhaps some tests should be added
in test_oat_hooks.sql?  It would be tempted to add a new SQL file for
that.

+   else if (relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+   {
+       /* Do nothing: it's a catalog settings for partitions to inherit */
+   }
Actually, we could add an assertion telling that rd_rel->relam will
always be valid.

-       if (RELKIND_HAS_TABLE_AM(tbinfo->relkind))
+       if (RELKIND_HAS_TABLE_AM(tbinfo->relkind) ||
+           tbinfo->relkind == RELKIND_PARTITIONED_TABLE)
            tableam = tbinfo->amname;
I have spent some time pondering on this particular change, concluding
that it should be OK.  It passes my tests, as well.

Hmm.  I think that we should rewrite a bit this test rather than just
adding contents on top of it.  There is also an extra test I would be
interesting in here: a partition tree with 2 levels of depth, an ALTER
TABLE SET ACCESS METHOD run at level 1 on a partitioned table, and
some new partitions attached to it to check that the new partitions
inherit from the level 1 partitioned table, not the top-parent.

alter_table.sgml should be updated to explain what happens when SET
ACCESS METHOD is applied on a partitioned table.  See for example SET
TABLESPACE that explains what happens to partitions created
afterwards, telling that there is no rewrite in this case.

The regression test added to check pg_dump with a partition tree and
the two table AMs was mixed with an existing one, but it seems to me
that it should be independent of the rest?  I have tweaked that as in
the attached, on the way, using one partition that relies on the
default defined by the parent, and a second that has a USING clause on
heap.  I did not touch the rest of the code.
--
Michael
From aa5cba948f848a3add4b010cf7c691f4895bfddf Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Thu, 25 May 2023 15:45:56 +0900
Subject: [PATCH v2]  Allow specifying access method of partitioned tables..

See also:

 src/bin/pg_dump/t/002_pg_dump.pl        | 34 ++++++++++
 10 files changed, 187 insertions(+), 40 deletions(-)

index 4f5418b972..9f26357d37 100644
index 2a0d82aedd..bbf8e08618 100644
index 4d49d70c33..3f94184ca5 100644
@@ -572,6 +572,7 @@ static ObjectAddress ATExecClusterOn(Relation rel, const char *indexName,
@@ -681,7 +682,6 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 
 	/*
@@ -946,20 +946,21 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 
 	/*
 	 * Create the relation.  Inherited defaults and constraints are passed in
@@ -4803,12 +4804,6 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
 		case AT_SetAccessMethod:	/* SET ACCESS METHOD */
 			ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_MATVIEW);
 
@@ -5162,6 +5157,13 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
 
@@ -14568,6 +14570,54 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
 	list_free(reltoastidxids);
 }
 
+/*
index 60978f9415..c1e3cfb015 100644
 
+/*
+ * get_rel_relam
+ *

index 8a08463c2b..74dfc6da40 100644
 
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 3af97a6039..4434528725 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -16110,7 +16110,8 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
 		if (RELKIND_HAS_TABLESPACE(tbinfo->relkind))
 			tablespace = tbinfo->reltablespace;

 		ArchiveEntry(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId,
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 387c5d3afb..24bd6af14a 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -4533,6 +4533,40 @@ my %tests = (
 			no_table_access_method => 1,
 			only_dump_measurement => 1,
 		},
+	},
+
+	# CREATE TABLE with partitioned table and various AMs.  One
+	# partition uses the same default as the parent, and a second
+	# uses its own AM.
+	'CREATE TABLE regress_pg_dump_table_part' => {
+		create_order => 19,
+		create_sql => '
+			CREATE TABLE dump_test.regress_pg_dump_table_am_parent (id int) PARTITION BY LIST (id) USING regress_table_am;
+			CREATE TABLE dump_test.regress_pg_dump_table_am_child_1
+			  PARTITION OF dump_test.regress_pg_dump_table_am_parent FOR VALUES IN (1) USING heap;
+			CREATE TABLE dump_test.regress_pg_dump_table_am_child_2
+			  PARTITION OF dump_test.regress_pg_dump_table_am_parent FOR VALUES IN (2);',
+		regexp => qr/^
+			\QSET default_table_access_method = regress_table_am;\E
+			(\n(?!SET[^;]+;)[^\n]*)*
+			\n\QCREATE TABLE dump_test.regress_pg_dump_table_am_parent (\E
+			(.*\n)*
+			\QSET default_table_access_method = heap;\E
+			(\n(?!SET[^;]+;)[^\n]*)*
+			\n\QCREATE TABLE dump_test.regress_pg_dump_table_am_child_1 (\E
+			(.*\n)*
+			\QSET default_table_access_method = regress_table_am;\E
+			(\n(?!SET[^;]+;)[^\n]*)*
+			\n\QCREATE TABLE dump_test.regress_pg_dump_table_am_child_2 (\E
+			(.*\n)*/xm,
+		like => {
+			%full_runs, %dump_test_schema_runs, section_pre_data => 1,
+		},
+		unlike => {
+			exclude_dump_test_schema => 1,
+			no_table_access_method => 1,
+			only_dump_measurement => 1,
+		},
 	});
 
 #########################################
diff --git a/src/test/regress/expected/create_am.out b/src/test/regress/expected/create_am.out
index e9a9283d7a..34d86eacf6 100644
--- a/src/test/regress/expected/create_am.out
+++ b/src/test/regress/expected/create_am.out
@@ -176,15 +176,15 @@ SELECT f1 FROM tableam_tblmv_heap2 ORDER BY f1;
   1
 (1 row)

 
index 256884c959..73cef0da4c 100644

index 10ef699fab..b20d272b15 100644
2.40.1

---

From: Justin Pryzby <[email-masked]>
Date: Wed, 31 May 2023 18:35:34 -0500

> looking at the patch.  Here are a few comments.

...
> The comment at the top of this code block needs an update.

What do you think the comment ought to say ?  It already says:

src/backend/catalog/heap.c-              * Make a dependency link to force the relation to be deleted if its
src/backend/catalog/heap.c-              * access method is.

> in test_oat_hooks.sql?  It would be tempted to add a new SQL file for

Are you suggesting to put this in a conditional: if oldrelam!=newAccessMethod ?

Why is that desirable ?  I'd prefer to see it written with fewer
conditionals, not more; then, it hits the same path every time.

This ought to address your other comments.

-- 
Justin
>From a726bd7ca8844f6eee639d672afa7edace329caf Mon Sep 17 00:00:00 2001

See also:

Authors: Justin Pryzby, Soumyadeep Chakraborty
---
 doc/src/sgml/ref/alter_table.sgml       |  4 ++
 doc/src/sgml/ref/create_table.sgml      |  9 ++-
 src/backend/catalog/heap.c              |  3 +-
 src/backend/commands/tablecmds.c        | 89 +++++++++++++++++++------
 src/backend/utils/cache/lsyscache.c     | 22 ++++++
 src/backend/utils/cache/relcache.c      |  5 ++
 src/bin/pg_dump/pg_dump.c               |  3 +-
 src/bin/pg_dump/t/002_pg_dump.pl        | 34 ++++++++++
 src/include/catalog/pg_class.h          |  4 +-
 src/include/utils/lsyscache.h           |  1 +
 src/test/regress/expected/create_am.out | 62 ++++++++++++-----
 src/test/regress/sql/create_am.sql      | 25 +++++--
 12 files changed, 212 insertions(+), 49 deletions(-)

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index d4d93eeb7c6..d32d4c44b10 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -719,6 +719,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
      <para>
       This form changes the access method of the table by rewriting it. See
       <xref linkend="tableam"/> for more information.
+      When applied to a partitioned table, there is no data to rewrite, but any
+      partitions created afterwards with
+      <command>CREATE TABLE PARTITION OF</command> will use that access method,
+      unless overridden by an <literal>USING</literal> clause.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 10ef699fab9..b20d272b151 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1295,9 +1295,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
       This optional clause specifies the table access method to use to store
       the contents for the new table; the method needs be an access method of
       type <literal>TABLE</literal>. See <xref linkend="tableam"/> for more
-      information.  If this option is not specified, the default table access
-      method is chosen for the new table. See <xref
-      linkend="guc-default-table-access-method"/> for more information.
+      information.  If this option is not specified, a default table access
+      method is chosen for the new table.
+      When creating a partition, the default table access method is the
+      access method of its parent.
+      For other tables, the default is determined by
+      <xref linkend="guc-default-table-access-method"/>.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 2a0d82aedd7..bbf8e08618b 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -1452,7 +1452,8 @@ heap_create_with_catalog(const char *relname,
 		 * No need to add an explicit dependency for the toast table, as the
 		 * main table depends on it.
 		 */
-		if (RELKIND_HAS_TABLE_AM(relkind) && relkind != RELKIND_TOASTVALUE)
+		if ((RELKIND_HAS_TABLE_AM(relkind) && relkind != RELKIND_TOASTVALUE) ||
+				relkind == RELKIND_PARTITIONED_TABLE)
 		{
 			ObjectAddressSet(referenced, AccessMethodRelationId, accessmtd);
 			add_exact_object_address(&referenced, addrs);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 4d49d70c339..4ac34759e8d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -572,6 +572,7 @@ static ObjectAddress ATExecClusterOn(Relation rel, const char *indexName,
 									 LOCKMODE lockmode);
 static void ATExecDropCluster(Relation rel, LOCKMODE lockmode);
 static void ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname);
+static void ATExecSetAccessMethodNoStorage(Relation rel, Oid newAccessMethod);
 static bool ATPrepChangePersistence(Relation rel, bool toLogged);
 static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
 								const char *tablespacename, LOCKMODE lockmode);
@@ -681,7 +682,6 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 	Oid			ofTypeId;
 	ObjectAddress address;
 	LOCKMODE	parentLockmode;
-	const char *accessMethod = NULL;
 	Oid			accessMethodId = InvalidOid;
 
 	/*
@@ -946,20 +946,22 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
+		if (stmt->partbound)
+			 * For partitions, if no access method is specified, use the AM of
+			 * the parent table.
+			Assert(OidIsValid(accessMethodId));
+		else
 
 	/*
 	 * Create the relation.  Inherited defaults and constraints are passed in
@@ -4803,12 +4805,6 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
 		case AT_SetAccessMethod:	/* SET ACCESS METHOD */
 			ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_MATVIEW);
 
@@ -5162,6 +5158,13 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
 
@@ -14568,6 +14571,54 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
 	list_free(reltoastidxids);
 }
 
+/*
index 60978f9415b..c1e3cfb0151 100644
 
+/*
+ * get_rel_relam
+ *

diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 8a08463c2b7..c00899690eb 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -1207,6 +1207,11 @@ retry:
 	else if (RELKIND_HAS_TABLE_AM(relation->rd_rel->relkind) ||
 			 relation->rd_rel->relkind == RELKIND_SEQUENCE)
 		RelationInitTableAccessMethod(relation);
+	else if (relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+	{
+		/* Do nothing: it's a catalog settings for partitions to inherit */
+		Assert(relation->rd_rel->relam != InvalidOid);
+	}
 	else
 		Assert(relation->rd_rel->relam == InvalidOid);
 
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 1a423f4d117..97855ff449d 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -16101,7 +16101,8 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
 		if (RELKIND_HAS_TABLESPACE(tbinfo->relkind))
 			tablespace = tbinfo->reltablespace;

index 58a1aee807b..e5f791121b0 100644
 
 #########################################
diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h
index 2d1bb7af3a9..92c7c53d620 100644
--- a/src/include/catalog/pg_class.h
+++ b/src/include/catalog/pg_class.h
@@ -216,7 +216,9 @@ DECLARE_INDEX(pg_class_tblspc_relfilenode_index, 3455, ClassTblspcRelfilenodeInd
 /*
  * Relation kinds with a table access method (rd_tableam).  Although sequences
  * use the heap table AM, they are enough of a special case in most uses that
- * they are not included here.
+ * they are not included here.  Partitioned tables don't have physical storage,
+ * but they have an access method set so that their children can inherit it;
+ * however, it's handled specially outside of this macro.
  */
 #define RELKIND_HAS_TABLE_AM(relkind) \
 	((relkind) == RELKIND_RELATION || \
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 4f5418b9728..9f26357d373 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -139,6 +139,7 @@ extern char get_rel_relkind(Oid relid);
 extern bool get_rel_relispartition(Oid relid);
 extern Oid	get_rel_tablespace(Oid relid);
 extern char get_rel_persistence(Oid relid);
+extern Oid	get_rel_relam(Oid relid);
 extern Oid	get_transform_fromsql(Oid typid, Oid langid, List *trftypes);
 extern Oid	get_transform_tosql(Oid typid, Oid langid, List *trftypes);
 extern bool get_typisdefined(Oid typid);
diff --git a/src/test/regress/expected/create_am.out b/src/test/regress/expected/create_am.out
index e9a9283d7ab..6f5dd8e8cd7 100644
--- a/src/test/regress/expected/create_am.out
+++ b/src/test/regress/expected/create_am.out
@@ -176,19 +176,22 @@ SELECT f1 FROM tableam_tblmv_heap2 ORDER BY f1;
   1
 (1 row)
 
--- CREATE TABLE ..  PARTITION BY doesn't not support USING
+-- CREATE TABLE ..  PARTITION BY supports USING
+-- new partitions will inherit the AM from their parent table
 CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2;
-ERROR:  specifying a table access method is not supported on a partitioned table
-CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a);
--- new partitions will inherit from the current default, rather the partition root
 SET default_table_access_method = 'heap';
 CREATE TABLE tableam_parted_a_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('a');
+CREATE TABLE tableam_parted_aa_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('aa') PARTITION BY LIST (a);
 SET default_table_access_method = 'heap2';
 CREATE TABLE tableam_parted_b_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('b');
+CREATE TABLE tableam_parted_bb_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('bb') PARTITION BY LIST (a);
 RESET default_table_access_method;
 -- but the method can be explicitly specified
-CREATE TABLE tableam_parted_c_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('c') USING heap;
-CREATE TABLE tableam_parted_d_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('d') USING heap2;
+CREATE TABLE tableam_parted_c_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('c') PARTITION BY LIST(a) USING heap;
+CREATE TABLE tableam_parted_d_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('d') PARTITION BY LIST(a) USING heap2;
+-- and children inherit from their direct parent
+CREATE TABLE tableam_parted_cc_heap2 PARTITION OF tableam_parted_c_heap2 FOR VALUES IN ('c');
+CREATE TABLE tableam_parted_dd_heap2 PARTITION OF tableam_parted_d_heap2 FOR VALUES IN ('d');
 -- List all objects in AM
 SELECT
     pc.relkind,
@@ -203,16 +206,22 @@ FROM pg_class AS pc,
 WHERE pa.oid = pc.relam
    AND pa.amname = 'heap2'
 ORDER BY 3, 1, 2;
- relkind | amname |             relname              
----------+--------+----------------------------------
+ relkind | amname |              relname              
+---------+--------+-----------------------------------
+ r       | heap2  | tableam_parted_a_heap2
+ p       | heap2  | tableam_parted_aa_heap2
  r       | heap2  | tableam_parted_b_heap2
- r       | heap2  | tableam_parted_d_heap2
+ p       | heap2  | tableam_parted_bb_heap2
+ p       | heap2  | tableam_parted_d_heap2
+ r       | heap2  | tableam_parted_dd_heap2
+ p       | heap2  | tableam_parted_heap2
  r       | heap2  | tableam_tbl_heap2
  r       | heap2  | tableam_tblas_heap2
  m       | heap2  | tableam_tblmv_heap2
+ t       | heap2  | toast for tableam_parted_a_heap2
  t       | heap2  | toast for tableam_parted_b_heap2
- t       | heap2  | toast for tableam_parted_d_heap2
-(7 rows)
+ t       | heap2  | toast for tableam_parted_dd_heap2
+(13 rows)
 
@@ -226,9 +235,14 @@ ORDER BY classid, objid, objsubid;
+ table tableam_parted_dd_heap2
+(10 rows)
 
@@ -284,11 +298,26 @@ ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
@@ -326,7 +355,7 @@ ORDER BY 3, 1, 2;
@@ -355,7 +384,6 @@ ERROR:  cannot drop access method heap2 because other objects depend on it
index 256884c9592..ad4e71b4a61 100644
@@ -124,19 +124,22 @@ CREATE SEQUENCE tableam_seq_heap2 USING heap2;
 
--- CREATE TABLE ..  PARTITION BY doesn't not support USING
+-- CREATE TABLE ..  PARTITION BY supports USING
+-- new partitions will inherit the AM from their parent table
 CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2;
-
-CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a);
--- new partitions will inherit from the current default, rather the partition root
 SET default_table_access_method = 'heap';
 CREATE TABLE tableam_parted_a_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('a');
+CREATE TABLE tableam_parted_aa_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('aa') PARTITION BY LIST (a);
 SET default_table_access_method = 'heap2';
 CREATE TABLE tableam_parted_b_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('b');
+CREATE TABLE tableam_parted_bb_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('bb') PARTITION BY LIST (a);
 RESET default_table_access_method;
 -- but the method can be explicitly specified
-CREATE TABLE tableam_parted_c_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('c') USING heap;
-CREATE TABLE tableam_parted_d_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('d') USING heap2;
+CREATE TABLE tableam_parted_c_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('c') PARTITION BY LIST(a) USING heap;
+CREATE TABLE tableam_parted_d_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('d') PARTITION BY LIST(a) USING heap2;
+-- and children inherit from their direct parent
+CREATE TABLE tableam_parted_cc_heap2 PARTITION OF tableam_parted_c_heap2 FOR VALUES IN ('c');
+CREATE TABLE tableam_parted_dd_heap2 PARTITION OF tableam_parted_d_heap2 FOR VALUES IN ('d');
 
 -- List all objects in AM
 SELECT
@@ -183,10 +186,18 @@ ALTER TABLE heaptable SET ACCESS METHOD heap, SET ACCESS METHOD heap2;

---

From: Michael Paquier <[email-masked]>
Date: Thu, 1 Jun 2023 08:50:50 -0400

> src/backend/catalog/heap.c-              * access method is.

This is the third location where we rely on the fact that
RELKIND_HAS_TABLE_AM() does not include RELKIND_PARTITIONED_TABLE, so
it seems worth documenting what we are relying on as a comment?  Say:
 * Make a dependency link to force the relation to be deleted if its
 * access method is.
 *
 * No need to add an explicit dependency for the toast table, as the
 * main table depends on it.  Partitioned tables have a table access
 * method defined, and RELKIND_HAS_TABLE_AM ignores them.

> Are you suggesting to put this in a conditional: if oldrelam!=newAccessMethod ?

Yes, that's what I would add with a few lines close to the beginning
of ATExecSetTableSpaceNoStorage() to save from catalog updates if
these are not needed.
--
Michael

---

From: Daniel Gustafsson <[email-masked]>
Date: Tue, 4 Jul 2023 10:54:04 +0200

Have you had a chance to address the comments raised by Michael in his last
review such that a new patch revision can be submitted?

--
Daniel Gustafsson

---

From: Justin Pryzby <[email-masked]>
Date: Sun, 16 Jul 2023 21:37:28 -0500

> of ATExecSetTableSpaceNoStorage() to save from catalog updates if

I understand that it's possible for it to be conditional, but I don't
undertand why it's desirable/important ?

It still seems preferable to be unconditional.

>> conditionals, not more; then, it hits the same path every time.

It's not conditional in the tablespace code that this follows, nor
others like ATExecSetStatistics(), ATExecForceNoForceRowSecurity(),
ATExecSetCompression(), etc, some of which are recently-added.  If it's
important to do here, isn't it also important to do everywhere else ?

-- 
Justin

---

From: Michael Paquier <[email-masked]>
Date: Wed, 19 Jul 2023 11:49:49 +0900

> undertand why it's desirable/important ?

Because it's cheaper on repeated commands, like no CCI necessary.

> important to do here, isn't it also important to do everywhere else ?

Good point here.  I am OK to discard this point.
--
Michael

---

From: Justin Pryzby <[email-masked]>
Date: Wed, 19 Jul 2023 13:13:48 -0500

>  * method defined, and RELKIND_HAS_TABLE_AM ignores them.

You said that this location "relies on" the macro not including
partitioned tables, but I would say the opposite: the places that use
RELKIND_HAS_TABLE_AM() and do *not* say "or relkind==PARTITIONED_TABLE"
are the ones that "rely on" that...

Anyway, this updates various comments.  No other changes.

-- 
Justin
>From e96a2a109d25bb9ed7cc9c0bf80c0824128dceac Mon Sep 17 00:00:00 2001

See also:

Authors: Justin Pryzby, Soumyadeep Chakraborty
---
 doc/src/sgml/ref/alter_table.sgml       |  4 ++
 doc/src/sgml/ref/create_table.sgml      |  9 ++-
 src/backend/catalog/heap.c              |  6 +-
 src/backend/commands/tablecmds.c        | 89 +++++++++++++++++++------
 src/backend/utils/cache/lsyscache.c     | 22 ++++++
 src/backend/utils/cache/relcache.c      |  5 ++
 src/bin/pg_dump/pg_dump.c               |  3 +-
 src/bin/pg_dump/t/002_pg_dump.pl        | 34 ++++++++++
 src/include/catalog/pg_class.h          |  3 +-
 src/include/utils/lsyscache.h           |  1 +
 src/test/regress/expected/create_am.out | 62 ++++++++++++-----
 src/test/regress/sql/create_am.sql      | 25 +++++--
 12 files changed, 213 insertions(+), 50 deletions(-)

index 4c30c7d461f..82e003cabc1 100644
@@ -1450,9 +1450,11 @@ heap_create_with_catalog(const char *relname,
-		 * main table depends on it.
+		 * main table depends on it.  Partitioned tables have an access method
+		 * defined, but are not handled by RELKIND_HAS_TABLE_AM.
index 4dc029f91f1..bc343539aeb 100644
@@ -573,6 +573,7 @@ static ObjectAddress ATExecClusterOn(Relation rel, const char *indexName,
@@ -682,7 +683,6 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 
 	/*
@@ -947,20 +947,22 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 
 	/*

 
+/*
index 2c01a86c292..7861c1866ed 100644
@@ -2095,6 +2095,28 @@ get_rel_persistence(Oid relid)
 	return result;
 }
 
+/*
+ * get_rel_relam
+ *

index 8e28335915f..45d805bd225 100644
+		/* Do nothing: it's a catalog setting for partitions to inherit */
 
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index c67c0391943..c3443b1a20f 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -16104,7 +16104,8 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
 		if (RELKIND_HAS_TABLESPACE(tbinfo->relkind))
 			tablespace = tbinfo->reltablespace;

index e7d81d41449..8c4c7f3c662 100644
@@ -4584,6 +4584,40 @@ my %tests = (
 
 #########################################
diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h
index 2d1bb7af3a9..d19f33194da 100644
--- a/src/include/catalog/pg_class.h
+++ b/src/include/catalog/pg_class.h
@@ -216,7 +216,8 @@ DECLARE_INDEX(pg_class_tblspc_relfilenode_index, 3455, ClassTblspcRelfilenodeInd
 /*
  * Relation kinds with a table access method (rd_tableam).  Although sequences
  * use the heap table AM, they are enough of a special case in most uses that
- * they are not included here.
+ * they are not included here.  Likewise, partitioned tables have an access
+ * method defined; however, it's handled specially outside of this macro.
  */
 #define RELKIND_HAS_TABLE_AM(relkind) \
 	((relkind) == RELKIND_RELATION || \
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index f5fdbfe116b..067e581574b 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -140,6 +140,7 @@ extern char get_rel_relkind(Oid relid);
 extern bool get_rel_relispartition(Oid relid);
 extern Oid	get_rel_tablespace(Oid relid);
 extern char get_rel_persistence(Oid relid);
+extern Oid	get_rel_relam(Oid relid);
 extern Oid	get_transform_fromsql(Oid typid, Oid langid, List *trftypes);
 extern Oid	get_transform_tosql(Oid typid, Oid langid, List *trftypes);
 extern bool get_typisdefined(Oid typid);
diff --git a/src/test/regress/expected/create_am.out b/src/test/regress/expected/create_am.out
index b50293d514f..8622a24cb73 100644
--- a/src/test/regress/expected/create_am.out
+++ b/src/test/regress/expected/create_am.out
@@ -176,19 +176,22 @@ SELECT f1 FROM tableam_tblmv_heap2 ORDER BY f1;
   1
 (1 row)

 
@@ -313,11 +327,26 @@ ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
+-- upon ALTER, new children will inherit the new AM, whereas the existing
@@ -355,7 +384,7 @@ ORDER BY 3, 1, 2;
@@ -384,7 +413,6 @@ ERROR:  cannot drop access method heap2 because other objects depend on it
index 2785ffd8bbb..f631e9811a1 100644

@@ -201,10 +204,18 @@ ALTER TABLE heaptable SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
+-- upon ALTER, new children will inherit the new AM, whereas the existing

---

From: Peter Smith <[email-masked]>
Date: Mon, 22 Jan 2024 13:25:13 +1100

2024-01 Commitfest.

Hi, this patch was marked in CF as "Needs Review" [1], but there has
been no activity on this thread for 6+ months.

Is anything else planned, or can you post something to elicit more
interest in reviews for the latest patch? Otherwise, if nothing
happens then the CF entry will be closed ("Returned with feedback") at
the end of this CF.

======
[1] https://commitfest.postgresql.org/46/3727/

Kind Regards,
Peter Smith.
Fujitsu Australia

---

From: Alvaro Herrera <[email-masked]>
Date: Thu, 1 Feb 2024 16:50:49 +0100

> +			accessMethodId = get_table_am_oid(default_table_access_method, false);

I think this works similarly but not identically to tablespace defaults,
and the difference could be confusing.  You seem to have made it so that
the partitioned table _always_ have a table AM, so the partitions can
always inherit from it.  I think it would be more sensible to _allow_
partitioned tables to have one, but not mandatory; if they don't have
it, then a partition created from it would use default_table_access_method.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
Bob [Floyd] used to say that he was planning to get a Ph.D. by the "green
stamp method," namely by saving envelopes addressed to him as 'Dr. Floyd'.
After collecting 500 such letters, he mused, a university somewhere in
Arizona would probably grant him a degree.              (Don Knuth)

---

From: Michael Paquier <[email-masked]>
Date: Fri, 2 Feb 2024 06:46:13 +0900

> it, then a partition created from it would use default_table_access_method.

You mean to allow a value of 0 in pg_class.relam on a partitioned
table to allow any partitions created on it to use the default AM in
the GUC when the partition is created?  Yes, this inconsistency was
bothering me as well in the patch.
--
Michael

---

From: Robert Haas <[email-masked]>
Date: Fri, 2 Feb 2024 12:04:47 -0500

> it, then a partition created from it would use default_table_access_method.

I agree that we don't want this feature to invent any new behavior. If
it's clearly and fully parallel to what we do for tablespaces, then I
think it's probably OK, but anything less than that would be a cause
for concern for me.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

---

From: Peter Eisentraut <[email-masked]>
Date: Tue, 20 Feb 2024 15:47:46 +0100

> Anyway, this updates various comments.  No other changes.

It would be helpful if this patch could more extensively document in its 
commit message what semantic changes it makes.  Various options of 
possible behaviors were discussed in this thread, but it's not clear 
which behaviors were chosen in this particular patch version.

The general idea is that you can set an access method on a partitioned 
table.  That much seems very agreeable.  But then what happens with this 
setting, how can you override it, how can you change it, what happens 
when you change it, what happens with existing partitions and new 
partitions, etc. -- and which of these behaviors are new and old.  Many 
things to specify.

---

From: Michael Paquier <[email-masked]>
Date: Wed, 21 Feb 2024 15:40:18 +0900

On Tue, Feb 20, 2024 at 03:47:46PM +0100, Peter Eisentraut wrote:
> commit message what semantic changes it makes.  Various options of possible
> behaviors were discussed in this thread, but it's not clear which behaviors
> were chosen in this particular patch version.
> 
> setting, how can you override it, how can you change it, what happens when
> you change it, what happens with existing partitions and new partitions,
> etc. -- and which of these behaviors are new and old.  Many things to
> specify.

The main point in this patch is the following code block in
DefineRelation(), that defines the semantics about the AM set for a
partitioned table:
+    else if (RELKIND_HAS_TABLE_AM(relkind) || relkind == RELKIND_PARTITIONED_TABLE)
     {
+        if (stmt->partbound)
+        {
+            /*
+             * For partitions, if no access method is specified, use the AM of
+             * the parent table.
+             */
+            Assert(list_length(inheritOids) == 1);
+            accessMethodId = get_rel_relam(linitial_oid(inheritOids));
+            Assert(OidIsValid(accessMethodId));
+        }
+        else
+            accessMethodId = get_table_am_oid(default_table_access_method, false);
     }

This means that all partitioned tables would have pg_class.relam set,
and that relam would never be 0:
- The USING clause takes priority over default_table_access_method.
- If no USING clause, default_table_access_method is the AM used

Any partitions created from this partitioned table would inherit the
AM set, ignoring default_table_access_method.  

Alvaro has made a very good point a couple of days ago at [1] where we
should try to make the behavior stick closer to tablespaces, where it
could be possible to set relam to 0 for a partitioned table, where a
partition would inherit the AM set in the GUC when a USING clause is
not defined (if USING specifies the AM, we'd just use it).

Existing partitions should not be changed if the AM of their
partitioned table changes, so you can think of the AM as a hint for
the creation of new partitions.

[1]: https://www.postgresql.org/message-id/[email-masked]
--
Michael

---

From: Peter Eisentraut <[email-masked]>
Date: Wed, 21 Feb 2024 08:46:48 +0100

> not defined (if USING specifies the AM, we'd just use it).

Yes, I think most people agreed that that would be the preferred behavior.

---

From: Michael Paquier <[email-masked]>
Date: Wed, 28 Feb 2024 17:08:49 +0900

> Yes, I think most people agreed that that would be the preferred behavior.

Challenge accepted.  As of the patch attached.

Tablespaces rely MyDatabaseTableSpace to fallback to the database's
default if not specified, but we cannot do that for table AMs as there
is no equivalent to dattablespace.

I have implemented that so as we keep the default, historical
behavior: if pg_class.relam is 0 for a partitioned table, use the AM
defined by default_table_access_method.  The patch only adds a path to
switch to a different AM than the GUC when creating a new partition if
and only if a partitioned table has been manipulated with ALTER TABLE
SET ACCESS METHOD to update its AM to something else than the GUC.
Similarly to tablespaces, CREATE TABLE USING is *not* supported for
partitioned tables, same behavior as previously.

There is a bit more regarding the handling of the entries in
pg_depend, but nothing really complicated, knowing that there can be
three possible patterns:
- Add a new dependency if changing the AM to be something different
than the GUC.
- Remove the dependency if changing the AM to the value of the GUC,
when something existing previously.
- Update the dependency if switching between AMs that don't refer to
the GUC at all.

If the AM of a partitioned table is not changed, there is no need to
update the catalogs at all.  The prep phase of the sub-command is
already aware of that, setting the new AM OID to InvalidOid in this
case.

The attached includes regression tests that check all the dependency
entries, the contents of pg_class for partitioned tables, as well as
the creation of partitions when pg_class.relam is not 0.  I'd welcome
more eyes regarding these changes.  pg_dump needs to be tweaked to
save the AM information of a partitioned table, like the previous
versions.  There are tests for these dump patterns, that needed a
slight tweak to work.  Docs have been refreshed.

Thoughts, comments?
--
Michael
From 4309cd0806f92c573bb362f0da92eba95c34a827 Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Wed, 28 Feb 2024 17:06:16 +0900
Subject: [PATCH v2] Allow specifying access method of partitioned tables

Authors: Justin Pryzby, Soumyadeep Chakraborty
---
 src/include/catalog/pg_class.h          |   4 +-
 src/include/utils/lsyscache.h           |   1 +
 src/backend/commands/tablecmds.c        | 138 +++++++++++++++++++++---
 src/backend/utils/cache/lsyscache.c     |  22 ++++
 src/backend/utils/cache/relcache.c      |   7 ++
 src/bin/pg_dump/pg_dump.c               |   3 +-
 src/bin/pg_dump/t/002_pg_dump.pl        |  35 ++++++
 src/test/regress/expected/create_am.out | 100 ++++++++++++++++-
 src/test/regress/sql/create_am.sql      |  54 +++++++++-
 doc/src/sgml/catalogs.sgml              |   5 +-
 doc/src/sgml/ref/alter_table.sgml       |   8 ++
 doc/src/sgml/ref/create_table.sgml      |   4 +
 12 files changed, 357 insertions(+), 24 deletions(-)

diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h
index 3b7533e7bb..304241923b 100644
--- a/src/include/catalog/pg_class.h
+++ b/src/include/catalog/pg_class.h
@@ -219,7 +219,9 @@ MAKE_SYSCACHE(RELNAMENSP, pg_class_relname_nsp_index, 128);
 /*
  * Relation kinds with a table access method (rd_tableam).  Although sequences
  * use the heap table AM, they are enough of a special case in most uses that
- * they are not included here.
+ * they are not included here.  Likewise, partitioned tables can have an access
+ * method defined so as their children can inherit it; however, this is handled
+ * specially outside of this macro.
  */
 #define RELKIND_HAS_TABLE_AM(relkind) \
 	((relkind) == RELKIND_RELATION || \
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index e4a200b00e..35a8dec2b9 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -139,6 +139,7 @@ extern char get_rel_relkind(Oid relid);
 extern bool get_rel_relispartition(Oid relid);
 extern Oid	get_rel_tablespace(Oid relid);
 extern char get_rel_persistence(Oid relid);
+extern Oid	get_rel_relam(Oid relid);
 extern Oid	get_transform_fromsql(Oid typid, Oid langid, List *trftypes);
 extern Oid	get_transform_tosql(Oid typid, Oid langid, List *trftypes);
 extern bool get_typisdefined(Oid typid);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f798794556..4b4b34278e 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -592,6 +592,7 @@ static ObjectAddress ATExecClusterOn(Relation rel, const char *indexName,
 									 LOCKMODE lockmode);
 static void ATExecDropCluster(Relation rel, LOCKMODE lockmode);
 static void ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname);
+static void ATExecSetAccessMethodNoStorage(Relation rel, Oid newAccessMethod);
 static bool ATPrepChangePersistence(Relation rel, bool toLogged);
 static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
 								const char *tablespacename, LOCKMODE lockmode);
@@ -706,7 +707,6 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 	Oid			ofTypeId;
 	ObjectAddress address;
 	LOCKMODE	parentLockmode;
-	const char *accessMethod = NULL;
 	Oid			accessMethodId = InvalidOid;
 
 	/*
@@ -960,19 +960,28 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 	 */
 	if (stmt->accessMethod != NULL)
 	{
-		accessMethod = stmt->accessMethod;
+		accessMethodId = get_table_am_oid(stmt->accessMethod, false);
 
 		if (partitioned)
 			ereport(ERROR,
 					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 					 errmsg("specifying a table access method is not supported on a partitioned table")));
 	}
-	else if (RELKIND_HAS_TABLE_AM(relkind))
-		accessMethod = default_table_access_method;
+	else if (stmt->partbound)
+	{
+		/*
+		 * For partitions, if no access method is specified, use the AM of the
+		 * parent table.
+		 */
+		Assert(list_length(inheritOids) == 1);
+		accessMethodId = get_rel_relam(linitial_oid(inheritOids));
+	}
+	else
+		accessMethodId = InvalidOid;
 
-	/* look up the access method, verify it is for a table */
-	if (accessMethod != NULL)
-		accessMethodId = get_table_am_oid(accessMethod, false);
+	/* Still nothing?  Use the default */
+	if (RELKIND_HAS_TABLE_AM(relkind) && !OidIsValid(accessMethodId))
+		accessMethodId = get_table_am_oid(default_table_access_method, false);
 
 	/*
 	 * Create the relation.  Inherited defaults and constraints are passed in
@@ -5048,12 +5057,6 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
 		case AT_SetAccessMethod:	/* SET ACCESS METHOD */
 			ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_MATVIEW);
 
@@ -5410,6 +5413,13 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
 
@@ -15544,6 +15554,108 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
 	list_free(reltoastidxids);
 }
 
+/*
+ * Special handling of ALTER TABLE SET ACCESS METHOD for relations with no
+ * storage that have an interest in preserving AM.
+ *
+ * Since these have no storage, setting the access method is a catalog only
+ * operation.
+ */
+static void
+ATExecSetAccessMethodNoStorage(Relation rel, Oid newAccessMethodId)
+{
+	Relation	pg_class;
+	Oid			oldAccessMethodId;
+	HeapTuple	tuple;
+	Form_pg_class rd_rel;
+	Oid			reloid = RelationGetRelid(rel);
+	Oid			defaultAccessMethodId;
+
+	if (!OidIsValid(newAccessMethodId))
+	{
+		/* if the access method is unchanged, leave */
+		return;
+	}
+
+	/*
+	 * Shouldn't be called on relations having storage; these are processed in
+	 * phase 3.
+	 */
+	Assert(!RELKIND_HAS_STORAGE(rel->rd_rel->relkind));
+	defaultAccessMethodId = get_table_am_oid(default_table_access_method, false);
+
+	/* Get a modifiable copy of the relation's pg_class row. */
+	pg_class = table_open(RelationRelationId, RowExclusiveLock);
+
+	tuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(reloid));
+	if (!HeapTupleIsValid(tuple))
+		elog(ERROR, "cache lookup failed for relation %u", reloid);
+	rd_rel = (Form_pg_class) GETSTRUCT(tuple);
+
+	/* Update the pg_class row. */
+	oldAccessMethodId = rd_rel->relam;
+	rd_rel->relam = (newAccessMethodId == defaultAccessMethodId) ?
+		InvalidOid : newAccessMethodId;
+
+	/* Leave if no update required */
+	if (rd_rel->relam == oldAccessMethodId)
+	{
+		heap_freetuple(tuple);
+		table_close(pg_class, RowExclusiveLock);
+		return;
+	}
+
+	CatalogTupleUpdate(pg_class, &tuple->t_self, tuple);
+
+	/*
+	 * Update the dependency on the new access method.  No dependency is added
+	 * if the access method is InvalidOid.
+	 */
+	if (!OidIsValid(oldAccessMethodId) && OidIsValid(newAccessMethodId))
+	{
+		ObjectAddress relobj,
+					referenced;
+
+		/*
+		 * New access method is defined and there was no dependency
+		 * previously, so record a new one.
+		 */
+		ObjectAddressSet(relobj, RelationRelationId, reloid);
+		ObjectAddressSet(referenced, AccessMethodRelationId, newAccessMethodId);
+		recordDependencyOn(&relobj, &referenced, DEPENDENCY_NORMAL);
+	}
+	else if (OidIsValid(oldAccessMethodId) &&
+			 newAccessMethodId == defaultAccessMethodId)
+	{
+		/*
+		 * There was an access method defined, and no new one, so just remove
+		 * the existing dependency.
+		 */
+		deleteDependencyRecordsForClass(RelationRelationId, reloid,
+										AccessMethodRelationId,
+										DEPENDENCY_NORMAL);
+	}
+	else
+	{
+		Assert(OidIsValid(oldAccessMethodId) &&
+			   newAccessMethodId != defaultAccessMethodId);
+
+		/* Both are valid, so update the dependency */
+		changeDependencyFor(RelationRelationId, reloid, AccessMethodRelationId,
+							oldAccessMethodId, newAccessMethodId);
+	}
+
+	CommandCounterIncrement();
+
+	InvokeObjectPostAlterHook(RelationRelationId, RelationGetRelid(rel), 0);
+
+	heap_freetuple(tuple);
+	table_close(pg_class, RowExclusiveLock);
+
+	/* Make sure the relam change is visible */
+	CommandCounterIncrement();
+}
+
 /*
  * Special handling of ALTER TABLE SET TABLESPACE for relations with no
  * storage that have an interest in preserving tablespace.
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index f730aa26c4..2ea249e90c 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -2068,6 +2068,28 @@ get_rel_persistence(Oid relid)
 	return result;
 }
 
+/*
+ * get_rel_relam
+ *

diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 50acae4529..e80f156912 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -1210,6 +1210,13 @@ retry:
 	else if (RELKIND_HAS_TABLE_AM(relation->rd_rel->relkind) ||
 			 relation->rd_rel->relkind == RELKIND_SEQUENCE)
 		RelationInitTableAccessMethod(relation);
+	else if (relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+	{
+		/*
+		 * Do nothing: access methods is a setting that partitions can
+		 * inherit.
+		 */
+	}
 	else
 		Assert(relation->rd_rel->relam == InvalidOid);
 
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 2225a12718..91a7031b05 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -16610,7 +16610,8 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
 		if (RELKIND_HAS_TABLESPACE(tbinfo->relkind))
 			tablespace = tbinfo->reltablespace;

index 00b5092713..dfee1982f4 100644
@@ -4587,6 +4587,41 @@ my %tests = (
+			CREATE TABLE dump_test.regress_pg_dump_table_am_parent (id int) PARTITION BY LIST (id);
+			ALTER TABLE dump_test.regress_pg_dump_table_am_parent SET ACCESS METHOD regress_table_am;
 
 #########################################
diff --git a/src/test/regress/expected/create_am.out b/src/test/regress/expected/create_am.out
index b50293d514..77089e2905 100644
--- a/src/test/regress/expected/create_am.out
+++ b/src/test/regress/expected/create_am.out
@@ -313,12 +313,104 @@ ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
 ERROR:  cannot have multiple SET ACCESS METHOD subcommands
 DROP MATERIALIZED VIEW heapmv;
 DROP TABLE heaptable;
--- No support for partitioned tables.
-CREATE TABLE am_partitioned(x INT, y INT)
-  PARTITION BY hash (x);
+-- Partition hierarchies with access methods
+BEGIN;
+SET LOCAL default_table_access_method = 'heap';
+CREATE TABLE am_partitioned(x INT, y INT) PARTITION BY hash (x);
+-- pg_class.relam is 0, no dependency recorded between the AM and the
+-- partitioned table.
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+ relam 
+-------
+     0
+(1 row)
+
+SELECT pg_describe_object(classid, objid, objsubid) AS obj,
+       pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
+  FROM pg_depend, pg_am
+  WHERE pg_depend.refclassid = 'pg_am'::regclass
+    AND pg_am.oid = pg_depend.refobjid
+    AND pg_depend.objid = 'am_partitioned'::regclass;
+ obj | refobj 
+-----+--------
+(0 rows)
+
+-- New default is set, with dependency added.
 ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
-ERROR:  cannot change access method of a partitioned table
+SELECT a.amname FROM pg_class c, pg_am a
+  WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
+ amname 
+--------
+ heap2
+(1 row)
+
+SELECT pg_describe_object(classid, objid, objsubid) AS obj,
+       pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
+  FROM pg_depend, pg_am
+  WHERE pg_depend.refclassid = 'pg_am'::regclass
+    AND pg_am.oid = pg_depend.refobjid
+    AND pg_depend.objid = 'am_partitioned'::regclass;
+         obj          |       refobj        
+----------------------+---------------------
+ table am_partitioned | access method heap2
+(1 row)
+
+-- Default is set, with dependency updated.
+SET LOCAL default_table_access_method = 'heap2';
+ALTER TABLE am_partitioned SET ACCESS METHOD heap;
+SELECT a.amname FROM pg_class c, pg_am a
+  WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
+ amname 
+--------
+ heap
+(1 row)
+
+-- Dependency pinned, hence removed.
+SELECT pg_describe_object(classid, objid, objsubid) AS obj,
+       pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
+  FROM pg_depend, pg_am
+  WHERE pg_depend.refclassid = 'pg_am'::regclass
+    AND pg_am.oid = pg_depend.refobjid
+    AND pg_depend.objid = 'am_partitioned'::regclass;
+ obj | refobj 
+-----+--------
+(0 rows)
+
+-- Reset to default
+ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+ relam 
+-------
+     0
+(1 row)
+
+-- Upon ALTER TABLE SET ACCESS METHOD on a partitioned table, new partitions
+-- will inherit the AM set.  Existing partitioned are unchanged
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+ relam 
+-------
+     0
+(1 row)
+
+SET LOCAL default_table_access_method = 'heap';
+CREATE TABLE am_partitioned_1 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 3,REMAINDER 0);
+SET LOCAL default_table_access_method = 'heap2';
+CREATE TABLE am_partitioned_2 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 3,REMAINDER 1);
+SET LOCAL default_table_access_method = 'heap';
+ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
+CREATE TABLE am_partitioned_3 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 3,REMAINDER 2);
+SELECT relname, amname FROM pg_class c, pg_am am
+  WHERE c.relam = am.oid AND c.relname LIKE 'am_partitioned%' ORDER BY 1;
+     relname      | amname 
+------------------+--------
+ am_partitioned   | heap2
+ am_partitioned_1 | heap
+ am_partitioned_2 | heap2
+ am_partitioned_3 | heap2
+(4 rows)
+
 DROP TABLE am_partitioned;
+COMMIT;
 -- Second, create objects in the new AM by changing the default AM
 BEGIN;
 SET LOCAL default_table_access_method = 'heap2';
diff --git a/src/test/regress/sql/create_am.sql b/src/test/regress/sql/create_am.sql
index 2785ffd8bb..884f49e1a3 100644
--- a/src/test/regress/sql/create_am.sql
+++ b/src/test/regress/sql/create_am.sql
@@ -201,11 +201,59 @@ ALTER TABLE heaptable SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
 ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
 DROP MATERIALIZED VIEW heapmv;
 DROP TABLE heaptable;
--- No support for partitioned tables.
-CREATE TABLE am_partitioned(x INT, y INT)
-  PARTITION BY hash (x);
+
+-- Partition hierarchies with access methods
+BEGIN;
+SET LOCAL default_table_access_method = 'heap';
+CREATE TABLE am_partitioned(x INT, y INT) PARTITION BY hash (x);
+-- pg_class.relam is 0, no dependency recorded between the AM and the
+-- partitioned table.
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+SELECT pg_describe_object(classid, objid, objsubid) AS obj,
+       pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
+  FROM pg_depend, pg_am
+  WHERE pg_depend.refclassid = 'pg_am'::regclass
+    AND pg_am.oid = pg_depend.refobjid
+    AND pg_depend.objid = 'am_partitioned'::regclass;
+-- New default is set, with dependency added.
 ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
+SELECT a.amname FROM pg_class c, pg_am a
+  WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
+SELECT pg_describe_object(classid, objid, objsubid) AS obj,
+       pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
+  FROM pg_depend, pg_am
+  WHERE pg_depend.refclassid = 'pg_am'::regclass
+    AND pg_am.oid = pg_depend.refobjid
+    AND pg_depend.objid = 'am_partitioned'::regclass;
+-- Default is set, with dependency updated.
+SET LOCAL default_table_access_method = 'heap2';
+ALTER TABLE am_partitioned SET ACCESS METHOD heap;
+SELECT a.amname FROM pg_class c, pg_am a
+  WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
+-- Dependency pinned, hence removed.
+SELECT pg_describe_object(classid, objid, objsubid) AS obj,
+       pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
+  FROM pg_depend, pg_am
+  WHERE pg_depend.refclassid = 'pg_am'::regclass
+    AND pg_am.oid = pg_depend.refobjid
+    AND pg_depend.objid = 'am_partitioned'::regclass;
+-- Reset to default
+ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+-- Upon ALTER TABLE SET ACCESS METHOD on a partitioned table, new partitions
+-- will inherit the AM set.  Existing partitioned are unchanged
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+SET LOCAL default_table_access_method = 'heap';
+CREATE TABLE am_partitioned_1 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 3,REMAINDER 0);
+SET LOCAL default_table_access_method = 'heap2';
+CREATE TABLE am_partitioned_2 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 3,REMAINDER 1);
+SET LOCAL default_table_access_method = 'heap';
+ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
+CREATE TABLE am_partitioned_3 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 3,REMAINDER 2);
+SELECT relname, amname FROM pg_class c, pg_am am
+  WHERE c.relam = am.oid AND c.relname LIKE 'am_partitioned%' ORDER BY 1;
 DROP TABLE am_partitioned;
+COMMIT;
 
 -- Second, create objects in the new AM by changing the default AM
 BEGIN;
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 880f717b10..359d03b7f1 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1989,8 +1989,9 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
       </para>
       <para>
        If this is a table or an index, the access method used (heap,
-       B-tree, hash, etc.); otherwise zero (zero occurs for sequences,
-       as well as relations without storage, such as views)
+       B-tree, hash, etc.); otherwise zero. Zero occurs for sequences,
+       as well as relations without storage, such as views. Partitioned
+       tables may use a non-zero value.
       </para></entry>
      </row>
 
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 9670671107..e5dc11fafd 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -735,6 +735,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
       This form changes the access method of the table by rewriting it. See
       <xref linkend="tableam"/> for more information.
      </para>
+     <para>
+      When applied to a partitioned table, there is no data to rewrite, but any
+      partitions created afterwards will use that access method unless
+      overridden by a <literal>USING</literal> clause. If the value
+      specified is the same as <varname>default_table_access_method</varname>
+      the partitioned table is switched to rely on the default that is set at
+      the time when new partitions are created, instead.
+     </para>
     </listitem>
    </varlistentry>
 
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 4cbaaccaf7..b79081a5ec 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1330,6 +1330,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
       method is chosen for the new table. See <xref
       linkend="guc-default-table-access-method"/> for more information.
      </para>
+     <para>
+      When creating a partition, the table access method is the access method
+      of its partitioned table, if set.
+     </para>
     </listitem>
    </varlistentry>
 
-- 
2.43.0

---

From: Justin Pryzby <[email-masked]>
Date: Thu, 29 Feb 2024 08:51:31 -0600

> Challenge accepted.  As of the patch attached.

Thanks for picking it up.  I find it pretty hard to switch back to
put the needed effort into a patch after a long period.

> partitioned tables, same behavior as previously.

This patch allows resetting relam=0 by running ALTER TABLE SET AM to the
same value as the GUC.  Maybe it'd be better to have an explicit SET
DEFAULT (as in b9424d01 and 4f622503).

-- 
Justin

---

From: Michael Paquier <[email-masked]>
Date: Fri, 1 Mar 2024 10:56:50 +0900

> DEFAULT (as in b9424d01 and 4f622503).

Outside the scope of this patch's thread, this looks like a good idea
even for tables/matviews.  And the semantics are pretty easy: if DEFAULT
is specified, just set the access method to NULL in the parser and let
tablecmds.c go the AM OID lookup in the prep phase if set to NULL.
See 0001 attached.  This one looks pretty good taken as an independent
piece.

When it comes to partitioned tables, there is a still a tricky case:
what should we do when a user specifies a non-default value in the SET
ACCESS METHOD clause and it matches default_table_access_method?
Should the relam be 0 or should we force relam to be the OID of the
given value given by the query?  Implementation-wise, forcing the
value to 0 is simpler, but I can get why it could be confusing as
well, because the state of the catalogs does not reflect what was
provided in the query.  At the same time, the user has explicitly set
the access method to be the same as the default, so perhaps 0 makes
sense anyway in this case.

0002 does that, as that's simpler.  I'm not sure if there is a case
for forcing a value in relam if the query has the same value as the
default.  Thoughts?
--
Michael
From d8f1094415c34eaaae422226c8d05b8abc83693b Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Fri, 1 Mar 2024 10:22:22 +0900
Subject: [PATCH v2 1/2] Add DEFAULT option to ALTER TABLE SET ACCESS METHOD

---
 src/backend/commands/tablecmds.c        |  3 ++-
 src/backend/parser/gram.y               | 10 ++++++++--
 src/test/regress/expected/create_am.out | 21 +++++++++++++++++++++
 src/test/regress/sql/create_am.sql      | 11 +++++++++++
 doc/src/sgml/ref/alter_table.sgml       |  6 ++++--
 5 files changed, 46 insertions(+), 5 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f798794556..3b1c2590fd 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -15212,7 +15212,8 @@ ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname)
 	Oid			amoid;
 
 	/* Check that the table access method exists */
-	amoid = get_table_am_oid(amname, false);
+	amoid = get_table_am_oid(amname ? amname : default_table_access_method,
+							 false);
 
 	if (rel->rd_rel->relam == amoid)
 		return;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 130f7fc7c3..c6e2f679fd 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -338,6 +338,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <list>    alter_identity_column_option_list
 %type <defelt>  alter_identity_column_option
 %type <node>	set_statistics_value
+%type <str>		set_access_method_name
 
 %type <list>	createdb_opt_list createdb_opt_items copy_opt_list
 				transaction_mode_list
@@ -2859,8 +2860,8 @@ alter_table_cmd:
 					n->newowner = $3;
 					$$ = (Node *) n;
 				}
-			/* ALTER TABLE <name> SET ACCESS METHOD <amname> */
-			| SET ACCESS METHOD name
+			/* ALTER TABLE <name> SET ACCESS METHOD { <amname> | DEFAULT } */
+			| SET ACCESS METHOD set_access_method_name
 				{
 					AlterTableCmd *n = makeNode(AlterTableCmd);
 
@@ -3076,6 +3077,11 @@ set_statistics_value:
 			| DEFAULT						{ $$ = NULL; }
 		;
 
+set_access_method_name:
+			ColId							{ $$ = $1; }
+			| DEFAULT						{ $$ = NULL; }
+		;
+
 PartitionBoundSpec:
 			/* a HASH partition */
 			FOR VALUES WITH '(' hash_partbound ')'
diff --git a/src/test/regress/expected/create_am.out b/src/test/regress/expected/create_am.out
index b50293d514..e843d39ee7 100644
--- a/src/test/regress/expected/create_am.out
+++ b/src/test/regress/expected/create_am.out
@@ -283,6 +283,27 @@ SELECT COUNT(a), COUNT(1) FILTER(WHERE a=1) FROM heaptable;
      9 |     1
 (1 row)
 
+-- DEFAULT access method
+BEGIN;
+SET LOCAL default_table_access_method TO heap2;
+ALTER TABLE heaptable SET ACCESS METHOD DEFAULT;
+SELECT amname FROM pg_class c, pg_am am
+  WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
+ amname 
+--------
+ heap2
+(1 row)
+
+SET LOCAL default_table_access_method TO heap;
+ALTER TABLE heaptable SET ACCESS METHOD DEFAULT;
+SELECT amname FROM pg_class c, pg_am am
+  WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
+ amname 
+--------
+ heap
+(1 row)
+
+ROLLBACK;
 -- ALTER MATERIALIZED VIEW SET ACCESS METHOD
 CREATE MATERIALIZED VIEW heapmv USING heap AS SELECT * FROM heaptable;
 SELECT amname FROM pg_class c, pg_am am
diff --git a/src/test/regress/sql/create_am.sql b/src/test/regress/sql/create_am.sql
index 2785ffd8bb..f752bf1678 100644
--- a/src/test/regress/sql/create_am.sql
+++ b/src/test/regress/sql/create_am.sql
@@ -188,6 +188,17 @@ ALTER TABLE heaptable SET ACCESS METHOD heap2;
 SELECT amname FROM pg_class c, pg_am am
   WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
 SELECT COUNT(a), COUNT(1) FILTER(WHERE a=1) FROM heaptable;
+-- DEFAULT access method
+BEGIN;
+SET LOCAL default_table_access_method TO heap2;
+ALTER TABLE heaptable SET ACCESS METHOD DEFAULT;
+SELECT amname FROM pg_class c, pg_am am
+  WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
+SET LOCAL default_table_access_method TO heap;
+ALTER TABLE heaptable SET ACCESS METHOD DEFAULT;
+SELECT amname FROM pg_class c, pg_am am
+  WHERE c.relam = am.oid AND c.oid = 'heaptable'::regclass;
+ROLLBACK;
 -- ALTER MATERIALIZED VIEW SET ACCESS METHOD
 CREATE MATERIALIZED VIEW heapmv USING heap AS SELECT * FROM heaptable;
 SELECT amname FROM pg_class c, pg_am am
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 9670671107..96e3d77605 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -76,7 +76,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
     CLUSTER ON <replaceable class="parameter">index_name</replaceable>
     SET WITHOUT CLUSTER
     SET WITHOUT OIDS
-    SET ACCESS METHOD <replaceable class="parameter">new_access_method</replaceable>
+    SET ACCESS METHOD { <replaceable class="parameter">new_access_method</replaceable> | DEFAULT }
     SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
     SET { LOGGED | UNLOGGED }
     SET ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )
@@ -733,7 +733,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     <listitem>
      <para>
       This form changes the access method of the table by rewriting it. See
-      <xref linkend="tableam"/> for more information.
+      <xref linkend="tableam"/> for more information. Writing
+      <literal>DEFAULT</literal> changes the access method of the table
+      to <xref linkend="guc-default-table-access-method"/>.
      </para>
     </listitem>
    </varlistentry>
-- 
2.43.0

From 4ecebe7a5016cf5acac437fc94267c911f6166d7 Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Fri, 1 Mar 2024 10:37:36 +0900
Subject: [PATCH v2 2/2] Allow specifying access method of partitioned tables

Authors: Justin Pryzby, Soumyadeep Chakraborty
---
 src/include/catalog/pg_class.h          |   4 +-
 src/include/utils/lsyscache.h           |   1 +
 src/backend/commands/tablecmds.c        | 138 +++++++++++++++++++++---
 src/backend/utils/cache/lsyscache.c     |  22 ++++
 src/backend/utils/cache/relcache.c      |   7 ++
 src/bin/pg_dump/pg_dump.c               |   3 +-
 src/bin/pg_dump/t/002_pg_dump.pl        |  35 ++++++
 src/test/regress/expected/create_am.out | 108 ++++++++++++++++++-
 src/test/regress/sql/create_am.sql      |  57 +++++++++-
 doc/src/sgml/catalogs.sgml              |   5 +-
 doc/src/sgml/ref/alter_table.sgml       |   8 ++
 doc/src/sgml/ref/create_table.sgml      |   4 +
 12 files changed, 368 insertions(+), 24 deletions(-)

index 3b1c2590fd..a234861612 100644
 
 	/*
 
 		if (partitioned)
 			ereport(ERROR,

 	/*

@@ -15545,6 +15555,108 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
 	list_free(reltoastidxids);
 }
 
+/*
 
+/*
+ * get_rel_relam
+ *

 

 
 #########################################
diff --git a/src/test/regress/expected/create_am.out b/src/test/regress/expected/create_am.out
index e843d39ee7..f990887752 100644
--- a/src/test/regress/expected/create_am.out
+++ b/src/test/regress/expected/create_am.out
@@ -334,12 +334,112 @@ ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
 ERROR:  cannot have multiple SET ACCESS METHOD subcommands
 DROP MATERIALIZED VIEW heapmv;
 DROP TABLE heaptable;
--- No support for partitioned tables.
-CREATE TABLE am_partitioned(x INT, y INT)
-  PARTITION BY hash (x);
+-- Partition hierarchies with access methods
+BEGIN;
+SET LOCAL default_table_access_method = 'heap';
+CREATE TABLE am_partitioned(x INT, y INT) PARTITION BY hash (x);
+-- pg_class.relam is 0, no dependency recorded between the AM and the
+-- partitioned table.
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+ relam 
+-------
+     0
+(1 row)
+
+SELECT pg_describe_object(classid, objid, objsubid) AS obj,
+       pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
+  FROM pg_depend, pg_am
+  WHERE pg_depend.refclassid = 'pg_am'::regclass
+    AND pg_am.oid = pg_depend.refobjid
+    AND pg_depend.objid = 'am_partitioned'::regclass;
+ obj | refobj 
+-----+--------
+(0 rows)
+
+-- New default is set, with dependency added.
 ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
-ERROR:  cannot change access method of a partitioned table
+SELECT a.amname FROM pg_class c, pg_am a
+  WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
+ amname 
+--------
+ heap2
+(1 row)
+
+SELECT pg_describe_object(classid, objid, objsubid) AS obj,
+       pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
+  FROM pg_depend, pg_am
+  WHERE pg_depend.refclassid = 'pg_am'::regclass
+    AND pg_am.oid = pg_depend.refobjid
+    AND pg_depend.objid = 'am_partitioned'::regclass;
+         obj          |       refobj        
+----------------------+---------------------
+ table am_partitioned | access method heap2
+(1 row)
+
+-- Default is set, with dependency updated.
+SET LOCAL default_table_access_method = 'heap2';
+ALTER TABLE am_partitioned SET ACCESS METHOD heap;
+SELECT a.amname FROM pg_class c, pg_am a
+  WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
+ amname 
+--------
+ heap
+(1 row)
+
+-- Dependency pinned, hence removed.
+SELECT pg_describe_object(classid, objid, objsubid) AS obj,
+       pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
+  FROM pg_depend, pg_am
+  WHERE pg_depend.refclassid = 'pg_am'::regclass
+    AND pg_am.oid = pg_depend.refobjid
+    AND pg_depend.objid = 'am_partitioned'::regclass;
+ obj | refobj 
+-----+--------
+(0 rows)
+
+-- Reset to default
+ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+ relam 
+-------
+     0
+(1 row)
+
+-- Upon ALTER TABLE SET ACCESS METHOD on a partitioned table, new partitions
+-- will inherit the AM set.  Existing partitioned are unchanged
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+ relam 
+-------
+     0
+(1 row)
+
+SET LOCAL default_table_access_method = 'heap';
+CREATE TABLE am_partitioned_1 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 4,REMAINDER 0);
+SET LOCAL default_table_access_method = 'heap2';
+CREATE TABLE am_partitioned_2 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 4,REMAINDER 1);
+SET LOCAL default_table_access_method = 'heap';
+ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
+CREATE TABLE am_partitioned_3 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 4,REMAINDER 2);
+ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT;
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+ relam 
+-------
+     0
+(1 row)
+
+CREATE TABLE am_partitioned_4 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 4,REMAINDER 3);
+SELECT c.relname, a.amname FROM pg_class c, pg_am a
+  WHERE c.relam = a.oid AND c.relname LIKE 'am_partitioned%' ORDER BY 1;
+     relname      | amname 
+------------------+--------
+ am_partitioned_1 | heap
+ am_partitioned_2 | heap2
+ am_partitioned_3 | heap2
+ am_partitioned_4 | heap
+(4 rows)
+
 DROP TABLE am_partitioned;
+COMMIT;
 -- Second, create objects in the new AM by changing the default AM
 BEGIN;
 SET LOCAL default_table_access_method = 'heap2';
diff --git a/src/test/regress/sql/create_am.sql b/src/test/regress/sql/create_am.sql
index f752bf1678..db6490e6c3 100644
--- a/src/test/regress/sql/create_am.sql
+++ b/src/test/regress/sql/create_am.sql
@@ -212,11 +212,62 @@ ALTER TABLE heaptable SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
 ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
 DROP MATERIALIZED VIEW heapmv;
 DROP TABLE heaptable;
--- No support for partitioned tables.
-CREATE TABLE am_partitioned(x INT, y INT)
-  PARTITION BY hash (x);
+
+-- Partition hierarchies with access methods
+BEGIN;
+SET LOCAL default_table_access_method = 'heap';
+CREATE TABLE am_partitioned(x INT, y INT) PARTITION BY hash (x);
+-- pg_class.relam is 0, no dependency recorded between the AM and the
+-- partitioned table.
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+SELECT pg_describe_object(classid, objid, objsubid) AS obj,
+       pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
+  FROM pg_depend, pg_am
+  WHERE pg_depend.refclassid = 'pg_am'::regclass
+    AND pg_am.oid = pg_depend.refobjid
+    AND pg_depend.objid = 'am_partitioned'::regclass;
+-- New default is set, with dependency added.
 ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
+SELECT a.amname FROM pg_class c, pg_am a
+  WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
+SELECT pg_describe_object(classid, objid, objsubid) AS obj,
+       pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
+  FROM pg_depend, pg_am
+  WHERE pg_depend.refclassid = 'pg_am'::regclass
+    AND pg_am.oid = pg_depend.refobjid
+    AND pg_depend.objid = 'am_partitioned'::regclass;
+-- Default is set, with dependency updated.
+SET LOCAL default_table_access_method = 'heap2';
+ALTER TABLE am_partitioned SET ACCESS METHOD heap;
+SELECT a.amname FROM pg_class c, pg_am a
+  WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
+-- Dependency pinned, hence removed.
+SELECT pg_describe_object(classid, objid, objsubid) AS obj,
+       pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
+  FROM pg_depend, pg_am
+  WHERE pg_depend.refclassid = 'pg_am'::regclass
+    AND pg_am.oid = pg_depend.refobjid
+    AND pg_depend.objid = 'am_partitioned'::regclass;
+-- Reset to default
+ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+-- Upon ALTER TABLE SET ACCESS METHOD on a partitioned table, new partitions
+-- will inherit the AM set.  Existing partitioned are unchanged
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+SET LOCAL default_table_access_method = 'heap';
+CREATE TABLE am_partitioned_1 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 4,REMAINDER 0);
+SET LOCAL default_table_access_method = 'heap2';
+CREATE TABLE am_partitioned_2 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 4,REMAINDER 1);
+SET LOCAL default_table_access_method = 'heap';
+ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
+CREATE TABLE am_partitioned_3 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 4,REMAINDER 2);
+ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT;
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+CREATE TABLE am_partitioned_4 PARTITION OF am_partitioned FOR VALUES WITH (MODULUS 4,REMAINDER 3);
+SELECT c.relname, a.amname FROM pg_class c, pg_am a
+  WHERE c.relam = a.oid AND c.relname LIKE 'am_partitioned%' ORDER BY 1;
 DROP TABLE am_partitioned;
+COMMIT;

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 96e3d77605..fbbab72784 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -737,6 +737,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
       <literal>DEFAULT</literal> changes the access method of the table
       to <xref linkend="guc-default-table-access-method"/>.
      </para>
+     <para>
+      When applied to a partitioned table, there is no data to rewrite, but any
+      partitions created afterwards will use that access method unless
+      overridden by a <literal>USING</literal> clause. If the value
+      specified is the same as <varname>default_table_access_method</varname>
+      the partitioned table is switched to rely on the default that is set at
+      the time when new partitions are created, instead.
+     </para>
     </listitem>
    </varlistentry>

-- 
2.43.0

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Fri, 1 Mar 2024 05:43:25 +0100

> sense anyway in this case.

I think we should set the AM OID explicitly. Because an important
thing to consider is: What behaviour makes sense when later
default_table_access_method is changed?
I think if someone sets it explicitly on the partitioned table, they
would want the AM of the partitioned table to stay the same when
default_table_access_method is changed. Which requires storing the AM
OID afaict.

---

From: Michael Paquier <[email-masked]>
Date: Fri, 1 Mar 2024 14:03:48 +0900

> default_table_access_method is changed?

Per the latest discussion of the thread, we've kind of reached a
consensus that we should keep the current historical bevahior on
default, where relam remains at 0, causing new partitions to grab the
GUC as AM.  If we create a partitioned table attached to a partitioned
table, it should be 0 as well.  If the partitioned table has a non-0
relam, a new partitioned table created on it will inherit the same
non-0 value. 

> default_table_access_method is changed. Which requires storing the AM

If we allow relam to be non-0 for a partitioned table, it is equally
important to give users a way to reset it at will.  My point was a bit
more subtle than that.  For example, this sequence is clear to me:
SET default_table_access_method = 'foo';
ALTER TABLE part SET ACCESS METHOD DEFAULT;

The user wants to rely on the GUC, so relam should be 0, new
partitions created on it will use the GUC.

Now, what should this sequence mean?  See:
SET default_table_access_method = 'foo';
ALTER TABLE part SET ACCESS METHOD foo;

Should the relam be 0 because the user requested a match with the GUC,
or use the OID of the AM?  There has to be some difference with
tablespaces, because relations with physical storage (tables,
matviews) can use a reltablespace of 0, but AMs have to be set for
tables and matviews.

Fun topic, especially once coupled with the internals of tablecmds.c
that uses InvalidOid for the new access AM as a special value to work
as a no-op.
--
Michael

---

From: Michael Paquier <[email-masked]>
Date: Fri, 1 Mar 2024 14:14:47 +0900

> default_table_access_method is changed. Which requires storing the AM

Oops, I think I misread that.  You just mean to always set relam when
using an AM in the SET ACCESS METHOD clause.  Apologies for the noise.
--
Michael

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Fri, 1 Mar 2024 06:25:27 +0100

> using an AM in the SET ACCESS METHOD clause.  Apologies for the noise.

Correct, I intended to say that "SET ACCESS METHOD heap" on a
partitioned table should store heap its OID. Because while storing 0
might be simpler, it will result in (imho) wrong behaviour when later
the default_table_access_method is changed. behavior won't result in
the (imho) intended. i.e. it's not simply a small detail in what the
catolog looks like, but there's an actual behavioural change.

---

From: Justin Pryzby <[email-masked]>
Date: Fri, 1 Mar 2024 15:03:14 -0600

> ACCESS METHOD clause and it matches default_table_access_method?

I don't think it's tricky - it seems more like a weird hack in the
previous patch version to make AMs behave like tablespaces, despite not
being completely parallel, due to the absence of a pg_default AM.

With the new 001, the hack can go away, and so it should.

> given value given by the query?

You said "force" it to be the user-specified value, but I think that's
not "forcing", it's respecting (but to take the user's desired value,
and conditionally store 0 instead, that could be described as
"forcing.")

> Implementation-wise, forcing the value to 0 is simpler, but I can get
> why it could be confusing as well, because the state of the catalogs
> does not reflect what was provided in the query.

> At the same time, the user has explicitly set the access method to be
> the same as the default, so perhaps 0 makes sense anyway in this case.

I think if the user sets something "explicitly", the catalog should
reflect what they set.  Tablespaces have dattablespace, but AMs don't --
it's a simpler case.

For 001: we don't *need* to support "ALTER SET AM default" for leaf
tables.  It doesn't do anything that's not already possible.  But, if
AMs for partitioned tables are optional rather than required, then seems
to be needed to allow (re)settinng relam=0.

But for partitioned tables, I think it should set relam=0 directly.
Currently it 1) falls through to default_table_am; and 2) detects that
it's the default, so then sets relam to 0.  Since InvalidOid 

> that uses InvalidOid for the new access AM as a special value to work

Since InvalidOid is already taken, I guess you might need to introduce a
boolean flag, like set_relam, indicating that the statement has an
ACCESS METHOD clause.

> + * method defined so as their children can inherit it; however, this is handled

so that

> +		 * Do nothing: access methods is a setting that partitions can

method (singular), or s/is/are/

> partitioned tables, same behavior as previously.

Maybe I misunderstood what you're trying to say, but CREATE..TABLESPACE
*is* supported for partitioned tables.  I'm not sure why it wouldn't be
supported to set the AM, too.

In any case, it'd be a bit confusing for the error message to still say:

postgres=# CREATE TABLE a(i int) PARTITION BY RANGE(a) USING heap2;
ERROR:  specifying a table access method is not supported on a partitioned table

-- 
Justin

---

From: Michael Paquier <[email-masked]>
Date: Mon, 4 Mar 2024 17:46:56 +0900

> reflect what they set.  Tablespaces have dattablespace, but AMs don't --

Okay.

> to be needed to allow (re)settinng relam=0.

Indeed, for non-partitioned tables DEFAULT is a sugar flavor.  Not
mandatory, still it's nice to have to not have to type an AM.

> it's the default, so then sets relam to 0.
>

Yes, I don't see an alternative.  The default needs a different field
to be tracked down to the execution.

> method (singular), or s/is/are/

Indeed.  Fixed both.

> ERROR:  specifying a table access method is not supported on a partitioned table

I was looking at this one as well and I don't see why we could not
remove it, so you are right (missed the tablespace part last week).  A
partitioned table created as a partition of a partitioned table would
inherit the relam of its parent (0 if default is set, or non-0 is
something is set).  I have added some regression tests for that.

And I'm finishing with the attached.  To summarize SET ACCESS METHOD
on a partitioned table, the semantics are:
- DEFAULT sets the relam to 0, any partitions with storage would use
the GUC at creation time.  Partitioned tables use a relam of 0.
- If a value is set for the am, relam becomes non-0.  Any partitions
created on it inherit it (partitioned as well as non-partitioned
tables).
- No USING clause means to set its relam to 0.

0001 seems OK here, 0002 needs more eyes.  The bulk of the changes is
in the regression tests to cover all the cases I could think of.
--
Michael
From 4d8a5de79b829e4f1be875c668f85bbfa6d3f37a Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Fri, 1 Mar 2024 10:22:22 +0900
Subject: [PATCH v3 1/2] Add DEFAULT option to ALTER TABLE SET ACCESS METHOD

---

 

 
+set_access_method_name:
+			ColId							{ $$ = $1; }

From 04ff808771fceee70ecd01aeb8474b1b5c8dd9fa Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Mon, 4 Mar 2024 17:42:04 +0900
Subject: [PATCH v3 2/2] Allow specifying access method of partitioned tables

Authors: Justin Pryzby, Soumyadeep Chakraborty
---
 src/include/catalog/pg_class.h          |   4 +-
 src/include/utils/lsyscache.h           |   1 +
 src/backend/commands/tablecmds.c        | 185 +++++++++++++++++++++---
 src/backend/utils/cache/lsyscache.c     |  22 +++
 src/backend/utils/cache/relcache.c      |   7 +
 src/bin/pg_dump/pg_dump.c               |   3 +-
 src/bin/pg_dump/t/002_pg_dump.pl        |  35 +++++
 src/test/regress/expected/create_am.out | 158 +++++++++++++++++++-
 src/test/regress/sql/create_am.sql      |  91 +++++++++++-
 doc/src/sgml/catalogs.sgml              |   5 +-
 doc/src/sgml/ref/alter_table.sgml       |   8 +
 doc/src/sgml/ref/create_table.sgml      |   4 +
 12 files changed, 489 insertions(+), 34 deletions(-)

diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h
index 3b7533e7bb..b505ed1bec 100644
--- a/src/include/catalog/pg_class.h
+++ b/src/include/catalog/pg_class.h
@@ -219,7 +219,9 @@ MAKE_SYSCACHE(RELNAMENSP, pg_class_relname_nsp_index, 128);
 /*
  * Relation kinds with a table access method (rd_tableam).  Although sequences
  * use the heap table AM, they are enough of a special case in most uses that
- * they are not included here.
+ * they are not included here.  Likewise, partitioned tables can have an access
+ * method defined so that their children can inherit it; however, this is
+ * handled specially outside of this macro.
  */
 #define RELKIND_HAS_TABLE_AM(relkind) \
 	((relkind) == RELKIND_RELATION || \
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index e4a200b00e..35a8dec2b9 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -139,6 +139,7 @@ extern char get_rel_relkind(Oid relid);
 extern bool get_rel_relispartition(Oid relid);
 extern Oid	get_rel_tablespace(Oid relid);
 extern char get_rel_persistence(Oid relid);
+extern Oid	get_rel_relam(Oid relid);
 extern Oid	get_transform_fromsql(Oid typid, Oid langid, List *trftypes);
 extern Oid	get_transform_tosql(Oid typid, Oid langid, List *trftypes);
 extern bool get_typisdefined(Oid typid);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 3b1c2590fd..bd225b2eb9 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -187,6 +187,7 @@ typedef struct AlteredTableInfo
 	bool		verify_new_notnull; /* T if we should recheck NOT NULL */
 	int			rewrite;		/* Reason for forced rewrite, if any */
 	Oid			newAccessMethod;	/* new access method; 0 means no change */
+	bool		defaultAccessMethod;	/* true if SET ACCESS METHOD DEFAULT */
 	Oid			newTableSpace;	/* new tablespace; 0 means no change */
 	bool		chgPersistence; /* T if SET LOGGED/UNLOGGED is used */
 	char		newrelpersistence;	/* if above is true */
@@ -592,6 +593,8 @@ static ObjectAddress ATExecClusterOn(Relation rel, const char *indexName,
 									 LOCKMODE lockmode);
 static void ATExecDropCluster(Relation rel, LOCKMODE lockmode);
 static void ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname);
+static void ATExecSetAccessMethodNoStorage(Relation rel, Oid newAccessMethod,
+										   bool defaultAccessMethod);
 static bool ATPrepChangePersistence(Relation rel, bool toLogged);
 static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
 								const char *tablespacename, LOCKMODE lockmode);
@@ -706,7 +709,6 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 	Oid			ofTypeId;
 	ObjectAddress address;
 	LOCKMODE	parentLockmode;
-	const char *accessMethod = NULL;
 	Oid			accessMethodId = InvalidOid;
 
 	/*
@@ -959,20 +961,25 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 
-	/* look up the access method, verify it is for a table */
-	if (accessMethod != NULL)
-		accessMethodId = get_table_am_oid(accessMethod, false);
+	/*
+	 * Still nothing?  Use the default.  Partitioned tables default to
+	 * InvalidOid without an access method specified.
+	 */
+	if (RELKIND_HAS_TABLE_AM(relkind) && !OidIsValid(accessMethodId))
+		accessMethodId = get_table_am_oid(default_table_access_method, false);
 
 	/*
 	 * Create the relation.  Inherited defaults and constraints are passed in
@@ -5048,12 +5055,6 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
 		case AT_SetAccessMethod:	/* SET ACCESS METHOD */
 			ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_MATVIEW);
 
@@ -5410,6 +5411,14 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
+				ATExecSetAccessMethodNoStorage(rel, tab->newAccessMethod,
+											   tab->defaultAccessMethod);
 
@@ -6402,6 +6411,7 @@ ATGetQueueEntry(List **wqueue, Relation rel)
 	tab->relkind = rel->rd_rel->relkind;
 	tab->oldDesc = CreateTupleDescCopyConstr(RelationGetDescr(rel));
 	tab->newAccessMethod = InvalidOid;
+	tab->defaultAccessMethod = false;
 	tab->newTableSpace = InvalidOid;
 	tab->newrelpersistence = RELPERSISTENCE_PERMANENT;
 	tab->chgPersistence = false;
@@ -15204,7 +15214,15 @@ ATExecDropCluster(Relation rel, LOCKMODE lockmode)
  * Preparation phase for SET ACCESS METHOD
  *
  * Check that access method exists.  If it is the same as the table's current
- * access method, it is a no-op.  Otherwise, a table rewrite is necessary.
+ * access method, it is a no-op.  Otherwise, a table rewrite is necessary for
+ * relations with storage.
+ *
+ * amname is NULL for SET ACCESS METHOD DEFAULT.
+ *
+ * Partitioned tables may use InvalidOid to assign the default AM to partitions
+ * when created.  This operation is a no-op if a DEFAULT was given and the
+ * partitioned table uses InvalidOid as relam, or if the relam and the access
+ * method match.
  */
 static void
 ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname)
@@ -15215,12 +15233,35 @@ ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname)
 	amoid = get_table_am_oid(amname ? amname : default_table_access_method,
 							 false);
 
-	if (rel->rd_rel->relam == amoid)
+	if (RELKIND_HAS_STORAGE(rel->rd_rel->relkind) &&
+		rel->rd_rel->relam == amoid)
 		return;
 
+	/* Partitioned tables are handled here */
+	if (!RELKIND_HAS_STORAGE(rel->rd_rel->relkind))
+	{
+		if (amname == NULL)
+		{
+			/* DEFAULT implied, leave if relam is already InvalidOid */
+			if (!OidIsValid(rel->rd_rel->relam))
+				return;
+		}
+		else
+		{
+			/*
+			 * Access method set by query, leave if only this matches with its
+			 * relam.
+			 */
+			if (OidIsValid(rel->rd_rel->relam) &&
+				amoid == rel->rd_rel->relam)
+				return;
+		}
+	}
+
 	/* Save info for Phase 3 to do the real work */
 	tab->rewrite |= AT_REWRITE_ACCESS_METHOD;
 	tab->newAccessMethod = amoid;
+	tab->defaultAccessMethod = (amname == NULL);
 }
 
 /*
@@ -15545,6 +15586,110 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
 	list_free(reltoastidxids);
 }
 
+/*
+ * Special handling of ALTER TABLE SET ACCESS METHOD for relations with no
+ * storage that have an interest in preserving AM.
+ *
+ * Since these have no storage, setting the access method is a catalog only
+ * operation.  defaultAccessMethod tracks if a default value is wanted for
+ * the access method.
+ */
+static void
+ATExecSetAccessMethodNoStorage(Relation rel, Oid newAccessMethodId,
+							   bool defaultAccessMethod)
+{
+	Relation	pg_class;
+	Oid			oldAccessMethodId;
+	HeapTuple	tuple;
+	Form_pg_class rd_rel;
+	Oid			reloid = RelationGetRelid(rel);
+
+	if (!OidIsValid(newAccessMethodId))
+	{
+		/* if the access method is unchanged, leave */
+		return;
+	}
+
+	/*
+	 * Shouldn't be called on relations having storage; these are processed in
+	 * phase 3.
+	 */
+	Assert(!RELKIND_HAS_STORAGE(rel->rd_rel->relkind));
+
+	/* Get a modifiable copy of the relation's pg_class row. */
+	pg_class = table_open(RelationRelationId, RowExclusiveLock);
+
+	tuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(reloid));
+	if (!HeapTupleIsValid(tuple))
+		elog(ERROR, "cache lookup failed for relation %u", reloid);
+	rd_rel = (Form_pg_class) GETSTRUCT(tuple);
+
+	/* Update the pg_class row. */
+	oldAccessMethodId = rd_rel->relam;
+	rd_rel->relam = defaultAccessMethod ? InvalidOid : newAccessMethodId;
+
+	/* Leave if no update required */
+	if (rd_rel->relam == oldAccessMethodId)
+	{
+		heap_freetuple(tuple);
+		table_close(pg_class, RowExclusiveLock);
+		return;
+	}
+
+	CatalogTupleUpdate(pg_class, &tuple->t_self, tuple);
+
+	/*
+	 * Update the dependency on the new access method.  No dependency is added
+	 * if the new access method is InvalidOid (default case).  Be very careful
+	 * that this has to compare the previous value stored in pg_class with the
+	 * new one.
+	 */
+	if (!OidIsValid(oldAccessMethodId) && OidIsValid(rd_rel->relam))
+	{
+		ObjectAddress relobj,
+					referenced;
+
+		/*
+		 * New access method is defined and there was no dependency
+		 * previously, so record a new one.
+		 */
+		ObjectAddressSet(relobj, RelationRelationId, reloid);
+		ObjectAddressSet(referenced, AccessMethodRelationId, rd_rel->relam);
+		recordDependencyOn(&relobj, &referenced, DEPENDENCY_NORMAL);
+	}
+	else if (OidIsValid(oldAccessMethodId) &&
+			 !OidIsValid(rd_rel->relam))
+	{
+		/*
+		 * There was an access method defined, and no new one, so just remove
+		 * the existing dependency.
+		 */
+		deleteDependencyRecordsForClass(RelationRelationId, reloid,
+										AccessMethodRelationId,
+										DEPENDENCY_NORMAL);
+	}
+	else
+	{
+		Assert(OidIsValid(oldAccessMethodId) &&
+			   OidIsValid(rd_rel->relam));
+
+		/* Both are valid, so update the dependency */
+		changeDependencyFor(RelationRelationId, reloid,
+							AccessMethodRelationId,
+							oldAccessMethodId, rd_rel->relam);
+	}
+
+	CommandCounterIncrement();
+
+	InvokeObjectPostAlterHook(RelationRelationId, RelationGetRelid(rel), 0);
+
+	heap_freetuple(tuple);
+	table_close(pg_class, RowExclusiveLock);
+
+	/* Make sure the relam change is visible */
+	CommandCounterIncrement();
+}
+
 /*
  * Special handling of ALTER TABLE SET TABLESPACE for relations with no
  * storage that have an interest in preserving tablespace.
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index f730aa26c4..2ea249e90c 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -2068,6 +2068,28 @@ get_rel_persistence(Oid relid)
 	return result;
 }
 
+/*
+ * get_rel_relam
+ *

index 37c37df56c..5ad439c685 100644
+		 * Do nothing: access methods are a setting that partitions can

 
 
 #########################################
diff --git a/src/test/regress/expected/create_am.out b/src/test/regress/expected/create_am.out
index e843d39ee7..d023f29c59 100644
--- a/src/test/regress/expected/create_am.out
+++ b/src/test/regress/expected/create_am.out
@@ -176,9 +176,16 @@ SELECT f1 FROM tableam_tblmv_heap2 ORDER BY f1;
   1
 (1 row)
 
--- CREATE TABLE ..  PARTITION BY doesn't not support USING
+-- CREATE TABLE ..  PARTITION BY supports USING.
 CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2;
-ERROR:  specifying a table access method is not supported on a partitioned table
+SELECT a.amname FROM pg_class c, pg_am a
+  WHERE c.relname = 'tableam_parted_heap2' AND a.oid = c.relam;
+ amname 
+--------
+ heap2
+(1 row)
+
+DROP TABLE tableam_parted_heap2;
 CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a);
 -- new partitions will inherit from the current default, rather the partition root
 SET default_table_access_method = 'heap';
@@ -334,12 +341,151 @@ ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
 ERROR:  cannot have multiple SET ACCESS METHOD subcommands
 DROP MATERIALIZED VIEW heapmv;
 DROP TABLE heaptable;
--- No support for partitioned tables.
-CREATE TABLE am_partitioned(x INT, y INT)
-  PARTITION BY hash (x);
+-- Partition hierarchies with access methods
+BEGIN;
+SET LOCAL default_table_access_method = 'heap';
+CREATE TABLE am_partitioned(x INT, y INT) PARTITION BY hash (x);
+-- pg_class.relam is 0, no dependency recorded between the AM and the
+-- partitioned table.
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+ relam 
+-------
+     0
+(1 row)
+
+SELECT pg_describe_object(classid, objid, objsubid) AS obj,
+       pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
+  FROM pg_depend, pg_am
+  WHERE pg_depend.refclassid = 'pg_am'::regclass
+    AND pg_am.oid = pg_depend.refobjid
+    AND pg_depend.objid = 'am_partitioned'::regclass;
+ obj | refobj 
+-----+--------
+(0 rows)
+
+-- New default is set, with dependency added.
 ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
-ERROR:  cannot change access method of a partitioned table
+SELECT a.amname FROM pg_class c, pg_am a
+  WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
+ amname 
+--------
+ heap2
+(1 row)
+
+SELECT pg_describe_object(classid, objid, objsubid) AS obj,
+       pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
+  FROM pg_depend, pg_am
+  WHERE pg_depend.refclassid = 'pg_am'::regclass
+    AND pg_am.oid = pg_depend.refobjid
+    AND pg_depend.objid = 'am_partitioned'::regclass;
+         obj          |       refobj        
+----------------------+---------------------
+ table am_partitioned | access method heap2
+(1 row)
+
+-- Default is set, with dependency updated.
+SET LOCAL default_table_access_method = 'heap2';
+ALTER TABLE am_partitioned SET ACCESS METHOD heap;
+SELECT a.amname FROM pg_class c, pg_am a
+  WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
+ amname 
+--------
+ heap
+(1 row)
+
+-- Dependency pinned, hence removed.
+SELECT pg_describe_object(classid, objid, objsubid) AS obj,
+       pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
+  FROM pg_depend, pg_am
+  WHERE pg_depend.refclassid = 'pg_am'::regclass
+    AND pg_am.oid = pg_depend.refobjid
+    AND pg_depend.objid = 'am_partitioned'::regclass;
+ obj | refobj 
+-----+--------
+(0 rows)
+
+-- Default and AM set in in clause are the same, relam should be set.
+SET LOCAL default_table_access_method = 'heap2';
+ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
+SELECT a.amname FROM pg_class c, pg_am a
+  WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
+ amname 
+--------
+ heap2
+(1 row)
+
+-- Reset to default
+ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT;
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+ relam 
+-------
+     0
+(1 row)
+
+-- Upon ALTER TABLE SET ACCESS METHOD on a partitioned table, new partitions
+-- will inherit the AM set.  Existing partitioned are unchanged.
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+ relam 
+-------
+     0
+(1 row)
+
+SET LOCAL default_table_access_method = 'heap';
+CREATE TABLE am_partitioned_0 PARTITION OF am_partitioned
+  FOR VALUES WITH (MODULUS 10, REMAINDER 0);
+SET LOCAL default_table_access_method = 'heap2';
+CREATE TABLE am_partitioned_1 PARTITION OF am_partitioned
+  FOR VALUES WITH (MODULUS 10, REMAINDER 1);
+SET LOCAL default_table_access_method = 'heap';
+ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
+CREATE TABLE am_partitioned_2 PARTITION OF am_partitioned
+  FOR VALUES WITH (MODULUS 10, REMAINDER 2);
+ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT;
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+ relam 
+-------
+     0
+(1 row)
+
+CREATE TABLE am_partitioned_3 PARTITION OF am_partitioned
+  FOR VALUES WITH (MODULUS 10, REMAINDER 3);
+-- Partitioned table with relam at 0
+ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT;
+CREATE TABLE am_partitioned_5p PARTITION OF am_partitioned
+  FOR VALUES WITH (MODULUS 10, REMAINDER 5) PARTITION BY hash(y);
+-- Partitions of this partitioned table inherit default AM at creation
+-- time.
+CREATE TABLE am_partitioned_5p1 PARTITION OF am_partitioned_5p
+  FOR VALUES WITH (MODULUS 10, REMAINDER 1);
+-- Partitioned table with relam set.
+ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
+CREATE TABLE am_partitioned_6p PARTITION OF am_partitioned
+  FOR VALUES WITH (MODULUS 10, REMAINDER 6) PARTITION BY hash(y);
+-- Partitions of this partitioned table inherit its AM.
+CREATE TABLE am_partitioned_6p1 PARTITION OF am_partitioned_6p
+  FOR VALUES WITH (MODULUS 10, REMAINDER 1);
+SELECT c.relname, a.amname FROM pg_class c, pg_am a
+  WHERE c.relam = a.oid AND
+        c.relname LIKE 'am_partitioned%'
+UNION ALL
+SELECT c.relname, 'default' FROM pg_class c
+  WHERE c.relam = 0
+        AND c.relname LIKE 'am_partitioned%' ORDER BY 1;
+      relname       | amname  
+--------------------+---------
+ am_partitioned     | heap2
+ am_partitioned_0   | heap
+ am_partitioned_1   | heap2
+ am_partitioned_2   | heap2
+ am_partitioned_3   | heap
+ am_partitioned_5p  | default
+ am_partitioned_5p1 | heap
+ am_partitioned_6p  | heap2
+ am_partitioned_6p1 | heap2
+(9 rows)
+
 DROP TABLE am_partitioned;
+COMMIT;
 -- Second, create objects in the new AM by changing the default AM
 BEGIN;
 SET LOCAL default_table_access_method = 'heap2';
diff --git a/src/test/regress/sql/create_am.sql b/src/test/regress/sql/create_am.sql
index f752bf1678..010c07cdd2 100644
--- a/src/test/regress/sql/create_am.sql
+++ b/src/test/regress/sql/create_am.sql
@@ -124,8 +124,11 @@ CREATE SEQUENCE tableam_seq_heap2 USING heap2;
 CREATE MATERIALIZED VIEW tableam_tblmv_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2;
 SELECT f1 FROM tableam_tblmv_heap2 ORDER BY f1;
 
--- CREATE TABLE ..  PARTITION BY doesn't not support USING
+-- CREATE TABLE ..  PARTITION BY supports USING.
 CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2;
+SELECT a.amname FROM pg_class c, pg_am a
+  WHERE c.relname = 'tableam_parted_heap2' AND a.oid = c.relam;
+DROP TABLE tableam_parted_heap2;
 
 CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a);
 -- new partitions will inherit from the current default, rather the partition root
@@ -212,11 +215,91 @@ ALTER TABLE heaptable SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
 ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
 DROP MATERIALIZED VIEW heapmv;
 DROP TABLE heaptable;
--- No support for partitioned tables.
-CREATE TABLE am_partitioned(x INT, y INT)
-  PARTITION BY hash (x);
+
+-- Partition hierarchies with access methods
+BEGIN;
+SET LOCAL default_table_access_method = 'heap';
+CREATE TABLE am_partitioned(x INT, y INT) PARTITION BY hash (x);
+-- pg_class.relam is 0, no dependency recorded between the AM and the
+-- partitioned table.
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+SELECT pg_describe_object(classid, objid, objsubid) AS obj,
+       pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
+  FROM pg_depend, pg_am
+  WHERE pg_depend.refclassid = 'pg_am'::regclass
+    AND pg_am.oid = pg_depend.refobjid
+    AND pg_depend.objid = 'am_partitioned'::regclass;
+-- New default is set, with dependency added.
 ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
+SELECT a.amname FROM pg_class c, pg_am a
+  WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
+SELECT pg_describe_object(classid, objid, objsubid) AS obj,
+       pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
+  FROM pg_depend, pg_am
+  WHERE pg_depend.refclassid = 'pg_am'::regclass
+    AND pg_am.oid = pg_depend.refobjid
+    AND pg_depend.objid = 'am_partitioned'::regclass;
+-- Default is set, with dependency updated.
+SET LOCAL default_table_access_method = 'heap2';
+ALTER TABLE am_partitioned SET ACCESS METHOD heap;
+SELECT a.amname FROM pg_class c, pg_am a
+  WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
+-- Dependency pinned, hence removed.
+SELECT pg_describe_object(classid, objid, objsubid) AS obj,
+       pg_describe_object(refclassid, refobjid, refobjsubid) as refobj
+  FROM pg_depend, pg_am
+  WHERE pg_depend.refclassid = 'pg_am'::regclass
+    AND pg_am.oid = pg_depend.refobjid
+    AND pg_depend.objid = 'am_partitioned'::regclass;
+-- Default and AM set in in clause are the same, relam should be set.
+SET LOCAL default_table_access_method = 'heap2';
+ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
+SELECT a.amname FROM pg_class c, pg_am a
+  WHERE c.relname = 'am_partitioned' AND a.oid = c.relam;
+-- Reset to default
+ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT;
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+-- Upon ALTER TABLE SET ACCESS METHOD on a partitioned table, new partitions
+-- will inherit the AM set.  Existing partitioned are unchanged.
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+SET LOCAL default_table_access_method = 'heap';
+CREATE TABLE am_partitioned_0 PARTITION OF am_partitioned
+  FOR VALUES WITH (MODULUS 10, REMAINDER 0);
+SET LOCAL default_table_access_method = 'heap2';
+CREATE TABLE am_partitioned_1 PARTITION OF am_partitioned
+  FOR VALUES WITH (MODULUS 10, REMAINDER 1);
+SET LOCAL default_table_access_method = 'heap';
+ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
+CREATE TABLE am_partitioned_2 PARTITION OF am_partitioned
+  FOR VALUES WITH (MODULUS 10, REMAINDER 2);
+ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT;
+SELECT relam FROM pg_class WHERE relname = 'am_partitioned';
+CREATE TABLE am_partitioned_3 PARTITION OF am_partitioned
+  FOR VALUES WITH (MODULUS 10, REMAINDER 3);
+-- Partitioned table with relam at 0
+ALTER TABLE am_partitioned SET ACCESS METHOD DEFAULT;
+CREATE TABLE am_partitioned_5p PARTITION OF am_partitioned
+  FOR VALUES WITH (MODULUS 10, REMAINDER 5) PARTITION BY hash(y);
+-- Partitions of this partitioned table inherit default AM at creation
+-- time.
+CREATE TABLE am_partitioned_5p1 PARTITION OF am_partitioned_5p
+  FOR VALUES WITH (MODULUS 10, REMAINDER 1);
+-- Partitioned table with relam set.
+ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
+CREATE TABLE am_partitioned_6p PARTITION OF am_partitioned
+  FOR VALUES WITH (MODULUS 10, REMAINDER 6) PARTITION BY hash(y);
+-- Partitions of this partitioned table inherit its AM.
+CREATE TABLE am_partitioned_6p1 PARTITION OF am_partitioned_6p
+  FOR VALUES WITH (MODULUS 10, REMAINDER 1);
+SELECT c.relname, a.amname FROM pg_class c, pg_am a
+  WHERE c.relam = a.oid AND
+        c.relname LIKE 'am_partitioned%'
+UNION ALL
+SELECT c.relname, 'default' FROM pg_class c
+  WHERE c.relam = 0
+        AND c.relname LIKE 'am_partitioned%' ORDER BY 1;
 DROP TABLE am_partitioned;
+COMMIT;

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 96e3d77605..779c8b3122 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -737,6 +737,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
       <literal>DEFAULT</literal> changes the access method of the table
       to <xref linkend="guc-default-table-access-method"/>.
      </para>
+     <para>
+      When applied to a partitioned table, there is no data to rewrite, but any
+      partitions created afterwards will use that access method unless
+      overridden by a <literal>USING</literal> clause. Using
+      <varname>DEFAULT</varname> switches the partitioned table to rely on
+      the value of <varname>default_table_access_method</varname> set
+      when new partitions are created, instead.
+     </para>
     </listitem>
    </varlistentry>

-- 
2.43.0

---

From: Justin Pryzby <[email-masked]>
Date: Thu, 7 Mar 2024 20:02:00 -0600

> to be tracked down to the execution.

The data structure you used (defaultAccessMethod) allows this, which
is intended to be prohibited:

postgres=# ALTER TABLE a SET access method default, SET access method default;
ALTER TABLE

As you wrote it, you pass the "defaultAccessMethod" bool to
ATExecSetAccessMethodNoStorage(), which seems odd.  Why not just pass
the target amoid as newAccessMethod ?

When I fooled with this on my side, I called it "chgAccessMethod" to
follow "chgPersistence".  I think "is default" isn't the right data
structure.

Attached a relative patch with my version.

Also: I just realized that instead of adding a bool, we could test
(tab->rewrite & AT_REWRITE_ACCESS_METHOD) != 0

in in?

-- 
Justin
>From 8aca5e443ebb41b7de1ba18b519a33a97a41a897 Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Mon, 4 Mar 2024 17:42:04 +0900
Subject: [PATCH 1/2] Allow specifying access method of partitioned tables

 src/backend/commands/tablecmds.c        | 183 +++++++++++++++++++++---
 12 files changed, 487 insertions(+), 34 deletions(-)

index 0ae97d1adaa..e4fcacb610a 100644
 
index 96e3d776051..779c8b31226 100644
 
index 4cbaaccaf7c..b79081a5ec1 100644
 
index 7014be80396..cf595329b6c 100644
@@ -184,6 +184,7 @@ typedef struct AlteredTableInfo
@@ -589,6 +590,8 @@ static ObjectAddress ATExecClusterOn(Relation rel, const char *indexName,
@@ -703,7 +706,6 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
 
 	/*
@@ -956,20 +958,25 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,

 	/*
 	 * Create the relation.  Inherited defaults and constraints are passed in
@@ -5045,12 +5052,6 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
 		case AT_SetAccessMethod:	/* SET ACCESS METHOD */
 			ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_MATVIEW);
 
@@ -5407,6 +5408,14 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
 
@@ -6399,6 +6408,7 @@ ATGetQueueEntry(List **wqueue, Relation rel)
@@ -15201,8 +15211,14 @@ ATExecDropCluster(Relation rel, LOCKMODE lockmode)
  * If amname is NULL, select default_table_access_method as access method.
@@ -15213,12 +15229,35 @@ ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname)

 
 /*
@@ -15543,6 +15582,110 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
 	list_free(reltoastidxids);
 }
 
+/*
index 6418d1c6eba..26368ffcc97 100644
@@ -2069,6 +2069,28 @@ get_rel_persistence(Oid relid)
 	return result;
 }
 
+/*
+ * get_rel_relam
+ *

index 4d339ee7950..18c6044238c 100644
@@ -1208,6 +1208,13 @@ retry:
 
index 10cbf02bebd..ec406bff297 100644

index 00b5092713d..dfee1982f41 100644
 
index 3b7533e7bb3..b505ed1becc 100644
index e4a200b00ec..35a8dec2b9f 100644
index 8d73e213563..438f5a72193 100644
 
@@ -336,12 +343,151 @@ ALTER MATERIALIZED VIEW heapmv SET ACCESS METHOD heap, SET ACCESS METHOD heap2;
index 606ee4cb241..907211653d3 100644

@@ -213,11 +216,91 @@ ALTER TABLE heaptable SET ACCESS METHOD DEFAULT, SET ACCESS METHOD heap2;
 
2.42.0

>From a69348b59307ab85a051ea17aabca3150a391eae Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email-masked]>
Date: Sat, 2 Mar 2024 07:23:00 -0600
Subject: [PATCH 2/2] f

---
 src/backend/commands/tablecmds.c | 74 +++++++++++---------------------
 1 file changed, 25 insertions(+), 49 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index cf595329b6c..3f26379b1bd 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -183,8 +183,8 @@ typedef struct AlteredTableInfo
 	List	   *afterStmts;		/* List of utility command parsetrees */
 	bool		verify_new_notnull; /* T if we should recheck NOT NULL */
 	int			rewrite;		/* Reason for forced rewrite, if any */
-	Oid			newAccessMethod;	/* new access method; 0 means no change */
-	bool		defaultAccessMethod;	/* true if SET ACCESS METHOD DEFAULT */
+	bool		chgAccessMethod;	/* T if SET ACCESS METHOD is used */
+	Oid			newAccessMethod;	/* new access method; 0 means no change - if above is true */
 	Oid			newTableSpace;	/* new tablespace; 0 means no change */
 	bool		chgPersistence; /* T if SET LOGGED/UNLOGGED is used */
 	char		newrelpersistence;	/* if above is true */
@@ -590,8 +590,7 @@ static ObjectAddress ATExecClusterOn(Relation rel, const char *indexName,
 									 LOCKMODE lockmode);
 static void ATExecDropCluster(Relation rel, LOCKMODE lockmode);
 static void ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname);
-static void ATExecSetAccessMethodNoStorage(Relation rel, Oid newAccessMethod,
-										   bool defaultAccessMethod);
+static void ATExecSetAccessMethodNoStorage(Relation rel, Oid newAccessMethod);
 static bool ATPrepChangePersistence(Relation rel, bool toLogged);
 static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
 								const char *tablespacename, LOCKMODE lockmode);
@@ -5053,7 +5052,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
 			ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_MATVIEW);
 
 			/* check if another access method change was already requested */
-			if (OidIsValid(tab->newAccessMethod))
+			if (tab->chgAccessMethod)
 				ereport(ERROR,
 						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 						 errmsg("cannot have multiple SET ACCESS METHOD subcommands")));
@@ -5413,9 +5412,8 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
 			 * Only do this for partitioned tables, for which this is just a
 			 * catalog change.  Tables with storage are handled by Phase 3.
 			 */
-			if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-				ATExecSetAccessMethodNoStorage(rel, tab->newAccessMethod,
-											   tab->defaultAccessMethod);
+			if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE && tab->chgAccessMethod)
+				ATExecSetAccessMethodNoStorage(rel, tab->newAccessMethod);
 			break;
 		case AT_SetTableSpace:	/* SET TABLESPACE */
 
@@ -5821,7 +5819,7 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
 			 * Select destination access method (same as original unless user
 			 * requested a change)
 			 */
-			if (OidIsValid(tab->newAccessMethod))
+			if (tab->chgAccessMethod)
 				NewAccessMethod = tab->newAccessMethod;
 			else
 				NewAccessMethod = OldHeap->rd_rel->relam;
@@ -6408,7 +6406,7 @@ ATGetQueueEntry(List **wqueue, Relation rel)
 	tab->relkind = rel->rd_rel->relkind;
 	tab->oldDesc = CreateTupleDescCopyConstr(RelationGetDescr(rel));
 	tab->newAccessMethod = InvalidOid;
-	tab->defaultAccessMethod = false;
+	tab->chgAccessMethod = false;
 	tab->newTableSpace = InvalidOid;
 	tab->newrelpersistence = RELPERSISTENCE_PERMANENT;
 	tab->chgPersistence = false;
@@ -15225,39 +15223,25 @@ ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname)
 {
 	Oid			amoid;
 
-	/* Check that the table access method exists */
-	amoid = get_table_am_oid(amname ? amname : default_table_access_method,
-							 false);
+	/*
+	 * Check that the table access method exists.
+	 * Use the access method, if specified, otherwise (when not specified) 0
+	 * for partitioned tables or the configured default AM.
+	 */
+	if (amname != NULL)
+		amoid = get_table_am_oid(amname, false);
+	else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+		amoid = 0;
+	else
+		amoid = get_table_am_oid(default_table_access_method, false);
 
-	if (RELKIND_HAS_STORAGE(rel->rd_rel->relkind) &&
-		rel->rd_rel->relam == amoid)
+	if (rel->rd_rel->relam == amoid)
 		return;
 
-	/* Partitioned tables are handled here */
-	if (!RELKIND_HAS_STORAGE(rel->rd_rel->relkind))
-	{
-		if (amname == NULL)
-		{
-			/* DEFAULT implied, leave if relam is already InvalidOid */
-			if (!OidIsValid(rel->rd_rel->relam))
-				return;
-		}
-		else
-		{
-			/*
-			 * Access method set by query, leave if only this matches with its
-			 * relam.
-			 */
-			if (OidIsValid(rel->rd_rel->relam) &&
-				amoid == rel->rd_rel->relam)
-				return;
-		}
-	}
-
 	/* Save info for Phase 3 to do the real work */
 	tab->rewrite |= AT_REWRITE_ACCESS_METHOD;
 	tab->newAccessMethod = amoid;
-	tab->defaultAccessMethod = (amname == NULL);
+	tab->chgAccessMethod = true;
 }
 
 /*
@@ -15587,12 +15571,10 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
  * storage that have an interest in preserving AM.
  *
  * Since these have no storage, setting the access method is a catalog only
- * operation.  defaultAccessMethod tracks if a default value is wanted for
- * the access method.
+ * operation.
  */
 static void
-ATExecSetAccessMethodNoStorage(Relation rel, Oid newAccessMethodId,
-							   bool defaultAccessMethod)
+ATExecSetAccessMethodNoStorage(Relation rel, Oid newAccessMethodId)
 {
 	Relation	pg_class;
 	Oid			oldAccessMethodId;
@@ -15600,12 +15582,6 @@ ATExecSetAccessMethodNoStorage(Relation rel, Oid newAccessMethodId,
 	Form_pg_class rd_rel;
 	Oid			reloid = RelationGetRelid(rel);
 
-	if (!OidIsValid(newAccessMethodId))
-	{
-		/* if the access method is unchanged, leave */
-		return;
-	}
-
 	/*
 	 * Shouldn't be called on relations having storage; these are processed in
 	 * phase 3.
@@ -15622,7 +15598,7 @@ ATExecSetAccessMethodNoStorage(Relation rel, Oid newAccessMethodId,
 
 	/* Update the pg_class row. */
 	oldAccessMethodId = rd_rel->relam;
-	rd_rel->relam = defaultAccessMethod ? InvalidOid : newAccessMethodId;
+	rd_rel->relam = newAccessMethodId;
 
 	/* Leave if no update required */
 	if (rd_rel->relam == oldAccessMethodId)
@@ -15640,7 +15616,7 @@ ATExecSetAccessMethodNoStorage(Relation rel, Oid newAccessMethodId,
 	 * that this has to compare the previous value stored in pg_class with the
 	 * new one.
 	 */
-	if (!OidIsValid(oldAccessMethodId) && OidIsValid(rd_rel->relam))
+	if (!OidIsValid(oldAccessMethodId) && OidIsValid(newAccessMethodId))
 	{
 		ObjectAddress relobj,
 					referenced;
-- 
2.42.0

---

From: Michael Paquier <[email-masked]>
Date: Fri, 8 Mar 2024 13:32:10 +0900

+	/*

..  While using this flow to choose the AM oid, that's neater than the
versions I could come up with, pretty cool.

> Attached a relative patch with my version.

Thanks.  I've applied the patch to add the DEFAULT clause for now, to
ease the work on this patch.

> (tab->rewrite & AT_REWRITE_ACCESS_METHOD) != 0

Hmm.  I've considered that, but the boolean style is able to do the
work, while being more consistent, so I'm OK with what you are doing
in your 0002.

> +-- Default and AM set in in clause are the same, relam should be set.

Oops, fixed.

I have spent more time reviewing the whole and the tests (I didn't see
much value in testing the DEFAULT clause twice for the partitioned
table case and there is a test in d61a6cad6418), tweaked a few
comments and the documentation, did an indentation and a commit
message draft.

How does that look to you?  The test coverage and the semantics do
what we want them to do, so that looks rather reasonable here.  A
second or even third pair of eyes would not hurt.
--
Michael
From 4114c7944c78dbb6eb85320434d942daa77fa4d8 Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email-masked]>
Date: Fri, 8 Mar 2024 13:24:14 +0900
Subject: [PATCH v4] Allow specifying access method for partitioned tables

Partitioned tables gain support for CREATE TABLE .. USING, where
specifying an access method can serve as a hint when creating partitions
on it.  This includes support for ALTER TABLE .. SET ACCESS METHOD,
where specifying DEFAULT is equivalent to resetting the partitioned
table's relam to 0.  Specifying a non-DEFAULT access method will set its
relam to not be 0.

The historical default of using pg_class.relam as InvalidOid is
preserved, corresponding to the case where USING clause is not used.  In
this case, like previous releases, a partition is created with the
access method set by default_table_access_method if its CREATE TABLE
query does not specify an access method with a USING clause.

The relcache of partitioned tables is not changed: rd_tableam is not
set, even if a partitioned table has a relam set.

Authors: Justin Pryzby, Soumyadeep Chakraborty
Reviewed-by: Michael Paquier, and a few others..
Discussion: https://postgr.es/m/
---
 src/include/catalog/pg_class.h          |   4 +-
 src/include/utils/lsyscache.h           |   1 +
 src/backend/commands/tablecmds.c        | 172 ++++++++++++++++++++----
 src/backend/utils/cache/lsyscache.c     |  22 +++
 src/backend/utils/cache/relcache.c      |   7 +
 src/bin/pg_dump/pg_dump.c               |   3 +-
 src/bin/pg_dump/t/002_pg_dump.pl        |  35 +++++
 src/test/regress/expected/create_am.out | 158 +++++++++++++++++++++-
 src/test/regress/sql/create_am.sql      |  91 ++++++++++++-
 doc/src/sgml/catalogs.sgml              |   5 +-
 doc/src/sgml/ref/alter_table.sgml       |   8 ++
 doc/src/sgml/ref/create_table.sgml      |   4 +
 12 files changed, 471 insertions(+), 39 deletions(-)

index 3b7533e7bb..0fc2c093b0 100644
+ * method defined so that their partitions can inherit it, but they do not set
+ * rd_tableam; hence, this is handled specially outside of this macro.
index 7014be8039..4dd96b93cb 100644
@@ -183,7 +183,9 @@ typedef struct AlteredTableInfo
+	Oid			newAccessMethod;	/* new access method; 0 means no change,
+									 * if above is true */
@@ -589,6 +591,7 @@ static ObjectAddress ATExecClusterOn(Relation rel, const char *indexName,
 
 	/*

 	/*
 	 * Create the relation.  Inherited defaults and constraints are passed in
@@ -5045,14 +5052,8 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
 		case AT_SetAccessMethod:	/* SET ACCESS METHOD */
 			ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_MATVIEW);
 
+			if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE &&
+				tab->chgAccessMethod)
 
@@ -5812,7 +5821,7 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
@@ -15201,17 +15211,32 @@ ATExecDropCluster(Relation rel, LOCKMODE lockmode)
+ * method specified match.
 
-	/* Check that the table access method exists */
-	amoid = get_table_am_oid(amname ? amname : default_table_access_method,
-							 false);
+	/*
+	 * Check that the table access method exists.
+	 *
+	 * Use the access method specified, otherwise, when not specified, use
+	 * InvalidOid for partitioned tables or the configured default AM.
+	 */
+	if (amname != NULL)
+		amoid = get_table_am_oid(amname, false);
+	else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+		amoid = InvalidOid;
+	else
+		amoid = get_table_am_oid(default_table_access_method, false);
 
@@ -15219,6 +15244,7 @@ ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname)
 
 /*
@@ -15543,6 +15569,102 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
 	list_free(reltoastidxids);
 }
 
+/*
index 6418d1c6eb..26368ffcc9 100644
 
+/*
+ * get_rel_relam
+ *

index 4d339ee795..18c6044238 100644
 
index 10cbf02beb..ec406bff29 100644

 
index 8d73e21356..a27805a8f5 100644
 
+-- Default and AM set in the clause are the same, relam should be set.
index 606ee4cb24..adff0079f9 100644

+-- Default and AM set in the clause are the same, relam should be set.
 
index 0ae97d1ada..e4fcacb610 100644
 
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 96e3d77605..b6e794e6ce 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -737,6 +737,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
       <literal>DEFAULT</literal> changes the access method of the table
       to <xref linkend="guc-default-table-access-method"/>.
      </para>
+     <para>
+      When applied to a partitioned table, there is no data to rewrite, but any
+      partitions created afterwards will use that access method unless
+      overridden by a <literal>USING</literal> clause. Using
+      <varname>DEFAULT</varname> switches the partitioned table to use
+      <varname>default_table_access_method</varname> when new partitions are
+      created.
+     </para>
     </listitem>
    </varlistentry>

-- 
2.43.0