Re: ALTER TABLE SET ACCESS METHOD on partitioned tables

On Fri, Mar 01, 2024 at 03:03:14PM -0600, Justin Pryzby wrote:
> 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.

Okay.

> 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.

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.

> 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 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.

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

>> + * 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/

Indeed. Fixed both.

> 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

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