Horton
Menu

Re: ALTER TABLE SET ACCESS METHOD on partitioned tables

Michael Paquier <michael@paquier.xyz>

View thread
From: Michael Paquier <michael@paquier.xyz>
To: Jelte Fennema-Nio <postgres@jeltef.nl>
Cc: Justin Pryzby <pryzby@telsasoft.com>, Peter Eisentraut <peter@eisentraut.org>, Soumyadeep Chakraborty <soumyadeep2007@gmail.com>, Zhihong Yu <zyu@yugabyte.com>, pgsql-hackers@postgresql.org, Ashwin Agrawal <ashwinstar@gmail.com>, vanjared@vmware.com
Date: 2024-03-01T05:03:48Z
Lists: pgsql-hackers 
On Fri, Mar 01, 2024 at 05:43:25AM +0100, Jelte Fennema-Nio wrote:
> 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?

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. 

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

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

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add information about access method for partitioned relations in \dP+

  2. Fix dumps of partitioned tables with table AMs

  3. Don't use the pg_am system catalog in new test

  4. ALTER TABLE: rework determination of access method ID

  5. Allow specifying an access method for partitioned tables

  6. Review wording on tablespaces w.r.t. partitioned tables

  7. Add support for DEFAULT in ALTER TABLE .. SET ACCESS METHOD

  8. Update comment of AlterTableCmd->name in parsenodes.h