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: Peter Eisentraut <peter@eisentraut.org>
Cc: Justin Pryzby <pryzby@telsasoft.com>, Soumyadeep Chakraborty <soumyadeep2007@gmail.com>, Zhihong Yu <zyu@yugabyte.com>, pgsql-hackers@postgresql.org, Ashwin Agrawal <ashwinstar@gmail.com>, vanjared@vmware.com
Date: 2024-02-28T08:08:49Z
Lists: pgsql-hackers

Attachments

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

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