Re: ALTER TABLE SET ACCESS METHOD on partitioned tables

From: Michael Paquier <michael(at)paquier(dot)xyz>
To: Justin Pryzby <pryzby(at)telsasoft(dot)com>
Cc: Peter Eisentraut <peter(at)eisentraut(dot)org>, Soumyadeep Chakraborty <soumyadeep2007(at)gmail(dot)com>, Zhihong Yu <zyu(at)yugabyte(dot)com>, pgsql-hackers(at)postgresql(dot)org, Ashwin Agrawal <ashwinstar(at)gmail(dot)com>, vanjared(at)vmware(dot)com
Subject: Re: ALTER TABLE SET ACCESS METHOD on partitioned tables
Date: 2024-03-01 01:56:50
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Thu, Feb 29, 2024 at 08:51:31AM -0600, Justin Pryzby wrote:
> On Wed, Feb 28, 2024 at 05:08:49PM +0900, Michael Paquier wrote:
>> 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.
> 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).

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

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?

Attachment Content-Type Size
v2-0001-Add-DEFAULT-option-to-ALTER-TABLE-SET-ACCESS-METH.patch text/x-diff 5.6 KB
v2-0002-Allow-specifying-access-method-of-partitioned-tab.patch text/x-diff 22.5 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2024-03-01 02:02:01 Re: Injection points: some tools to wait and wake
Previous Message Kyotaro Horiguchi 2024-03-01 01:29:12 Re: Infinite loop in XLogPageRead() on standby