Re: ALTER TABLE SET ACCESS METHOD on partitioned tables

On Wed, Feb 21, 2024 at 08:46:48AM +0100, Peter Eisentraut wrote:
> 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